彻底理解mysql innodb的死锁

梦里梦外; 2022-10-02 10:50 175阅读 0赞

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

死锁的可能性不受隔离级别的影响，因为隔离级别会更改读取操作的行为，而死锁是由于写入操作而发生的。当事务锁定多个表中的行时（通过诸如：UPDATE 或 SELECT ... FOR UPDATE），可能会发生死锁，但顺序相反。当这些语句锁定索引记录的范围和间隙时（参阅之前分享的[行锁、间隙锁及Next-KEY][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`][LOCK TABLES]语句设置的表锁或非innodb存储引擎设置的锁，innodb无法检测到死锁。但可以通过设置`innodb_lock_wait_timeout`系统变量的值来解决这个情况。

如何选择死锁牺牲品呢？

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

为何要禁用死锁检测？

在高并发系统上，当许多线程等待相同的锁时，死锁检测可能会导致速度减慢。有时，当死锁发生时，禁用死锁检测并依靠 [`innodb_lock_wait_timeout`][innodb_lock_wait_timeout] 设置进行事务回滚可能更有效。可以使用 [`innodb_deadlock_detect`][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表级锁，必须使用超时方法来解决死锁。![92768043][]

### 总结 ###

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

### **参阅资料** ###

*  [Deadlocks in InnoDB][]

[Next-KEY]: https://blog.csdn.net/alex_xfboy/article/details/87866876
[deadlock detection]: https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_deadlock_detection
[LOCK TABLES]: https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
[innodb_lock_wait_timeout]: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
[innodb_deadlock_detect]: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_deadlock_detect
[92768043]: /images/20220112/95bc335d18e1493d99fedea7d4e1d9d9.png
[Deadlocks in InnoDB]: https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks.html