MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁

如果此时，T1 rollback释放掉其所持有的index record lock，则T2和T3等待获取的shared next-key lock都成功了，然后，T2和T3争夺主键1上的index record lock，于是T2和T3就死锁了，因为它俩都持有shard next-key lock，双方谁都不会放弃已经得到的shared next-key lock，于是，谁都无法得到主键1的index record lock。

需要明确的是死锁的可能性并不受隔离级别的影响，因为隔离级别改变的是读操作的行为，而死锁是由于写操作产生的。死锁并不可怕，MySQL会选择一个牺牲者，然后，在系统变量innodb_lock_wait_timeout指定的秒数达到后，自动回滚牺牲者事务;从MySQL5.7开始，新加入了系统变量innodb_deadlock_detect(默认ON)，如果开启此变量，则MySQL不会再等待，一旦探测到死锁，就立即回滚牺牲者事务。

上图演示了：在上图的状态下，当T1 commit时，T1释放了主键1上的index record lock，于是T2和T3等待获取的shared next-key lock都成功了，然后，T2和T3争夺主键1上的index record lock，于是T2和T3死锁了，因为它俩都持有shard next-key lock，双方谁都不会放弃已经得到的shared next-key lock，于是，谁都无法得到主键1的index record lock。

五、performance_schema.data_locks中能看到全部的锁吗?

显而易见，performance_schema.data_locks并未显示全部的锁，那么，它显示了哪些锁呢?很不幸，我并未找到文档说这事，尽管文档(https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html)说：“事务持有的每一个锁 以及 事务被阻塞的每一个锁请求，都在该表中占据一行”，但，我们很多例子都表明，它并未显示全部的锁。根据我的试验，我猜测performance_schema.data_locks显示的是WHERE条件所触碰到的索引上的锁，“WHERE条件所触碰到的索引”是指SQL实际执行时所使用的索引，也就是SQL执行计划的key列所显示的索引，正因为此，INSERT时看不到任何锁，update g set a=a+1 where b=22时只看到idx_b上的锁。需要强调的是，这是我自己试验并猜测的，我并未在文档中看到这种说法。

假设T1和T2两个事务操作同一个表，先执行T1，此时尽管performance_schema.data_locks中只显示T1的WHERE条件所触碰到的索引上的锁，但是，事实上在T1的WHERE条件触碰不到的索引上，也是会设置锁的。尽管表的索引idx并未被T1所触碰到，即performance_schema.data_locks显示T1在索引idx并没有设置任何锁，但，当T2执行 锁定读/插入/更新/删除 时触碰到了索引idx，T2才恍然发现，原来T1已经在索引idx上加锁了。

我们来看下面的三个例子

“performance_schema.data_locks无法看到全部锁”示例一

上图演示了：T1执行时，只触碰到了索引idx_b，T1执行完后，在performance_schema.data_locks中只能看到idx_b上的锁，看起来T1并未在idx_a上设置任何锁;但，当T2执行触碰到了索引idx_a时，T2才恍然发现，原来T1已经在idx_a上设置了index record lock啦。

“performance_schema.data_locks无法看到全部锁”示例二

插入新行时，会先设置insert intention lock，插入成功后再在插入完成的行上设置index record lock。

上图演示了：T1插入了新行，但，在performance_schema.data_locks中，我们既看不到T1设置的insert intention lock，也看不到T1设置的index record lock。这是因为T1的WHERE条件并未触碰到任何索引(T1根本不存在WHERE条件)，因此我们看不到T1的这两个锁;但，当T2要删除T1新插入的行时，T2才恍然发现，原来T1已经在索引c2上设置了index record lock啦。

“performance_schema.data_locks无法看到全部锁”示例三

插入新行时，本来是不会在performance_schema.data_locks中显示insert intention lock的，因为插入时WHERE条件并未触碰到任何索引(插入时根本不存在WHERE条件)。

上图演示了：T2插入新行时的insert intention lock 和 T1的gap lock冲突了，于是，我们得以在performance_schema.data_locks中观察到T2插入新行时需要请求insert intentin lock。

【编辑推荐】

1. 作为DBA不得不知的5大管理MYSQL的开源免费利器
2. 问题SQL优化：从超过300s优化到1s案例分析
3. MySQL单表数据不要超过500万行：是经验数值，还是黄金铁律？
4. 自制小工具大大加速MySQL SQL语句优化(附源码)

（编辑：西安站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!