彻底理解mysql innodb的死锁

死锁只发生在并发的情况，也就是说你的程序是串行的是不可能发生死锁。通常表现为两个事务都在等待某个资源可用而无法继续进行，因为每个事务都持有另一个事务需要的锁，而它们都不会释放所持有的锁。为此，InnoDB引擎有一个后台的锁监控线程，它负责查看可能的死锁问题，并自动告知用户。可以通过 innodb_deadlock_detect 配置选项禁用死锁检测，innodb还提供了innodb_lock_wait_timeout 配置在死锁情况下回滚事务。

死锁的可能性不受隔离级别的影响，因为隔离级别会更改读取操作的行为，而死锁是由于写入操作而发生的。当事务锁定多个表中的行时（通过诸如：UPDATE 或 SELECT ... FOR UPDATE），可能会发生死锁，但顺序相反。当这些语句锁定索引记录的范围和间隙时（参阅之前分享的行锁、间隙锁及Next-KEY），也可能发生死锁。

死锁示例

话不多说直接上代码看死锁如何发生的：

CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1);
--客户端A
START TRANSACTION;
SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;

--客户端B
START TRANSACTION;
DELETE FROM t WHERE i = 1;

客户端B报错：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

因为删除操作需要X锁，无法获取该锁，因为它与客户端A持有的S锁不兼容

死锁检测和回滚

死锁检测（ deadlock detection）默认是启用的，InnoDB自动检测事务死锁并回滚一个或多个事务以打破死锁。当InnoDB执行事务的完全回滚时，该事务设置的所有锁都将被释放。

另外，如果频繁发生的死锁，也可以通过启用 innodb_print_all_deadlocks配置将有关所有死锁的信息打印到mysqld错误日志中。每个死锁的信息，不仅仅是最新的死锁，都记录在mysql错误日志中，完成调试后禁用此选项。

什么情况下死锁检测不会生效？

如果涉及mysql的 LOCK TABLES语句设置的表锁或非innodb存储引擎设置的锁，innodb无法检测到死锁。但可以通过设置innodb_lock_wait_timeout系统变量的值来解决这个情况。

如何选择死锁牺牲品呢？

我们知道：SQL Server会把它认为取消或回滚代价最小的连接作为默认的死锁牺牲品。而InnoDB尝试选择要回滚的小事务，其中事务的大小由插入、更新或删除的行数决定。

为何要禁用死锁检测？

在高并发系统上，当许多线程等待相同的锁时，死锁检测可能会导致速度减慢。有时，当死锁发生时，禁用死锁检测并依靠 innodb_lock_wait_timeout 设置进行事务回滚可能更有效。可以使用 innodb_deadlock_detect配置选项禁用死锁检测。

如何最小化和处理死锁

您可以使用以下技术来处理死锁并降低发生死锁的可能性：

1. 随时发出show engine innodb status命令以确定最近死锁的原因。如果InnoDB监视器输出的（LATEST DETECTED DEADLOCK ）节点包含一条消息：“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,” ，这表示等待列表上的事务数已达到200个限制。超过200个事务的等待列表将被视为死锁，试图检查等待列表的事务将回滚。如果锁定线程必须查看等待列表上事务拥有的超过1000000个锁，则也可能发生相同的错误。

2. 启用innodb_print_all_deadlocks配置，前面讲过

3. 保持事务的小规模和短持续时间，以减少它们发生冲突的可能性

4. 所有的相关更改请立即提交事务，以减少它们发生冲突的可能性。尤其是：不要让交互式MySQL会话在未提交事务的情况下长时间处于打开状态

5. 使用较低的隔离级别，如果使用锁定读取(SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)，建议使用读提交的级别

6. 一致的顺序执行，在一个事务中修改多个表或同一个表中的不同行集时，每次都以一致的顺序执行这些操作，然后事务形成定义良好的队列，并且不会死锁

7. 索引，选择最适合查询列的建索引，保证的查询可以扫描更少的索引记录，从而设置更少的锁

8. 使用更少的锁，如果您可以允许select从旧快照返回数据，请不要使用FOR UPDATE 或 LOCK IN SHARE MODE子句。这里使用read-committed隔离级别是很好的，因为同一事务中的每个一致读取都是从自己的新快照读取的。

9. 使用表级锁序列化事务（表级锁防止对表进行并发更新，以牺牲繁忙系统的响应能力来避免死锁），lock tables 是用来加表级锁，但是是MySQL的server层来加这把锁的。当innodb_table_locks = 1 (the default) 以及 autocommit = 0的时候，innodb能够感知表锁，同时server层了解到innodb已经加了row-level locks。否则，innodb将无法自动检测到死锁，同时server无法确定是否有行级锁，导致当其他会话占用行级锁的时候还能获得表锁。

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

10. 序列化事务，创建一个只包含一行的辅助“信号量”表。在访问其他表之前，让每个事务更新该行。这样，所有事务都以串行方式发生。注意，在这种情况下，InnoDB即时死锁检测算法也可以工作，因为序列化锁是一个行级锁。对于MySQL表级锁，必须使用超时方法来解决死锁。

总结

为了减少死锁的可能性，请使用事务而不是锁表语句。使插入或更新数据的事务足够小，以至于它们不会长时间保持打开状态。当不同的事务更新多个表或大范围的行时，在每个事务中请使用相同的操作顺序（SELECT ... FOR UPDATE）。在select中使用的列上创建索引（SELECT ... FOR UPDATE 和 UPDATE ... WHERE）。

参阅资料

• Deadlocks in InnoDB