mysql8锁调研(实测)

这里只说innodb，why？因为MyISAM基本不用；为什么写这篇文档？因为昨天生产出了个事故：有张表，竟然无法select了？？也不能看表结构了？？，其他表正常；调研了一天，得出的结论是只有lock tables … write 才能造成，其他人无法select，但是工程中是没有这样的的语句的，最终原因还是没有找到。。。

一 锁分层

二 innodb层锁分类

1. 行级锁

• S 共享锁 ：允许获取到此锁的事务读取行
• X 排他锁 ：允许获取到此锁的事务update，delete行

2. 表级锁-意向锁

• IS 意向共享锁：事务打算对表中的行设置共享S锁
• IX 意向排他锁：事务打算对表中的行设置排他X锁

意向锁定协议如下

• 事务在获得表中某行上的共享锁之前，必须先获得表上的IS锁或更高级别的锁。
• 在事务可以获得表中某一行上的排他锁之前，它必须首先获得表上的IX锁。

sql加了什么锁？(以下都是自己的测试和猜测，如有不对请留言指正)

1. select … for share ,会设置IS锁，选择的行的S锁，如果没有数据，则只会加表的IS锁，还有表的S锁
2. select … for update,delete,update 会设置IX锁，选择的行的X锁，如果没有数据，则只会加表的IX锁，还有表的S锁
3. 普通select仅仅需要获取到表的S锁
4. lock tables … write 需要获取到表的X锁，和IX锁
5. lock tables … read 需要获取到表的S表，和IS锁

表级锁表级锁表级锁(重要的事说三遍) 类型兼容性如下

可以通过以下方式来验证，比如表aa,需要开启set AUTOCOMMIT = FALSE;
X: lock tables aa write;
S: lock tables aa read;
IX:select * from aa where 1=2 for update;
IS:select * from aa where 1=2 lock in share mode;

3.行锁

4. 间隙锁

间隙锁：指对索引记录之间间隙的锁，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;防止其他事务在列t中插入值15，无论列中是否已经有这样的值，因为范围内所有现有值之间的间隙都被锁定。

5.next-key

next-key锁是索引记录上的记录锁和索引记录前间隙上的间隙锁的组合。
InnoDB执行行级锁的方式是，当它搜索或扫描一个表索引时，它会对遇到的索引记录设置共享锁或排他锁。因此，行级锁实际上是索引记录锁。索引记录上的next-key锁还会影响该索引记录之前的“间隙”。也就是说，next-key锁是索引记录锁加上索引记录之前的间隙锁。如果一个会话对索引中的记录R有共享或排他锁，则另一个会话不能在索引顺序中紧靠R之前的间隙插入新的索引记录。
默认情况下，InnoDB操作在可重复读事务隔离级别。在这种情况下，InnoDB使用next-key锁来进行搜索和索引扫描，这可以防止幻像行

6.插入意图锁

插入意图锁是一种间隙锁，由插入操作设置。如果多个插入到同一个索引间隙的事务没有在间隙内的相同位置插入，则该锁将以这样的方式表示插入的意图:多个插入到同一个索引间隙的事务不需要相互等待。假设有值为4和7的索引记录。分别尝试插入值为5和6的独立事务，在获得插入行的排他锁之前，都使用插入意图锁锁住4和7之间的间隙，但不会相互阻塞，因为这两行不冲突。

7.AUTO-INC锁

AUTO-INC锁是一种特殊的表级锁，通过事务插入到具有AUTO_INCREMENT列的表中来实现。在最简单的情况下，如果一个事务向表中插入值，那么任何其他事务都必须等待对该表进行自己的插入，以便由第一个事务插入的行接收到连续的主键值。
innodb_autoinc_lock_mode配置选项控制用于自动增量锁定的算法。它允许您选择如何在自动递增值的可预测序列和插入操作的最大并发性之间进行权衡。

8.空间索引的谓词锁

InnoDB支持对包含空间列的列进行空间索引(参见11.4.9节“优化空间分析”)。
为了处理涉及空间索引的操作的锁，next-key锁在支持可重复读取或可序列化事务隔离级别时不能很好地工作。在多维数据中没有绝对的排序概念，因此不清楚哪一个是“下一个”键。
为了支持具有空间索引的表的隔离级别，InnoDB使用谓词锁。空间索引包含最小边界矩形(MBR)值，因此InnoDB通过对用于查询的MBR值设置谓词锁来强制对索引进行一致读取。其他事务不能插入或修改与查询条件匹配的行。

9.其他注意点

1.lock table命令会获取两个锁，一个mysql层的（S或X），一个是innode层的（IS或IX）
2.所有innodb的锁在事务提交后，都会释放，所以在autocommit=1时，使用lock table没有什么意义
3.执行普通select，delete，update等操作必须要先获取到表级别的S锁，所以只有执行了lock tables aa write后，才会造成select * from aa因为锁阻塞，其他情况select一定不会
4.执行update，delete，扫描 到的行都会加X锁，所以更新删除的时候一定要用唯一索引，否则无关的行将会被锁定，即使其他where条件会把这条数据过滤掉

三参考资料

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html