InnoDB 中的死锁
死锁
死锁是指不同事务无法继续进行的情况,因为每个事务都持有另一个事务需要的锁。因为两个事务都在等待资源变得可用,所以都不会释放它所持有的锁。
当事务锁定多个表中的多个行(通过 UPDATE 或 SELECT ... FOR UPDATE 等语句)但顺序相反时,可能会发生死锁。当此类语句锁定索引记录和间隙的范围时,也可能会发生死锁,并且每个事务由于计时问题而获取一些锁,但不获取其他锁。
为了减少死锁的可能性,建议:
-
使用事务,而不是 LOCK TABLES 语句;
-
保持插入或更新数据的事务足够小,以免它们长时间保持打开状态;
-
当不同的事务更新多个表或大范围的行时,在每个事务中使用相同的操作顺序,例如,
SELECT ... FOR UPDATE
; -
在
SELECT ... FOR UPDATE
和UPDATE ... WHERE
语句中使用的列上创建索引。
死锁的可能性不受隔离级别的影响,因为隔离级别只是改变了读操作的行为,而死锁的发生是因为写操作。
当启用死锁检测(默认)并且死锁确实发生时,InnoDB 会检测到该情况并回滚其中一个事务(受害者)。
如果使用 innodb_deadlock_detect 变量禁用死锁检测,InnoDB 将依赖 innodb_lock_wait_timeout 设置在发生死锁时回滚事务。innodb_lock_wait_timeout因此,即使您的应用程序逻辑是正确的,您仍然必须处理必须重试事务的情况。要查看用户事务中的最后一个死锁InnoDB,可以使用 SHOW ENGINE INNODB STATUS 查看。
如果频繁的死锁突出表明事务结构或应用程序错误处理存在问题,可以启用 innodb_print_all_deadlocks 将有关所有死锁的信息打印到 mysqld 的错误日志中。
死锁示例
以下示例说明了当锁定请求导致死锁时如何发生错误。该示例涉及两个客户端 A 和 B。
InnoDB 状态包含上次死锁的详细信息。对于频繁发生死锁的情况,启用全局变量innodb_print_all_deadlocks。这会将死锁信息添加到错误日志中。
客户端 A 启用 innodb_print_all_deadlocks,创建两个表“Animals”和“Birds”,并向每个表插入数据。客户端 A 开始事务,并在共享模式下选择 Animals 中的一行:
例如,假设存在两个表 Animals、Birds,:
# 打开死锁日志
mysql> SET GLOBAL innodb_print_all_deadlocks = ON;
mysql> CREATE TABLE Animals (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE = InnoDB;
mysql> CREATE TABLE Birds (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE = InnoDB;
mysql> INSERT INTO Animals (name,value) VALUES ("Aardvark",10);
mysql> INSERT INTO Birds (name,value) VALUES ("Buzzard",20);
同时存在两个客户端 A 和 B,对上述两个表进行操作:
时刻 | 会话 A | 会话 B | 结果 |
---|---|---|---|
START TRANSACTION; | |||
SELECT value FROM Animals WHERE name='Aardvark' FOR SHARE; | 返回 value = 10 的结果 | ||
START TRANSACTION; | |||
T0 | SELECT value FROM Birds WHERE name='Buzzard' FOR SHARE; | 返回 value = 20 的结果 | |
T1 | UPDATE Animals SET value=30 WHERE name='Aardvark'; | 等待锁释放 | |
T3 | UPDATE Birds SET value=40 WHERE name='Buzzard'; | 发生死锁,提示:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
T0 时刻,两个事务的锁状态:
mysql> SELECT ENGINE_TRANSACTION_ID as Trx_Id,
OBJECT_NAME as `Table`,
INDEX_NAME as `Index`,
LOCK_DATA as Data,
LOCK_MODE as Mode,
LOCK_STATUS as Status,
LOCK_TYPE as Type
FROM performance_schema.data_locks;
+-----------------+---------+---------+------------+---------------+---------+--------+
| Trx_Id | Table | Index | Data | Mode | Status | Type |
+-----------------+---------+---------+------------+---------------+---------+--------+
| 421291106147544 | Animals | NULL | NULL | IS | GRANTED | TABLE |
| 421291106147544 | Animals | PRIMARY | 'Aardvark' | S,REC_NOT_GAP | GRANTED | RECORD |
| 421291106148352 | Birds | NULL | NULL | IS | GRANTED | TABLE |
| 421291106148352 | Birds | PRIMARY | 'Buzzard' | S,REC_NOT_GAP | GRANTED | RECORD |
+-----------------+---------+---------+------------+---------------+---------+--------+
4 rows in set (0.00 sec)
T1 时刻,锁状态:
mysql> SELECT REQUESTING_ENGINE_LOCK_ID as Req_Lock_Id,
REQUESTING_ENGINE_TRANSACTION_ID as Req_Trx_Id,
BLOCKING_ENGINE_LOCK_ID as Blk_Lock_Id,
BLOCKING_ENGINE_TRANSACTION_ID as Blk_Trx_Id
FROM performance_schema.data_lock_waits;
+----------------------------------------+------------+----------------------------------------+-----------------+
| Req_Lock_Id | Req_Trx_Id | Blk_Lock_Id | Blk_Trx_Id |
+----------------------------------------+------------+----------------------------------------+-----------------+
| 139816129437696:27:4:2:139816016601240 | 43260 | 139816129436888:27:4:2:139816016594720 | 421291106147544 |
+----------------------------------------+------------+----------------------------------------+-----------------+
1 row in set (0.00 sec)
mysql> SELECT ENGINE_LOCK_ID as Lock_Id,
ENGINE_TRANSACTION_ID as Trx_id,
OBJECT_NAME as `Table`,
INDEX_NAME as `Index`,
LOCK_DATA as Data,
LOCK_MODE as Mode,
LOCK_STATUS as Status,
LOCK_TYPE as Type
FROM performance_schema.data_locks;
+----------------------------------------+-----------------+---------+---------+------------+---------------+---------+--------+
| Lock_Id | Trx_Id | Table | Index | Data | Mode | Status | Type |
+----------------------------------------+-----------------+---------+---------+------------+---------------+---------+--------+
| 139816129437696:1187:139816016603896 | 43260 | Animals | NULL | NULL | IX | GRANTED | TABLE |
| 139816129437696:1188:139816016603808 | 43260 | Birds | NULL | NULL | IS | GRANTED | TABLE |
| 139816129437696:28:4:2:139816016600896 | 43260 | Birds | PRIMARY | 'Buzzard' | S,REC_NOT_GAP | GRANTED | RECORD |
| 139816129437696:27:4:2:139816016601240 | 43260 | Animals | PRIMARY | 'Aardvark' | X,REC_NOT_GAP | WAITING | RECORD |
| 139816129436888:1187:139816016597712 | 421291106147544 | Animals | NULL | NULL | IS | GRANTED | TABLE |
| 139816129436888:27:4:2:139816016594720 | 421291106147544 | Animals | PRIMARY | 'Aardvark' | S,REC_NOT_GAP | GRANTED | RECORD |
+----------------------------------------+-----------------+---------+---------+------------+---------------+---------+--------+
6 rows in set (0.00 sec)
T3 时刻,InnoDB 会回滚触发死锁的事务,然后,会话 B 的事务就可以获取到锁,继续进行。
可以通过如下语句,查询死锁数量:
mysql> SELECT `count` FROM INFORMATION_SCHEMA.INNODB_METRICS
WHERE NAME="lock_deadlocks";
+-------+
| count |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
可以通过错误日志查看事务和锁的信息:
mysql> SELECT @@log_error;
死锁检测
默认情况下,死锁检测是启用的,InnoDB 会自动检测事务 死锁(deadlocks)并回滚一个或多个事务以打破死锁的状态。InnoDB 会尝试选择小事务进行回滚,其中,事务的大小由插入、更新或删除的行数决定。
如果 innodb_table_locks = 1(默认) 和 autocommit = 0,则 InnoDB 知道表锁,同时,MySQL Server 层知道行级锁。否则,InnoDB 无法检测涉及 MySQLd 的 LOCK TABLES 语句设置的表锁或 InnoDB 以外的存储引擎设置的锁的死锁。可以通过设置 innodb_lock_wait_timeout 系统变量的值来解决这些情况。
如果 InnoDB Monitor 输出的 LATEST DETECTED DEADLOCK 部分包含一条消息,指出 TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH,WE WILL ROLL BACK FOLLOWING TRANSACTION,这表明等待列表上的事务数量已达到 200 的限制。超过 200 个事务的等待列表将被视为死锁,并且尝试检查等待列表的事务将被回滚。如果锁定线程必须查看等待列表上的事务拥有的超过 1,000,000 个锁,也可能会发生相同的错误。
有关组织数据库操作以避免死锁的技术,请参阅第 15.7.5 节“InnoDB 中的死锁”。
禁用死锁检测
在高并发系统上,当大量线程等待同一锁时,死锁检测可能会导致速度减慢。有时,禁用死锁检测并在发生死锁时依靠 innodb_lock_wait_timeout 设置进行事务回滚可能会更有效。可以使用 innodb_deadlock_detect 变量禁用死锁检测。
如何最小化和处理死锁
死锁是事务数据库中的一个典型问题,但它们并不危险,除非它们太频繁以至于您根本无法运行某些事务。通常,您必须编写应用程序,以便它们始终准备好在事务因死锁而回滚时重新发出事务。
InnoDB 使用自动行级锁定。即使事务只插入或删除一行,也可能会出现死锁。那是因为这些操作并不是真正的“原子”;它们自动在插入或删除的行的(可能是多个)索引记录上设置锁。
您可以使用以下技术应对死锁并减少发生死锁的可能性:
-
通过执行 SHOW ENGINE INNODB STATUS,来确定最近一次死锁的原因。这可以帮助我们调整应用程序以避免死锁。
-
如果频繁的死锁警告引起关注,请通过启用该变量来收集更广泛的调试信息 innodb_print_all_deadlocks 。有关每个死锁的信息(而不仅仅是最新的死锁)都记录在 MySQL 错误日志中。完成调试后禁用此选项。
-
如果事务因死锁而失败,请始终做好重新执行事务的准备。
死锁并不危险。再试一次。
-
保持交易规模小且持续时间短,以使其不易发生冲突。
-
在进行一组相关更改后立即提交事务,以使其不易发生冲突。特别是,不要让交互式 mysql会话长时间打开且未提交事务。
-
如果您使用锁定读取(SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE),尝试使用较低的隔离级别,例如 READ COMMITTED。
-
当修改事务中的多个表或同一表中的不同行集时,每次都以一致的顺序执行这些操作。然后事务形成明确定义的队列并且不会死锁。
例如,将数据库操作组织到应用程序内的函数中,或调用存储例程,而不是在不同位置编写多个类似的 INSERT、UPDATE 和 DELETE 语句序列。
-
将精心选择的索引添加到表中,以便您的查询扫描更少的索引记录并设置更少的锁。
通过 EXPLAIN SELECT 确定 MySQL 服务器认为哪些索引最适合您的查询。
-
少用锁定。如果您可以允许 SELECT 从旧快照返回数据,请不要向其中添加 FOR UPDATE 或 FOR SHARE 子句。在这里使用 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表级锁,必须使用超时方法来解决死锁。
事务调度
InnoDB 使用竞争感知事务调度 (CATS) 算法来确定等待锁的事务的优先级。当多个事务正在等待同一对象上的锁时,CATS 算法会确定哪个事务首先接收到锁。
CATS 算法通过分配调度权重来确定等待事务的优先级,调度权重是根据事务阻塞的事务数量计算的。例如,如果两个事务正在等待同一对象上的锁,则阻塞最多事务的事务将被分配更大的调度权重。如果权重相等,则优先考虑等待时间最长的事务。