mysql的锁
作者：bin什么是mysql锁？
锁是协调多线程或线程并发请求同一个资源的机制，mysql的锁机制相对简单，可以笼统的分为3大类：
1、表级锁：开销小，加锁快，无死锁，粗粒度，冲突概率高，并发度能低；
2、行级锁：开销大，加锁慢，会死锁，细粒度，冲突概率低，并发度能高；
3、页面锁：开销中，加锁中，会死锁，粒度中，并发一般。
一、InnoDB锁
InnoDB锁与MyISAM最大的2个不同点，1、支持事物，2、采用行锁；
使用for upadte上锁，来做并发控制，update语句默认加了排他锁，select的结果那么默认走的是MVCC方式
1、锁的类型
1、共享锁(又叫读锁S)：锁定行后，(其它session)可以加共享锁，(其它)不可以加排他锁:
select * from emp where id = 1 lock in share mode;
2、排他锁(又叫写锁X)：锁定行后，(其它)不可以加共享锁，(其它)不可以加排他锁:
select * from emp where id = 1 for update;
2、表锁
表锁是mysql server级别的锁，innoDB引擎中是row lock，可以使用如下命令锁一个表(read/write)：
lock table t write; //加X锁 unlock tables; //解锁
3、行锁
值得注意对是，如果innoDB不通过索引加锁，那就需要遍历所有数据，那么innoDB将对所有遍历过的记录进行加锁，实现效果和表锁一样了。
我们使用以下表来做演示，便于理解
CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 insert into test values(1,1); insert into test values(2,2); insert into test values(4,4); insert into test values(5,5); insert into test values(7,7); insert into test values(10,10); insert into test values(11,11);
加锁的原则:
1.默认加锁都是next-key锁，范围加锁时，就是若干个连续的next-key锁，如(7,10]
2.在唯一索引时，next-key锁会降级为record锁。如[7]
3.如果遍历到到值与查找到值不相等，那么next-key，就会降级为gap锁。如(7,10)
行锁类型:
next-key锁：默认的锁，即record + gap合并，锁一个范围，并且会把当前记录也锁进去,左开右闭(7,10]
select * from t where name = 10
record锁：锁定当前行，例如锁的是一个唯一索引时，next-key降级为record锁[7]
select * from t where id = 7
gap锁：锁2个确定值的间隙，例如找不到与查找值相等的值，就由next-key降级为gap锁(7,10)
select * from t where id = 8
next-key锁 + gap锁的验证
锁7记录，但是通过name(不唯一)去锁，找到值与where相等即7，那么给7上next key(5，7]锁，然后因为不是唯一索引的，后面可能还有7，如果还有一个7，那就又一个next key，但是继续往后找到的是10不是7，就将next key降级为gap(7,11)锁，锁住的就是(5,7] + (7,11)
MySQL [sessionA]> select * from test where name = 7 for update; +----+------+ | id | name | +----+------+ | 7 | 7 | +----+------+
我们会发现7的前后，包括7都被锁住了，
MySQL [sessionB]> insert into test set id = 6, name = 77; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [sessionB]> insert into test set id = 8, name = 88; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [sessionB]> update test set name = 77 where id = 7; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction //5和10未被锁 MySQL [sessionB]> mysql> update test set name = 100 where id = 5; Query OK, 1 row affected (0.01 sec) MySQL [sessionB]> update test set name = 1111 where id = 10; Query OK, 1 row affected (0.00 sec)
gap锁验证
如果锁定不存在都记录8：
MySQL [test]> select * from test where id = 8 for update; Empty set (0.00 sec)
那么会将间隙锁起来，也是一个gap锁，会把8、9都锁起来
mysql> update test set name = "a8" where id = 7; Query OK, 1 row affected (0.00 sec) MySQL [sessionB]> insert into test set id = 8, name = 88; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [sessionB]> insert into test set id = 9, name = 99; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction //10未锁 MySQL [sessionB]> update test set name = 101 where id = 10; Query OK, 1 row affected (0.00 sec)
遍历过的记录，都会加锁
例如如下order by，即便其中name是非唯一索引，那么会使用next-key，然后使用了order by，那么就会对所有排序的记录加锁，这里只和where加锁的字段有关，和order by哪个字段无关
select * from test where name = 11 order by id desc lock in share mode;
4、意向锁
意向锁是表锁级别的锁，表示一个意向，用于解决表锁和行锁冲突的问题，在加表锁时，需要先确定表中没有加行锁才行，如果没有意向锁，那么就要遍历所有记录。
意向锁就是在加行锁前，需要对表加一个意向锁，如果已经有意向锁，那么表锁就会阻塞等待。
意向锁分2种：
意向共享锁（IS）指示一个事务打算设置共享行锁时，需要先设置此锁。
意向独占锁（IX）指示一个事务打算设置排他行锁时，需要先设置此锁。
锁兼容|冲突矩阵如下：
5、插入意向锁
插入意向锁不是上面说的意向锁，是一个gap锁，表示对间隙的插入意向
例如4-7记录之间存在5、6，两个间隙，那么事物A插入5时，如果使用next-key锁的话，会影响事物B插入6，这样并发性能很低。
为了解决这个并发问题，引入了插入意向锁，事物A插入5时，事物B同时可以插入6，互不影响。（update和delete都还是原来的常规行锁record、next-key、gap等，只有insert才有插入意向锁）
6、自增锁
通常我们定义的AUTO_INCREMENT自增字段，在进行自增的时候，也需要加锁，避免出现重复的值。
自增锁不依赖事物，如果事物A申请了id = 2，事物B申请了id =3 ，事物A回滚，事物B提交成功，
那么此时自增就到id=4了，会出现id=2这个间隙（空洞）。
主从同步时为了id数据一致，应该将binlog的格式设置称row才行。
6、死锁的出现：
2个session对同一行添加锁共享锁:
即2个共享锁都对数据进行更新，就会出现死锁，导致退出；
(session_1)mysql> select * from emp where id = 1 lock in share mode; (session_2)mysql> select * from emp where id = 1 lock in share mode; (session_2)mysql> update emp set ename = 'qi7' where id = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 (session_1)mysql> update emp set ename = 'qi7' where id = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
2个session对同一个行进行排他锁，进入死循环:
(session_1)mysql> select * from emp where id = 1 for update; (session_2)mysql> select * from emp where id = 2 for update; --等待 (session_1)mysql> select * from emp where id = 2 for update; (session_2)mysql> select * from emp where id = 1 for update; --等待
这样session_1和session_2都进入等待，即出现了死锁
如果遇到上面的问题(访问多个表)，应该约定好通过相同的顺序进行访问，这样可以大大降低出现死锁概率；
7、查看死锁产生的原因(mysql 5.1后)
show engine innodb status\G;
mysql 5.1前：
show innoDB status;
二、myISAM的锁
1、锁状态与模式
该引擎只支持表锁，我们可以通过以下方式查看锁的争用状态
mysql> show status like 'Table_lock%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 26280 | | Table_locks_waited | 0 | +-----------------------+-------+
Table_locks_waited值比较高则说明争用严重
锁的模式：
共享锁：对MyISAM读，其它用户可读，不可写
独占锁：对MyISAM写，其它用不不可读，不可写
2、给表加锁
MyISAM在select操作时会自动加上读锁，在update、delete、insert时会自动加上写锁，我们一般不需要直接用lock table去锁表，显式去lock表时通常是为了方便调试，或者2条sql先后查寻2个表，且值要相同时，这时我们需要主动的去锁这2个表：
lock tables emp read local, emp_1 read local； select count(id) from emp; select count(id) from emp_1; unlock tables;
可以使用如下命令锁一个表(read/write)：
lock table emp write;
也可以使用如下命令锁多个表：
lock tables emp read local, emp_1 read local
添加了read锁之后，所有线程都不能对这个表写(包括自己)，并且当前线程，不能读写其他表。
如果通过别名访问，那么应该别名锁定：
mysql> lock table emp as e read; mysql> select * from emp as e;
3、并发插入
MyISAM有一个变量concurrent_insert；去控制并发行为：
mysql> show variables like '%insert'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | concurrent_insert | AUTO | +-------------------+-------+
0(NEVER) : 不允许并发
1(AUTO) :在表中不存在空洞时允许并发(空洞：表中间被删除行)
2(ALWAYS) : 不管有没有空洞，都允许
值得注意的是：
我们可以通过修改concurrent_insert来解决表在查询和插入时争用的问题，例如我们将concurrent_insert改为ALWAYS(2)，然后定期的去整理碎片(OPTIMIZE TABLE emp)，回收删除记录产生的空洞；
4、MyISAM锁的调度(⭐️)
MyISAM读写锁时互斥的，读写操作也是串行的，如果存在下面的情况：
重现：一个进行先请求读锁，另一个进程后请求写锁，并且2个都进入等待队列，那么写锁会插队到读锁前，这是因为mysql认为写比读重要；
问题：大量更新操作，会让查询操作没办法获得读锁(例如用户登陆等待很久)
解决办法：
1、设置low-priority-updates，给引擎默认给予读请求更高的优先级；
2、当前session设置low-priority-updates=1，使发出的更新请求优先级降低;
3、降低指定insert、update、delete语句的low-priority优先级；
4、给系统参数max_write_lock_count配置一个合适的值，当读锁达到这个值时，暂时降低写锁优先级;