mysql死锁自动检测_MySQL InnoDB死锁及死锁检测（四）

一、MySQL InnoDB死锁阐述

在MySQL中，当两个或以上的事务相互持有和请求锁，并形成一个循环的依赖关系，就会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。在一个事务系统中，死锁是确切存在并且是不能完全避免的。 InnoDB会自动检测事务死锁，立即回滚其中某个事务，并且返回一个错误。它根据某种机制来选择那个最简单(代价最小)的事务来进行回滚。偶然发生的死锁不必担心，但死锁频繁出现的时候就要引起注意了。InnoDB存储引擎有一个后台的锁监控线程，该线程负责查看可能的死锁问题，并自动告知用户。

在MySQL 5.6之前，只有最新的死锁信息可以使用show engine innodb status命令来进行查看。使用Percona Toolkit工具包中的pt-deadlock-logger可以从show engine innodb status的结果中得到指定的时间范围内的死锁信息，同时写入文件或者表中，等待后面的诊断分析。对于pt-deadlock-logger工具的更多信息可以参考手册。 如果使用的是MySQL 5.6或以上版本，您可以启用一个新增的参数innodb_print_all_deadlocks把InnoDB中发生的所有死锁信息都记录在错误日志里面。

产生死锁的必要条件

1. 多个并发事务(2个或者以上)；

2. 每个事务都持有锁(或者是已经在等待锁)；

3. 每个事务都需要再继续持有锁(为了完成事务逻辑，还必须更新更多的行)；

4. 事务之间产生加锁的循环等待，形成死锁。

总结：当两个或多个事务相互持有对方需要的锁时，就会产生死锁，如下图：

死锁实例

创建环境

create table money(id int primary key,price int);

insert into money values(1,1000);

insert into money values(2,1000);

1

2

3

createtablemoney(idintprimarykey,priceint);

insertintomoneyvalues(1,1000);

insertintomoneyvalues(2,1000);

事务A: 更新表，id=1的记录

mysql> start transaction;

Query OK, 0 rows affected (0.01 sec)

mysql> update money set price=2000 where id=1;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

1

2

3

4

5

6

mysql>starttransaction;

QueryOK,0rowsaffected(0.01sec)

mysql>updatemoneysetprice=2000whereid=1;

QueryOK,1rowaffected(0.03sec)

Rowsmatched:1 Changed:1 Warnings:0

事务B: 更新表，id=2的记录

mysql> start transaction;

Query OK, 0 rows affected (0.01 sec)

mysql> update money set price=2000 where id=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

1

2

3

4

5

6

mysql>starttransaction;

QueryOK,0rowsaffected(0.01sec)

mysql>updatemoneysetprice=2000whereid=2;

QueryOK,1rowaffected(0.00sec)

Rowsmatched:1 Changed:1 Warnings:0

事务A: 更新表，id=2的记录，此时会卡住(因为这条记录被加上了X锁)

mysql> update money set price=3000 where id=2;

1

mysql>updatemoneysetprice=3000whereid=2;

事务B: 更新表，id=1的记录，此时会报错事务进行回滚，并且事务1会执行更新id=2的记录

mysql> update money set price=3000 where id=1;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

1

2

mysql>updatemoneysetprice=3000whereid=1;

ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction

上述，事务抛出1213这个出错提示，即发生了死锁，上例中当两个事务都执行了第一条UPDATE语句，更新了一行数据，同时也锁定了该行数据，接着每个事务都尝试去执行第二条UPDATE语句，却发现该行已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。除非有外部因素接入才可能解除死锁。为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。

二、MySQL InnoDB死锁检测

尽量不出现死锁

在代码层调整SQL操作顺序，或者缩短事务长度，以避免出现死锁。

碰撞检测

当死锁出现时，Innodb会主动探知到死锁，并回滚了某一苦苦等待的事务。问题来了，Innodb是怎么探知死锁的？

核心就是数据库会把事务单元锁维持的锁和它所等待的锁都记录下来，Innodb提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要进入等待时，wait-for graph算法都会被触发。当数据库检测到两个事务不同方向地给同一个资源加锁(产生循序)，它就认为发生了死锁，触发wait-for graph算法。比如，事务1给A加锁，事务2给B加锁，同时事务1给B加锁(等待)，事务2给A加锁就发生了死锁。那么死锁解决办法就是终止一边事务的执行即可，这种效率一般来说是最高的，也是主流数据库采用的办法。

Innodb目前处理死锁的方法就是将持有最少行级排他锁的事务进行回滚。这也是相对比较简单的死锁回滚方式。死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型的系统，这是无法避免的，所以应用程序在设计必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

wait-for graph原理

我们怎么知道图中四辆车是死锁的？

他们相互等待对方的资源，而且形成环路！我们将每辆车看为一个节点，当节点1需要等待节点2的资源时，就生成一条有向边指向节点2，最后形成一个有向图。我们只要检测这个有向图是否出现环路即可，出现环路就是死锁！这就是wait-for graph算法。

Innodb将各个事务看为一个个节点，资源就是各个事务占用的锁，当事务1需要等待事务2的锁时，就生成一条有向边从1指向2，最后行成一个有向图。

等锁超时

死锁超时也是一种常见的做法，就是等待锁持有时间，如果说一个事务持有锁超过设置时间的话，就直接抛出一个错误，参数innodb_lock_wait_timeout用来设置超时时间。如果有些用户使用哪种超长的事务，你就需要把锁超时时间大于事务执行时间。在这种情况下这种死锁超时的方式就会导致每一个死锁超时被发现的时间是无法接受的。

不要太担心死锁，你可能会在MySQL error log中看到关于死锁的警告信息，或者在show engine InnoDB status输出中看到它。尽管看起来是一个可怕的名字，但deadlock不是一个严重的问题，对于InnoDB来说，通常不需要做任何纠正操作。当两个事务开始修改多个表时，如果访问表的顺序不同，会出现互相等待对方释放锁，然后才能继续处理的情况。MySQL会立刻发现这种情况并且终止较小的事务，允许其他的事务执行。

你的应用程序的确需要错误处理逻辑来重启该事务。当你重新执行相同的SQL语句时，原来的时间问题不再适用：要么其他的事务已经执行完成，这样你就可以执行事务了，要么其他的事务还在处理过程中，你的事务只能等它结束。

如果不断警告发生死锁，你可能要review你的应用程序源代码，调整SQL操作顺序，或者缩短事务长度。你可以启用innodb_print_all_deadlocks选项，把deadlock信息记录到MySQL的错误日志总，而不是仅仅通过show e