MySQL error : Deadlock found when trying to get lock; try restarting transaction

huangzejun 2018-04-17 原文

在使用 MySQL 时，我们有时会遇到这样的报错：“Deadlock found when trying to get lock; try restarting transaction”。

在 14.5.5.3 How to Minimize and Handle Deadlocks 中有这样一句话：

Deadlocks are not dangerous. Just try again.

死锁不危险，重试一下就行。

实际上这个建议非常实用。

我们回顾一下死锁发生的四个条件：

资源的独占性（在某一时刻，最多只能被一个事务访问）；
请求与保持（事务获取到锁后，不会主动释放）；
不可剥夺（事务无法获取另一个事务拥有的锁）；
循环等待（即事务 A 获取到锁 Lock1，等待 Lock2，同时事务 B 获取到锁 Lock2，等待 Lock1，此时，事务 A 和 B 循环等待对方释放各自需要的锁）。

值得注意的是，InnoDB 在绝大部分错误发生时都不会回滚（如：等待锁超时不会回滚）；

只有一个例外，那就是发生死锁时，InnoDB 会回滚一个影响最小的事务（直接破坏了上面的第2点，这个事务就成为了 victim）。此时我们只要重试一下这个 victim 事务，那么，所有的事务都会成功提交。

同时，14.5.5.3 How to Minimize and Handle Deadlocks 中另一段话很重要：

InnoDB uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really “atomic”; they automatically set locks on the (possibly several) index records of the row inserted or deleted.

insert / delete 一行也可能引发死锁。

因为这些操作不是原子性的，它们在执行中会加 index record lock。

例子很好构造。

比如有一张表：create table t1 (id int PRIMARY KEY, name char(20) key);

有两个 session 并发：

session 1:

insert into t1 (id, name) values(5, ‘Branden’);

session 2:

select * from t1 where name > ‘B’ and id >3;

session1 访问 name>’B’，会在 Ben、Bob上加 X 锁，并且在 Alex 与 Ben、Ben 与 Bob、Bob 与 Cathy 间加 Gap lock，在聚簇索引 id 为 1、3上加X锁；接下来要获取聚簇索引上 id 为 6、7的 X 锁，以及（3, 6）、（6， 7）间的 gap lock。

session2 先获取聚簇索引上（3， 6）间的 gap lock；接下来尝试获取 name 索引上（Ben, Bob）、（Bob, Cathy）间的 gap lock。

最后发生死锁。

怎么消除呢？让 select 中 where 子句先访问 id ，再访问 name。

可以阅读这篇文章：http://hedengcheng.com/?p=771#_Toc374698321。

另外，有必要了解各种语句需要的锁：14.5.3 Locks Set by Different SQL Statements in InnoDB。

参考资料：

MySQL 5.7 Reference Manual:

14.5.2.3 Consistent Nonlocking Reads

14.5.2.4 Locking Reads

14.5.5 Deadlocks in InnoDB

Stack Overflow:

How to avoid mysql ‘Deadlock found when trying to get lock; try restarting transaction’