关于MySQL锁机制的一些理解

最近在看关于mysql锁机制的一些问题，通过不断的查阅资料和实验，有了点自己的理解，以下实验基于innodb、rr隔离级别；

mysql锁机制

按粒度划分：行锁、表锁、页锁

行锁：record lock 、gap lock、next-key lock

表锁：意向排他锁IX、意向共享锁IS

表结构和数据如下

其中，id为主键(聚簇索引)、test为二级索引；

什么时候会加锁？什么时候不加锁？

1不加锁情况

执行select * from test     

 

 结论：通过查看innodb引擎状态，可知普通的select并没有加锁，而是由mvcc去实现的；

2加锁情况

一、插入操作

执行 insert into test values(4,'第四行','four');

查看引擎状态

 结论：查看引擎状态可知， innodb对test 加了IX，意向排他锁；

二、删除操作

查看引擎

 从结果上看，innodb分别给test表加了IX意向排他锁和主键索引上加了record lock纪录锁

回滚一下，执行

 可以发现，当使用二级索引加锁时，innodb分别给主键索引和二级索引加了X排他锁，并没有加gap lock间隙锁；

结论：  当使用主键索引加锁 时，mysql会先给表加  表锁 ，然后给主键加 record lock纪录锁；

当使用二级索引加锁时，mysql会分别给二级索引列和主键索引列加锁；

三、修改操作

执行

与上述一样

四、当前读

执行

select * from  test  lock in  share mode；

可以发现mysql给表加了  IS意向共享锁、和每个主键索引列加了 record lock纪录锁

执行

 select * from test for update；

可以发现mysql给表加了 IX意向排他锁，每个主键索引列加了record lock纪录锁

使用索引列加锁

执行

select * from  test  where  test=‘second’  for update

查看引擎

可以发现，当使用索引列加锁时，mysql会先给表加表锁，然后对满足条件的索引列加锁

总结：当使用  当前读读取数据时， mysql首先会给表  添加表锁，然后给各个索引列添加 record lock， 当不使用索引列加锁时， mysql会通过主键索引全盘扫描，并将每个主键索引列都加行锁；当使用索引可加锁时， mysql只会将满足条件的索引列进行加行锁；

什么时候出现gap lock、next-key lock？

执行

 

查看引擎

 

可以发现   ，插入操作被阻塞了；符合索引条件的列只有6这一列，但是mysql锁住了5 、6、7列以及7后面的列，这里mysql使用了next-key lock  临键锁，简单来说就是record和gap lock的结合，从实验的结果来看，mysql不仅锁住了符合条件的索引列，还锁住了条件列前面的间隙和后面的间隙，即范围区间为(5,6) (6,6),(6,>)，  最重要的一点是，临键锁 只有在给 非唯一索引列加锁的情况下才会出现， 当使用唯一索引加锁时，mysql先会给表加意向锁，然后给命中的索引列加record lock纪录锁；当使用非唯一索引加锁时，mysql先给表加意向锁，然后使用临建锁，给索引列加record lock，并给间隙添加gap lock间隙锁，打印锁状态如下；

 

什么是意见锁？意见锁之间是否会阻塞？

意见锁：意见共享锁IS、意见排他锁IX

开启两个事务分别执行

从结果上看，用两个事务分别给同一个表 加IS意见共享锁，并不会阻塞；

再次执行

 

可以发现，IS同样不会阻塞IX

执行

从上面结论可以发现 ，mysql会对该表添加意向排他锁IX，并对test=4的索引列添加行级排他锁X

开启另一个事务，执行

很流畅的查询完成

结论：IX与IX之间同样不会阻塞 

意向锁之间关系如下

虽然，意向锁之间完全不会阻塞，但是行锁之间会发生阻塞，从上面实验结果来看，mysql加锁前，总是会先给表添加意向锁，意向锁之间本身不会发生阻塞，但是有时候却发生了阻塞，这是什么原因导致的？

继续开始实验

老规矩

开启两个事务，同时执行

 执行成功，没有发生阻塞

这两条语句，会同时对test表加IS锁，并且对命中索引列加S锁

S锁之间不会阻塞，执行成功

开启两个事务，同时执行

 

阻塞成功，接着查看锁使用情况

看不懂没关系，百度翻译结果如下

表锁表'cloud_base'`测试'trx id 103481锁定模式九

记录锁定空间id 675第5页n位72索引表'cloud_base'的ind_test`测试'trx id 103481锁定模式X等待

记录锁，堆2号物理记录：n_字段2；紧凑的格式；信息位0

0:len1；十六进制34；asc 4；；

1：len 1；十六进制31；asc 1；；

---交易103480，有效379秒

5个锁结构，堆大小1136，4个行锁

MySQL线程id 142，OS线程句柄14204，查询id 1816 localhost:：1根开始

显示引擎innodb状态

表锁表'cloud_base'`测试'trx id 103480锁定模式为

记录锁定空间id 675第5页n位72索引表'cloud_base'的ind_test`测试'trx id 103480锁定模式S

记录锁，堆2号物理记录：n_字段2；紧凑的格式；信息位0

0:len1；十六进制34；asc 4；；

1：len 1；十六进制31；asc 1；；

记录锁定空间id 675第3页n位80索引表'cloud_base'的主索引`测试'trx id 103480锁定模式S锁定rec但不锁定gap

记录锁，9号堆物理记录：n_字段5；紧凑的格式；信息位0

0:len1；十六进制31；asc 1；；

1：len 6；十六进制000000018544；asc D；；

2：len 7；hex 2C00001A11827；asc；；

3:len9；六角e7acace4b880e8a18c；asc；；

4：len 1；十六进制34；asc 4；；

记录锁定空间id 675第5页n位72索引表'cloud_base'的ind_test`测试'trx id 103480锁定模式S在rec前锁定间隙

记录锁，堆6号物理记录：n_字段2；紧凑的格式；信息位0

0:len1；十六进制35；asc 5；；

1：len 1；十六进制32；asc 2；；

表锁表'cloud_base'`测试'trx id 103480锁定模式九

 

可以发现，事务二添加行级X锁，一直在等待，这条纪录已加上S锁，行级锁X与S之间发生阻塞；所以，意向锁之间不会阻塞，行级锁之间会阻塞，且需在给同一索引列加锁的情况下，会发生死锁；

 

 

 

 

注意！注意！注意！

这里在测试的时候，出现了一个问题

当我在索引命中的情况下加锁，发现mysql还是锁住了全部的列，跟预想的锁住符合条件的索引列不一致；

发现即使使用了索引 ，但扫描量超过全表的20%~30%，mysql还是会使用全盘扫描；