MySQL InnoDB如何应付死锁

千家信息网最后更新 2024年11月11日MySQL InnoDB如何应付死锁死锁是事务处理型数据库系统的一个经典问题，但是它们并不是很危险的， 除非它们如此地频繁以至于你根本处理不了几个事务。 当因死锁而产生了回滚时，你通常可以在你的应用程序中重新发出一个事务即可。

InnoDB 使用自动地行级锁定。你可能恰好在插入或删除单一一条记录时产生死锁。 这是因为这些操作并不是真正"原子(atomic)"级的：他们会自动地在锁定 inserted/deleted 行的索引记录(可能有几个)。


可以通过下面所示的技巧来应付死锁或减少死锁的次数：

在 MySQL >=3.23.52 和 >= 4.0.3 的版本中使用 SHOW INNODB STATUS 来确定引起最后一个死锁的原因。这可以帮助你调整你的应用程序来避免死锁。
总是准备在因死锁而发生错误时重新发出一个事务。死锁并不危险。仅仅只需重试一遍。
经常提交你的事务。小的事务有较少的碰撞可能。
如果使用锁定读取 SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE，尽量使用较低的隔离级 READ COMMITTED。
以一个固定秩序(a fixed order)访问你的表和记录。这样事务将形成一个较精细的队列，而避免死锁。
为你的表添加合适的索引。那么你的查询只需要扫描较少的索引，因而设置较少的锁定。使用 EXPLAIN SELECT 来确定 MySQL 为你的查询挑选的适当的索引。
尽量少用锁定：如果可以通过一个 SELECT 在一个较老的数据快照中获得所需数据，就不要再添加子句 FOR UPDATE 或 LOCK IN SHARE MODE 。在这时使用 READ COMMITTED 隔离级是较好的主意，因为在同一个事务中的每个 consistent read 只读取它最先确定的数据快照。
如果仍然没有什么补救效果，使用表级锁定连载你的事务(serialize transactions)：LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; UNLOCK TABLES。表级锁定可以使你的事务形成精细的队列。注意 LOCK TABLES 隐含地启动一个事务，就如同命令 BEGIN，UNLOCK TABLES 如同 COMMIT 一样隐含地结束一个事务。
连载事务(serialize transactions)的另一个解决办法就是建立一个仅有一行记录的辅助"信号量(semaphore)" 表。每一个事务在访问其它表之前均更新这个记录。通过这种方式所有的事务将持续执行。注意同时 InnoDB 实时死锁检测算法也在工作着，因为这个持续锁定(serializing lock)是一个行锁定。在 MySQL 中对于表级锁定我们必须采取超时方式。


死锁检测与回滚
InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。从 4.0.5 版开始，InnoDB 将设法提取小的事务来进行回滚。一个事务的大小由它所插入(insert)、更新(update)和删除(delete)的数据行数决定。 Previous to 4.0.5, InnoDB always rolled back the transaction whose lock request was the last one to build a deadlock, that is, a cycle in the waits-for graph of transactions.

InnoDB 不能检测出由 MySQL 的 LOCK TABLES 语句引起的死锁，或其它的表类型中的锁定所引起的死锁。你不得不通过在 my.cnf 中设置 innodb_lock_wait_timeout 参数来解决这些情形。

当 InnoDB 执行一个事务完整的回滚，这个事务所有所加的锁将被释放。然而，如果只一句的 SQL 语句因结果返回错误而进行回滚的，由这条 SQL 语句所设置的锁定可能会被保持。这是因为 InnoDB r的行锁存储格式无法知道锁定是由哪个 SQL 语句所设置。