测试MySQL锁的问题
InnoDB支持三种行锁：
-
Record Lock：单个行记录上面的锁
-
Gap Lock：间隙锁，锁定一个范围，但不会包含记录本身
-
Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。
比如一个索引有10，11，13，20，那么该索引可能被Next-Key Locking锁住的区间为：
(-∞,10], (10,11], (11, 13], (13, 20], (20, +∞]
1 Record Lock
InnoDB对于primary key（如果是多个列，且查询了所有的列，即点对点查询），会把间隙锁降级为Record Lock，比如下面的例子：
表的schema为：
create table t(
a int PRIMARY KEY
);
insert into t select 1;
insert into t select 2;
insert into t select 5;
session A和session B的操作顺序如下：
/*session a*/
begin;
select * from t where a = 5 for update;
/*session b*/
begin;
/*不会阻塞，因为对于唯一索引，innodb采用的是record lock，即锁住单行*/
insert into t select 4;
commit;
/*session a*/
commit;
2 Next-Key Lock
如果是二级索引，因为不具备唯一性，为了防止幻读的产生，InnoDB会锁住相关的范围，比如下面的例子：
表的schema为：
/*测试Next-Key Lock的问题*/
create table z(
a INT,
b INT,
PRIMARY KEY(a),
KEY(b)
);
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;
session A和session B的操作顺序如下：
/*session a*/
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;
/*按照书里面讲的，这个语句会锁住范围(1,3)(3,6)，所以我们尝试下*/
select * from z where b = 3 for update;
/*session b*/
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;
/*阻塞，因为session a中锁定了范围，所以这里也会被锁住*/
select * from z where a = 5 lock in share mode;
/*下面的两个语句也会被锁住*/
insert into z select 4,2;
insert into z select 6,5;
/*但是下面的语句就不会被锁住，因为不在锁定的范围里面*/
insert into z select 8,6;
insert into z select 2,0;
insert into z select 6,7;
commit;
/*session a*/
commit;
2 死锁测试
表的schema如下：
create table t(
a int PRIMARY KEY
);
insert into t values(1),(2),(4),(5);
测试MySQL版本：8.0.20
-
先来看最常见的AB-BA锁问题：
时间线 session A session B 1 begin;
select * from t where a = 2 for update;```2 begin;
select * from t where a = 4 for update;3 /*session a：尝试更新4*/
update t set a = a + 100 where a = 4;4 /*session b：尝试更新2，直接发生死锁*/
update t set a = a + 100 where a= 2;
在session B的时间点4的时候，mysql直接报错，并对sessionB进行回滚操作：
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
-
接下来是另外一种较为隐蔽的死锁；
先看第一种情况
时间线 session A session B 1 begin;
select * from t where a = 4 for update;2 /*因为sessionA中锁定了a=4这一行，所以这里会一直阻塞住，但是，不同的是，它成功的获取到了1,2的行锁，正在等待4的锁*/
begin;
select * from t where a <= 4 lock in share mode;3 /*发生死锁，虽然session b在等待，但是session b已经获取到了部分的gap lock，即[1,3)，所以这里直接报死锁错误*/
insert into t values(3);这里的情况稍微有些不同，sessionB虽然被阻塞了，但是它成功的获取到了a=1，a=22的行锁（Record Lock），此时正在等待a=4的行锁，如果在session A的时间点3的时候，我们允许插入成功，那么sessionA提交后，sessionB lock in share mode成功后，是不是应该在回过头来获取3的Record Lock？这样子是不合理的，所以Mysql这里就认定为死锁，直接回退了undo比较小的事务，那么问题来了，Mysql是怎么做到的呢？个人猜测可能是通过gap lock来实现的，只是在
performance_schema.data_locks表中查不到而已，进一步的情况需要查看源代码。再来看第二种情况：
时间线 session A session B 1 begin;
select * from t where a = 4 for update;
select * from t where a = 2 for update;
/*比第一种情况就多了这个加粗的语句*/2 /*因为sessionA中锁定了a=2和a=4这两行，所以这里会一直阻塞住，但是，不同的是，它成功的获取到了1的行锁，正在等待2的锁*/
begin;
select * from t where a <= 4 lock in share mode;3 /*和第一种情况不同的是，这里直接成功了，并没有认定为死锁*/
insert into t values(3);这里之所以没有问题，是因为session B在时间点2的时候，已经获取到了a=1的Record Lock，此时正在等待a=2的行锁，因为3>2，所以允许session A对a=3加上X锁，假设session A在时间点3后commit了，这个时候session B在时间点2的语句也成功了，查看
performance_schema.data_locks可以看到，session B获取到了a=1, a=2, a=3, a=4的行锁。这是Mysql实现锁的一个细节之处，需要好好理解下。