MySQL锁-行锁与间隙锁

MySQL锁-行锁与间隙锁

昨天新来的同事，问了我间隙锁的问题，在我一番解释后，发现好多处讲解不对，实实在在的打脸。今天重新梳理下，发现之前了解不够深入。

行的锁定方式

行锁：Record lock，主要更新删除是，等值查询条件是唯一索引时采用的

间隙锁：Gap lock，其实它是行锁中的一种。它锁定的是一个范围区间的索引，遵守左开右闭原则。

Next-Key Lock: 行锁+间隙锁的组合

可重复读隔离级别下加锁规则

• 加锁的维度是Next-key Lock,遵循左开右闭关，也称前开后闭

• 插入过程中访问对象会增加锁，其实会改变间隙锁的范围区间

• 等值查询是索引字段，且唯一索引（含主键），则Next-key Lock会升级为行锁

• 等值查询是普通索引，那么向右（后）遍历，最后一个值不满足查询条件的等值关系，则Next-key Lock升级为间隙锁

• 唯一索引上的范围查询会访问到不满足条件的第一个值为止

案例推演

id（PK）	no（idx_no(no)）	score
10	10	111
20	20	22
30	30	33
40	40	444

以上表，id为主键，no为不同索引，score无索引

锁的组成：

• 行锁：10、20、30、40

• 间隙锁：(-infinity, 10]、(10, 20]、(20, 30]、(30,40]、(40,infinity]， 其中(-infinity和infinity为数据库定义字段类型的最小值和最大值。

唯一索引等值查询

T1	T2	T3	T4
BEGIN; start TRANSACTION;	BEGIN; start TRANSACTION;	BEGIN; start TRANSACTION;	BEGIN; start TRANSACTION;
SELECT * FROM lock_test where id = 10 for update;		update lock_test set score= score +1 where id = 10 ; ***Lock wait timeout exceeded; try restarting transaction***
			SELECT * FROM lock_test where id = 10 ;
			SELECT * FROM lock_test where id = 10 lock in share mode; ***Lock wait timeout exceeded; try restarting transaction***
	insert into lock_test values (6,6,66);
	insert into lock_test values (11,11,111);
commit;
	commit;	commit;	commit;

• T1、T2、T3事务开启(T2、T3事务也可以在select … for update 后开启)，

• 由于id=10,符合唯一索引等值条件，所以间隙锁(-infinity, 10]升级为行锁10，于是以下：

• T2插入 6、11不会阻塞。

• T3，id=10，所以会阻塞等待中

• T4，for update添加是写锁（X锁），即排它锁，写之间是互斥的，但对于读取数据来说，不影响，它不加锁。但执行后面那个 SELECT * FROM lock_test where id = 10 lock in share mode;添加共享锁（S锁），是不允许，也就是说排它锁，只允许自己的事务进行读写，不允许其他事务进行增删改及加锁行为，只允许查。

  1. select … lock in share mode。
     为查询语句涉及到的数据加上共享锁，阻塞其他事务修改真实数据。
  2. 共享锁和排他锁。
     共享锁(读锁S锁)：一个事务lock in share mode，其他事务只能读数据不能更新数据
     排他锁(写锁X锁)：一个事务for update加上排他锁，其他事务不能对相关数据加其他锁
  3. update、insert、delete默认会加排他锁。

普通索引等值查询

T1	T2	T3
BEGIN; start TRANSACTION;	BEGIN; start TRANSACTION;	BEGIN; start TRANSACTION;
SELECT * FROM lock_test where no = 10 for update;		update lock_test set score= score +1 where id = 10 ; ***Lock wait timeout exceeded; try restarting transaction***
	insert into lock_test values (6,6,66); ***Lock wait timeout exceeded; try restarting transaction***
	insert into lock_test values (11,11,111); ***Lock wait timeout exceeded; try restarting transaction***
commit;
	commit;	commit;

• T1、T2、T3事务开启(T2、T3事务也可以在select … for update 后开启)，

• 由于no=10, 普通索引加锁范围：(-infinity, 10]、(10,20],

• T3，id=10，所以会阻塞等待中

• T2，6和11都在加锁范围内，所以阻塞等待中

• 另外：T1的for update换成 lock in model，结果也一样

唯一索引范围查询

T1	T2	T3	T4
BEGIN; start TRANSACTION;	BEGIN; start TRANSACTION;	BEGIN; start TRANSACTION;
SELECT * FROM lock_test where id>= 10 and id < 15 for update;		update lock_test set score= score +1 where id = 10 ; ***Lock wait timeout exceeded; try restarting transaction***
			update lock_test set score= score +1 where id = 15 ;
	insert into lock_test values (6,6,66);
	insert into lock_test values (11,11,111); ***Lock wait timeout exceeded; try restarting transaction***
commit;
	commit;	commit;

• 由于id=10,符合唯一索引等值条件，所以间隙锁(-infinity, 10]

• 而且id=10是区间最大值，所以间隙锁升级为行锁10

• 15的间隙锁为(10, 15],由于id大于10且小于15，所以间隙锁退化为(10,15）

• 经过上面演变锁的范围为[10,15),于是：

• T3 id=10,再锁的范围内，所以阻塞

• T2 第一sql插入6的不阻塞，插入11主键在锁范围内，阻塞了

• T4 id=15，不在范围内，所以不阻塞

普通索引范围查询

T1	T2	T3	T4
BEGIN; start TRANSACTION;	BEGIN; start TRANSACTION;	BEGIN; start TRANSACTION;
SELECT * FROM lock_test where no>= 10 and no< 15 for update;		update lock_test set score= score +1 where id = 10 ; ***Lock wait timeout exceeded; try restarting transaction***
			update lock_test set score= score +1 where id = 15 ;
	insert into lock_test values (6,6,66); ***Lock wait timeout exceeded; try restarting transaction***
	insert into lock_test values (11,11,111); ***Lock wait timeout exceeded; try restarting transaction***
commit;
	commit;	commit;

• 由于id=10,符合唯一索引等值条件，所以间隙锁(-infinity, 10]

• no=10,并不是唯一索引，所以等值条件也不会变成行锁，故还是(-infinity, 10]

• 15的间隙锁为(10, 15],由于id大于10且小于15，所以间隙锁退化为(10,15）

• 经过上面演变锁的范围为(-infinity, 10] 及[10,15),于是：

• T3 id=10,再锁的范围内，所以阻塞

• T2 第一sql插入6的不阻塞，插入11主键在锁范围内，阻塞了

• T4 id=15，不在范围内，所以不阻塞