MySQL官方文档14.5.5.3 如何最小化和处理死锁

原文地址：https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html

14.5.5.3如何最小化和处理死锁

本节基于第14.5.5.2节“死锁检测和回滚”中有关死锁的概念信息。它解释了如何组织数据库操作以最大限度地减少死锁以及应用程序中所需的后续错误处理。

死锁是事务数据库中的一个典型问题，但它们并不危险，除非它们频繁发生，根本无法运行某些事务。通常，您必须编写应用程序，以便它们随时准备重新发出一个事务，如果它由于死锁而回滚。

InnoDB使用自动行级锁。即使在插入或删除单行的事务中，也可能会出现死锁。那是因为这些行动并非真正的“原子”。他们自动在插入或删除的行的索引记录（可能有几个）上设置锁。

您可以通过以下技术来应对死锁并减少它们发生的可能性：

• 在任何时候，发出SHOW ENGINE INNODB STATUS命令来确定最近发生死锁的原因。这可以帮助您调整应用程序以避免死锁。
• 如果经常出现死锁警告，请通过启用innodb_print_all_deadlocks配置选项来收集更多的调试信息。关于每个死锁的信息，不仅仅是最新的，记录在MySQL错误日志中。完成调试后禁用此选项。
• 如果事务由于死锁而失败，请始终准备重新发出事务。死锁并不危险。再试一次。
• 保持事务持续时间较短，以减少冲突。
• 在进行一系列相关更改后立即提交事务，以减少冲突。特别是，不要让交互式mysql会话在未提交事务的情况下长时间处于打开状态。
• 如果使用锁定读取（SELECT … FOR UPDATE或SELECT … LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如READ COMMITTED。
• 在事务中修改多个表或同一表中的不同行时，每次都要以一致的顺序执行这些操作。然后事务形成明确定义的队列并且不会发生死锁。例如，将数据库操作组织到应用程序中的函数中，或调用存储过程，而不是在不同位置编码多个相似的INSERT，UPDATE和DELETE语句序列。
• 为表添加精心挑选的索引。然后，您的查询需要扫描更少的索引记录，从而设置更少的锁。使用EXPLAIN SELECT来确定MySQL服务器认为哪些索引最适合您的查询。
• 使用更少的锁。如果您可以承受允许SELECT从旧快照返回数据，请不要将FOR UPDATE或LOCK IN SHARE MODE子句添加到它。在这里使用READ COMMITTED隔离级别是很好的，因为在同一事务中每次一致的读取都会从它自己的新快照中读取。
• 如果没有其他帮助，请使用表级锁序列化您的事务。将LOCK TABLES与事务表（例如InnoDB表）一起使用的正确方法是，使用SET autocommit = 0（不是START TRANSACTION），然后是LOCK TABLES开始一个事务，并在显式提交事务之前不调用UNLOCK TABLES。例如，如果您需要写入表t1并从表t2读取，则可以这样做：

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

表级锁可防止对表进行并发更新，从而避免死锁，但会降低对繁忙系统的响应速度。

• 序列化事务的另一种方法是创建一个仅包含单行的辅助“信号量”表。在访问其他表之前，让每个事务更新该行。这样，所有的交易都是以串行的方式进行的。请注意，InnoDB即时死锁检测算法也适用于这种情况，因为序列化锁是行级锁。使用MySQL表级锁时，必须使用超时方法来解决死锁问题。