MySQL 中如果发生死锁应该如何解决？

Sherwin.Wei Lv7
  2025-02-26 22:23:13 2025-02-26 22:23:13 Created   2025-02-26 22:23:13 2025-02-26 22:23:13 Updated      

MySQL 中如果发生死锁应该如何解决？

回答重点

自动检测与回滚：

  • MySQL 自带死锁检测机制（innodb_deadlock_detect），当检测到死锁时，数据库会自动回滚其中一个事务，以解除死锁。通常会回滚事务中持有最少资源的那个。
  • 也有锁等待超时的参数（innodb_lock_wait_timeout），当获取锁的等待时间超过阈值时，就释放锁进行回滚。

手动 kill 发生死锁的语句：

  • 可以通过命令，手动快速地找出被阻塞的事务及其线程 ID，然后手动 kill 它，及时释放资源。

扩展知识

死锁日志

如果线上遇到死锁的情况，可以通过 SHOW ENGINE INNODB STATUS 来获取死锁的日志信息，从而定位到死锁发生的原因。

我从网上找了一个死锁日志，供大家参考学习，有个大致印象即可：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)

简单分析一下，上半部分在解释事务 1 即 (1) TRANSACTION 的情况，它执行delete from test where a = 2 在申请索引 a 的 X 锁（index a of table … lock_mode X waiting）

下半部分在解释事务 2 即 (2) TRANSACTION，它持有索引 a 的 X 锁，又因为 insert into test (id,a) values (10,2) 这个插入语句在申请 S 锁（表中的 a 建立了唯一索引，因此需要 S 锁来进行重复 key 判断）。

所以事务 1 排着队等事务 2 释放 X 锁，而事务 2 又需要 S 锁，申请 S 锁的时候发现等锁的队伍前还有个 X 锁，这样一来就形成了相互依赖。

通过日志分析我们就能知晓死锁的原因，然后通过一定的修改来避免死锁的产生或降低死锁的概率。

常见避免死锁或降低死锁的手段

1）避免大事务。大事务占据锁的时间长，将大事务拆分成多个小事务快速释放锁，可降低死锁产生的概率和避免冲突。

2）调整申请锁的顺序。在更新数据的时候要保证获得足够的锁，举个例子：先获取影响范围大的锁，比如说修改操作，先将排他锁获取到，再获取共享锁。或固定顺序访问数据，这样也能避免死锁的情况。

3）更改数据库隔离级别。可重复读比读已提交多了间隙锁和临键锁，利用读已提交替换之可降低死锁的情况。

4）合理建立索引，减少加锁范围。如果命中索引，则会锁对应的行，不然就是全表行都加锁，这样冲突大，死锁的概率就高了。

5）开启死锁检测，适当调整锁等待时长。

手动 kill 语句步骤

1）查找当前的事务和锁信息：

使用以下命令查看当前正在执行的事务和相关的锁信息：

1
SHOW ENGINE INNODB STATUS;

这个命令会输出 InnoDB 的状态，包括死锁信息和当前的活动事务。你可以在输出中找到被阻塞的事务及其线程 ID。

除此之外，也可以使用 INFORMATION_SCHEMA 中的 INNODB_LOCKS 和 INNODB_LOCK_WAITS 表，查看当前锁和锁等待情况，得到事务ID。

1
2
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

再通过 INFORMATION_SCHEMA 的 innodb_trx 可以找到事务ID和线程 ID 的对应关系。

2）手动终止该事务：

一旦确定了需要 KILL 的线程 ID，使用以下命令终止该事务：

1
KILL <thread_id>;

将 <thread_id> 替换为你找到的实际线程 ID。

INNODB_LOCKS 、 INNODB_LOCK_WAITS 和 INNODB_TRX 示例

INNODB_LOCKS 查询当前锁的信息

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

示例结果

LOCK_ID LOCK_MODE LOCK_TYPE LOCK_TABLE LOCK_INDEX LOCK_OWNER LOCK_DATA
12345678:1 X RECORD my_database/my_table PRIMARY 123456 1
12345678:2 S RECORD my_database/my_table PRIMARY 123457 2

字段解释：

  • LOCK_ID: 唯一标识每个锁。
  • LOCK_MODE: 锁的类型（X：排它锁，S：共享锁）。
  • LOCK_TYPE: 锁的作用对象类型（如 RECORD 表示行锁）。
  • LOCK_TABLE: 被锁定的表。
  • LOCK_INDEX: 相关索引。
  • LOCK_OWNER: 拥有该锁的事务 ID。
  • LOCK_DATA: 被锁定的数据行的主键值。

INNODB_LOCK_WAITS 查询锁等待情况

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

示例结果

REQUESTING_TRX_ID HOLDING_TRX_ID LOCK_ID LOCK_TYPE
123456 123457 12345678:1 RECORD
123458 123459 12345678:2 RECORD

字段解释

  • REQUESTING_TRX_ID: 请求锁的事务 ID。
  • HOLDING_TRX_ID: 当前持有锁的事务 ID。
  • LOCK_ID: 与 INNODB_LOCKS 表中的锁相对应。
  • LOCK_TYPE: 请求的锁类型。

INNODB_TRX 查询线程 ID 示例

1
2
3
SELECT trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_id 
FROM INFORMATION_SCHEMA.INNODB_TRX 
WHERE trx_id = '123456';

示例结果

trx_state trx_started trx_mysql_thread_id trx_query trx_id
LOCK_WAIT 2024-09-25 10:00:00 1234 SELECT * FROM my_table WHERE id = 1 123456

字段解释：

  • trx_state: 事务的当前状态。在此示例中，LOCK_WAIT 表示该事务正在等待锁。
  • trx_started: 事务开始的时间。在此示例中，事务于 2024-09-25 10:00:00 开始。
  • trx_mysql_thread_id: 与该事务关联的 MySQL 线程 ID，可以用来查找该事务的更多信息。
  • trx_query: 当前事务正在执行的查询。在此示例中，它正在执行一个简单的 SELECT 查询。
  • trx_id: 事务的唯一标识符。在此示例中，事务 ID 为 123456。
Comments
On this page
MySQL 中如果发生死锁应该如何解决？
  1. MySQL 中如果发生死锁应该如何解决？
    1. 回答重点
    2. 扩展知识
      1. 死锁日志
      2. 常见避免死锁或降低死锁的手段
      3. 手动 kill 语句步骤
      4. INNODB_LOCKS 、 INNODB_LOCK_WAITS 和 INNODB_TRX 示例