MySQL gap lock、net-key lock 实验记录

实验记录

打开两个session

set innodb_lock_wait_timeout=5; 设置 Lock wait timeout exceeded 为5秒，这样等待被锁阻塞的时间能短点 -_-! 便于实验进行

表结构

其中id为主键；age为普通索引
in name age
1 name1 15
5 lucy 18
11 南风 22
15 洛神赋 28

存在的非唯一索引

1. select * from user where age = 22 for update;
   锁住[18,28]

2. select * from user where age > 22 for update;
   锁住[22,+x]

3. select * from user where age < 22 for update;
   锁住[-x,22)

不存在的非唯一索引

1. select * from user where age = 20 for update;
   锁住[18,22)

2. select * from user where age < 20 for update;
   锁住(-x,22)

3. select * from user where age > 20 for update;
   锁住[18,+x)

非唯一索引区间

1. select * from user where age > 17 and age < 23 for update;
   锁住[15,28)

存在的唯一索引记录

1. select * from user where id = 11 for update;
   锁住11（只有record锁）

2. select * from user where id > 11 for update;
   锁住(11,+x)

3. select * from user where id < 11 for update;
   锁住(-x,11]

不存在的唯一索引记录

1. select * from user where id = 10 for update;
   锁住(5,11)

2. select * from user where id > 10 for update;
   锁住(5,+x)

3. select * from user where id < 10 for update;
   锁住(-x,11)

唯一索引左右区间

1. select * from user where id > 10 and id < 12 for update;
   锁住(5,15]

2. select * from user where id > 3 and id < 12 for update;
   锁住(1,15]

举一个可以导致生产环境爆炸的例子

假如因为某个原因（比如业务逻辑设计不合理）导致某次update 语句如下

update user set age = 20 where id = 10000;

可以注意到id远大于表中的最大id，这样就会产生一个(15, +x)的锁，配合其他并发insert事务就极易产生死锁

总结

按照“如何才能不出现幻行”的思路比较好确定锁范围