InnoDB：InnoDB 中的死锁

来自Wikioe
跳到导航 跳到搜索


关于

死锁是指由于每个事务都持有对方需要的锁而无法进行其他事务的情况。因为这两个事务都在 await 资源变得可用，所以两个都不会释放它持有的锁。

P.S. 死锁的条件：

  1. 互斥条件；
  2. 占有且等待；
  3. 不可剥夺；
  4. 循环等待；


当事务锁定多个表中的行时（通过诸如“UPDATE”或“SELECT ... FOR UPDATE”的语句），但 Sequences 相反，则会发生死锁。当此类语句锁定索引记录和间隙的范围时，由于顺序问题，每个事务都获得了一些锁而没有获得其他锁，也会发生死锁。


为了减少死锁的可能性，请使用事务而不是“LOCK TABLES”语句；保持插入或更新数据的事务足够小，以使其长时间不保持打开状态；当不同的事务更新多个表或大范围的行时，在每个事务中使用相同的操作顺序（例如“SELECT ... FOR UPDATE”）；在“SELECT ... FOR UPDATE”和“UPDATE ... WHERE”语句中使用的列上创建索引。死锁的可能性不受隔离级别的影响，因为隔离级别更改了读取操作的行为，而死锁则由于写入操作而发生。


如果启用了死锁检测（默认设置）并且确实发生了死锁，则 InnoDB 检测到该情况并回滚其中一个事务（受害方）。如果使用“innodb_deadlock_detect”配置选项禁用了死锁检测，则 InnoDB 将依靠“innodb_lock_wait_timeout”设置在发生死锁的情况下回滚事务。因此，即使您的应用程序逻辑正确，您仍必须处理必须重试事务的情况。要查看 InnoDB 用户事务中的最后一个死锁，请使用“SHOW ENGINE INNODB STATUS”命令。如果频繁的死锁凸显了事务结构或应用程序错误处理方面的问题，请在启用“innodb_print_all_deadlocks”设置的情况下运行，以将有关所有死锁的信息打印到 mysqld 错误日志中。

InnoDB 死锁示例

以下示例说明了锁定请求将导致死锁时如何发生错误。该示例涉及两个 Client 端 A 和 B。


首先，Client 端 A 创建一个包含一行的表，然后开始事务。在事务内，A 通过在共享模式下选择它来获得该行的 S 锁： 

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+

接下来，Client 端 B 开始事务并尝试从表中删除该行： 

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

删除操作需要 X 锁。无法授予该锁，因为它与 Client 端 A 持有的 S 锁不兼容，因此该请求进入针对行和 Client 端 B 块的锁请求队列中。

最后，Client 端 A 还尝试从表中删除该行： 

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

由于 Client 端 A 需要 X 锁才能删除行，因此在此处发生死锁。但是，不能授予该锁定请求，因为 Client 端 B 已经具有 X 锁定的请求，并且正在 await Client 端 A 释放其 S 锁定。由于 B 事先要求 X 锁，因此 A 持有的 S 锁也不能升级为 X 锁。结果，InnoDB 为其中一个 Client 端生成错误并释放其锁。Client 端返回此错误： 

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

届时，可以授予对另一个 Client 端的锁定请求，并从表中删除该行。

死锁检测和回滚

启用 deadlock detection（死锁检测）时(默认设置)，InnoDB 自动检测到事务死锁并回滚一个或多个事务以打破僵局。 InnoDB 尝试选择要回滚的小事务，其中事务的大小由插入，更新或删除的行数确定。

  • 如果“innodb_table_locks = 1”（默认值）和“autocommit = 0”，InnoDB 知道表锁，并且它上面的 MySQL 层知道行级锁。否则，在涉及由 MySQL “LOCK TABLES”语句设置的表锁或 InnoDB 以外的存储引擎设置的锁的情况下，InnoDB 无法检测到死锁。通过设置“innodb_lock_wait_timeout”系统变量的值来解决这些情况。【？？？】
  • 当 InnoDB 执行事务的完全回滚时，将释放该事务设置的所有锁。但是，如果由于错误而仅回滚单个 SQL 语句，则可以保留该语句设置的某些锁。发生这种情况是因为 InnoDB 以某种格式存储行锁，使得以后不知道哪个语句设置了哪个锁。
  • 如果“SELECT”在事务中调用存储的函数，并且函数内的语句失败，则该语句将回滚。此外，如果此后执行“ROLLBACK”，则整个事务都会回滚。
  • 如果 InnoDB 监视器输出的“LATEST DETECTED DEADLOCK”部分包含一条消息，指出“在锁表等待图中搜索太深或太长，我们将回滚后续事务”，这表明 await 列表上的事务数量已达到达到限制 200。
    • 超过 200 个事务的 await 列表将被视为死锁，并且尝试检查 await 列表的事务将回退。如果锁定线程必须查看 await 列表上的事务拥有的 1,000,000 个以上的锁，也可能发生相同的错误。

禁用死锁检测

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

如何最小化和处理死锁

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


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


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

  • 在任何时候，发出“SHOW ENGINE INNODB STATUS”命令来确定最新死锁的原因。这可以帮助您调整应用程序以避免死锁。
  • 如果频繁出现死锁警告引起关注，请通过启用“innodb_print_all_deadlocks”配置选项来收集更多的调试信息。有关每个死锁的信息，不仅是最新的死锁，还记录在 MySQL error log中。完成调试后，请禁用此选项。
  • 如果由于死锁而失败，请始终准备重新发出事务。死锁并不危险。请再试一次。
  • 保持事务小巧且持续时间短，以使事务不易发生冲突。
  • 进行一系列相关更改后立即提交事务，以减少冲突的发生。特别是，不要长时间未提交事务而保持交互式 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”隔离级别是件好事，因为同一事务中的每个一致性读取均从其自己的新快照读取。【！！！】
  • 如果没有其他帮助，请使用表级锁序列化事务。对事务表（例如 InnoDB 表）使用“LOCK TABLES”的正确方法是，先以“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 表级锁，必须使用超时方法来解决死锁。【？？？】
取自“http://wiki.eijux.com/index.php?title=InnoDB：InnoDB_中的死锁&oldid=6552”