14.7.5.3 如何最小化和处理死锁

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

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

InnoDB使用自动行级锁定。即使在仅插入或删除单行的事务中，也可能会遇到死锁。这是因为这些操作并不是 true 的“原子”操作；它们会自动对插入或删除的行的(可能是多个)索引记录设置锁定。

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

• 在任何时候，发出显示引擎的 INNODB 状态命令来确定最新死锁的原因。这可以帮助您调整应用程序以避免死锁。

• 如果频繁出现死锁警告引起关注，请通过启用innodb_print_all_deadlocks配置选项来收集更多的调试信息。有关每个死锁的信息，不仅是最新的死锁，还记录在 MySQL error log中。完成调试后，请禁用此选项。

• 如果由于死锁而失败，请始终准备重新发出事务。死锁并不危险。请再试一次。

• 保持 Transaction 小巧且持续时间短，以使 Transaction 不易发生冲突。

• 进行一系列相关更改后立即提交事务，以减少冲突的发生。特别是，不要长时间未提交事务而保持交互式mysql会话打开。

• 如果使用locking reads(选择...更新或SELECT ... LOCK IN SHARE MODE)，请尝试使用较低的隔离级别，例如READ COMMITTED。

• 修改事务中的多个 table 或同一 table 中的不同行集时，每次都要以一致的 Sequences 执行这些操作。然后，事务形成定义明确的队列，并且不会死锁。例如，将数据库操作组织到应用程序内的函数中，或调用存储的例程，而不是在不同的地方对INSERT，UPDATE和DELETE语句的多个相似序列进行编码。

• 将精选的索引添加到 table 中。这样，您的查询就需要扫描更少的索引记录，从而设置更少的锁。使用EXPLAIN SELECT来确定 MySQL 服务器认为哪些索引最适合您的查询。

• 使用较少的锁定。如果您有能力允许SELECT从旧快照返回数据，请不要在其上添加FOR UPDATE或LOCK IN SHARE MODE子句。在这里使用READ COMMITTED隔离级别是件好事，因为同一事务中的每个一致性读取均从其自己的新快照读取。

• 如果没有其他帮助，请使用 table 级锁序列化事务。对事务 table(例如InnoDBtable)使用LOCK TABLES的正确方法是，先以SET autocommit = 0(不是START TRANSACTION)后跟LOCK TABLES来开始事务，并且在明确提交事务之前不要调用UNLOCK TABLES。例如，如果您需要写入 tablet1并从 tablet2读取，则可以执行以下操作：

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

table 级锁可防止对 table 的并发更新，从而避免死锁，但代价是对繁忙系统的响应速度较慢。

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