锁（case篇）

庶民村村长

case1（表锁的读-写-读阻塞）

上篇文档中提到过

WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it.
对于读-写-读的情况，由于锁的优先级较高，如果申请写的session迟迟获取不到锁，会阻塞后续其他session申请读锁；

先看正常情况，表锁的读锁是可以加多个的，如下，通过两个查询命令也可以看到确实同时加上了，没有阻塞；

1. //console1
2. lock tables simple read;
3. //console2
4. lock tables simple read;

复制代码

select * from performance_schema.metadata_locks;

show OPEN TABLES where In_use > 0;

但是在两次读中间插入一次写锁的获取，后面的读锁也会同时被阻塞

1. //console1
2. lock tables simple read;
3. //console2
4. lock tables simple write;//被console1阻塞
5. //console3
6. lock tables simple read;//被console2阻塞

复制代码

实验证明确实如文档所说，原理还在研究中...
case2（元数据锁读-写-读）

mysql45讲中提到的一个问题，具体分析见mysql MDL读写锁阻塞，以及online ddl造成的“插队”现象_花落的速度的博客-CSDN博客

case3(next-key lock 和 primary key)

在分析之前，先贴一下45讲的总结,该总结版本是 5.x 系列 5 lock in share mode ;select * from performance_schema.data_locks;//console2都会被阻塞insert into simple (id,name,type,seq) value (6,5,5,5);insert into simple (id,name,type,seq) value (36,5,5,5);[/code]

id>=5

和上面的唯一区别就是多了个等于5，那么5上是临键锁还是行锁呢？我觉得是行锁，因为优化1，而且这样和我们的认知也是比较符合的；
实际看到确实是这样；

1. CREATE TABLE `simple` (
2.   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
3.   `name` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字符',
4.   `seq` bigint NOT NULL COMMENT '消息序号',
5.   `type` tinyint NOT NULL COMMENT '类型，tinyint值',
6.   `version` int NOT NULL DEFAULT '1' COMMENT '版本值',
7.   `msg` text COLLATE utf8mb4_bin COMMENT '消息',
8.   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
9.   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
10.   `yn` tinyint NOT NULL DEFAULT '1' COMMENT '是否有效',
11.   `uni` int NOT NULL COMMENT '唯一索引',
12.   PRIMARY KEY (`id`),
13.   UNIQUE KEY `unidx` (`uni`),
14.   KEY `seqidx` (`seq`)
15. ) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='简单测试表'

复制代码

id>5 and id105 and uni105 and uni105 and uni105 and uni130和上面的id>30结果一样，就不贴了

总结：对于唯一索引来说，因为存在主键，那么会产生回表操作，回表操作会给主键再加一把锁；而那个bug依旧存在，只有主键索引的修复了，非主键唯一索引依然存在这个bug;
case5（索引加在哪）

1. start transaction ;
2. select * from simple where id = 15 lock in share mode ;
3. select * from performance_schema.data_locks;

复制代码

现在我们已经清楚，执行完console1之后，会给unidx加一个行锁，因为没有回表，所以主键上没有锁；那么console2能否成功执行呢？
答案是

1. start transaction ;
2. select * from simple where id = 16 lock in share mode ;
3. select * from performance_schema.data_locks;
4. //console2
5. start transaction;
6. insert into simple (id,name,type,seq) value (16,5,5,5);//会被阻塞
7. select * from simple where id=20 for update ;//发现这行可以执行成功

复制代码

我个人理解，是因为锁是加在索引上的，而索引是列维度的，不是行维度的；console2执行语句只会去判断id这个索引上，有没有5这个锁；
接下来我们反过来

1. //console1
2. start transaction ;
3. select * from simple where id>5 lock in share mode ;
4. select * from performance_schema.data_locks;
5. //console2
6. 都会被阻塞
7. insert into simple (id,name,type,seq) value (6,5,5,5);
8. insert into simple (id,name,type,seq) value (36,5,5,5);

复制代码

你试着一起敲一下就会发现，咦，console2怎么阻塞了呢？按上面所说的，不是不应该吗？
实际上console1的执行锁的确实是id；
但是你console2的执行，会回表啊，会尝试给id加写锁，但是id已经加了读锁了，所以自然不行了；
所以，不要盲目的只看查询条件，要理解当前语句都会加什么锁，是否和已经加的锁冲突；
最后，我们再来看一个附加题，下面两个语句加的锁是否一样呢？

1. start transaction ;
2. select * from simple where id>=5 lock in share mode ;
3. select * from performance_schema.data_locks;

复制代码

在我没有尝试之前，我理解都没有回表，那么就应该一个是唯一索引加读锁，一个是唯一索引加写锁；
但是实际结果却是lock in share mode是对的，for update会认为你要更新语句，自动给主键加锁了

case6（next-key lock 和index）

吸取uni的教训，我给seq的值都加了200，现在这个表是这样的

seq=215

1. start transaction ;
2. select * from simple where id>5 and id<20 lock in share mode ;
3. select * from performance_schema.data_locks;

复制代码

除了意向锁，其他三个我们一个个看；
seqidx(S)这行是普通索引执行时加的临键锁，由于不是唯一索引，所以不能优化（因为可能存在重复）
primary(S,REC_NOT_GAP)这是回表操作带来的
seqidx(S,GAP)这行是因为不是唯一索引，所以在查询到匹配的值之后不会立马停止（因为后面可能还存在相同的值），所以必须要到不符合条件的值为止，而所有查询过的都会加索引，所以存在一个间隙锁。
seq=216

1. start transaction ;
2. select * from simple where uni = 15 lock in share mode ;
3. select * from performance_schema.data_locks;

复制代码

我理解，应该是从205开始查，查到第一个不符合条件的值是215，加上中间没有回表，所以就这一个锁；理论应该是(215,220]，但由于优化2，所以退化为间隙锁；
seq>215 and seq215 and seq 215 and seq 215 and seq 230和前面unidx>130和id>30都一样
case7(next-key和没有索引）

1. alter table simple drop index  seqidx;start transaction ;
2. select * from simple where id>5 and id<20 lock in share mode ;
3. select * from performance_schema.data_locks;

复制代码

前面提到过，查询条件匹配不到索引或者只是索引的一部分，这个时候为了保证数据的准确性，会给整个表“加锁”，其实给表里所有的记录都加锁(这里我不知道描述的对不对，因为表锁！=所有记录加锁，虽然效果相似，但并不是一个东西）.

同时因为这个表存在意向读锁，通过lock tables simple write 加写的表锁会冲突；
参考文档：

06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？-极客时间
mysql MDL读写锁阻塞，以及online ddl造成的“插队”现象_花落的速度的博客-CSDN博客

来源:https://www.cnblogs.com/qisi/p/innodb_lock_case.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！