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

对于主键或唯一索引，当有重复键错误(duplicate-key error)时，会在 重复的索引记录上 设置 shared next-key lock或shared index record lock。这可能会导致死锁。

上图演示了：T1在主键1上设置exclusive index record lock。T2和T3插入时，会产生重复键错误，于是T2和T3都在主键1上设置了shared next-key lock。如上图所示

如果此时，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。

（编辑：晋中站长网）

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