记录一次现网MySQL死锁问题排查和优化

原创已于 2025-12-23 16:56:23 修改 · 750 阅读

11 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#MySQL #死锁 #DeadLock

于 2025-12-17 11:07:57 首次发布

1、问题背景

双十一搞活动，企微群出现告警。

2、突现告警

3、查看应用日志

没有图片，忘了截图了，现在日志已经清空了，不过不重要，大概就是一个DeadLock的堆栈信息

4、查看数据库锁日志

查询mysql日志命令：

show engine innodb status

万幸，日志还能查到，日志大概内容：

=====================================
2025-11-07 11:18:36 140364350953216 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 17 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 21775837 srv_active, 0 srv_shutdown, 234487 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 116464029
OS WAIT ARRAY INFO: signal count 124496867
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-11-07 11:04:51 140363383674624
*** (1) TRANSACTION:
TRANSACTION 4655205627, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 98213395, OS thread handle 140364343031552, query id 28073836980 172.16.3.216 xxxx update
INSERT INTO xxxx( xxxx )  VALUES  ( 1986630903639687169, 'E690', 1929803586759090177, 1971490634585956387, 1, 3, 2, 0.3903, 0.3, 0.00, '2025-11-06', 'OF', '2025-11-07 11:04:51.902', '2025-11-07 11:04:51.902' ),( 1986630903341891585, 'E690', 1929803565829513218, 1971459225951498245, 6, 1, 2, -0.2582, -0.2582, -10.00, '2025-11-06', 'ALL', '2025-11-07 11:04:51.832', '2025-11-07 11:04:51.832' )

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 15025 page no 883 n bits 560 index uk_date_store_target of table `xxxx`.`xxxxx` trx id 4655205627 lock mode S waiting
Record lock, heap no 489 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 3; hex 8fd366; asc   f;;
 1: len 4; hex 45363930; asc E690;;
 2: len 8; hex 1ac80a233b0ce001; asc    #;   ;;
 3: len 8; hex 1b91ee47ff01b001; asc    G    ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15025 page no 883 n bits 560 index uk_date_store_target of table `xxxx`.`xxxx` trx id 4655205627 lock mode S waiting
Record lock, heap no 489 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 3; hex 8fd366; asc   f;;
 1: len 4; hex 45363930; asc E690;;
 2: len 8; hex 1ac80a233b0ce001; asc    #;   ;;
 3: len 8; hex 1b91ee47ff01b001; asc    G    ;;


*** (2) TRANSACTION:
TRANSACTION 4655205626, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 98205049, OS thread handle 140364356245248, query id 28073836979 172.16.3.60 xxxx update
INSERT INTO xxxx  ( xxxx )  VALUES  ( 1986630903635488769, 'E690', 1929803586759090177, 1971490634585956387, 1, 3, 2, 0.3903, 0.3, 0.00, '2025-11-06', 'OF', '2025-11-07 11:04:51.902', '2025-11-07 11:04:51.902' ),( 1986630903350276097, 'E690', 1929803565829513218, 1971459225951498245, 6, 1, 2, -0.2582, -0.2582, -10.00, '2025-11-06', 'ALL', '2025-11-07 11:04:51.833', '2025-11-07 11:04:51.833' )

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15025 page no 883 n bits 560 index uk_date_store_target of table `xxxx`.`xxxxxx` trx id 4655205626 lock_mode X locks rec but not gap
Record lock, heap no 489 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 3; hex 8fd366; asc   f;;
 1: len 4; hex 45363930; asc E690;;
 2: len 8; hex 1ac80a233b0ce001; asc    #;   ;;
 3: len 8; hex 1b91ee47ff01b001; asc    G    ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15025 page no 883 n bits 560 index uk_date_store_target of table `xxxx`.`xxxx` trx id 4655205626 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 489 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 3; hex 8fd366; asc   f;;
 1: len 4; hex 45363930; asc E690;;
 2: len 8; hex 1ac80a233b0ce001; asc    #;   ;;
 3: len 8; hex 1b91ee47ff01b001; asc    G    ;;

*** WE ROLL BACK TRANSACTION (1)

5、分析锁日志

5.1 死锁发生时间

时间：2025-11-07 11:04:51
线程 ID：
Transaction 1: 98213395
Transaction 2: 98205049

5.2 涉及表和索引

表名：xxxx.xxxxxx
索引名：uk_date_store_target

5.3 事务详情

Transaction (1)：插入操作

INSERT INTO xxxxx  
(xxxxxx) 
VALUES  
(1986630903639687169, 'E690', 1929803586759090177, 1971490634585956387, 1, 3, 2, 0.3903, 0.3, 0.00, '2025-11-06', 'OF', '2025-11-07 11:04:51.902', '2025-11-07 11:04:51.902'),
(1986630903341891585, 'E690', 1929803565829513218, 1971459225951498245, 6, 1, 2, -0.2582, -0.2582, -10.00, '2025-11-06', 'ALL', '2025-11-07 11:04:51.832', '2025-11-07 11:04:51.832')

锁等待情况：
当前持有共享锁（S）正在等待同一记录上的共享锁。
请求获取记录锁的位置为 page no 883 上的 heap no 489。

Transaction (2)：插入操作

INSERT INTO xxxx  
(xxxxxx) 
VALUES  
(1986630903635488769, 'E690', 1929803586759090177, 1971490634585956387, 1, 3, 2, 0.3903, 0.3, 0.00, '2025-11-06', 'OF', '2025-11-07 11:04:51.902', '2025-11-07 11:04:51.902'),
(1986630903350276097, 'E690', 1929803565829513218, 1971459225951498245, 6, 1, 2, -0.2582, -0.2582, -10.00, '2025-11-06', 'ALL', '2025-11-07 11:04:51.833', '2025-11-07 11:04:51.833')

锁等待情况：
已经持有一个排他锁（X），并试图插入意向锁等待。
请求获取记录锁位置同样是 page no 883 上的 heap no 489。

6、deadlock原因分析

两个事务都尝试向相同的数据页(page no 883)中的同一条记录(heap no 489)上加锁，并且它们的操作涉及的是相同的唯一约束索引 uk_date_store_target 的键值组合。这导致了典型的并发冲突：
Transaction (1) 尝试获得一个共享锁(S)，但由于另一个事务已经拥有排他锁(X)，因此被阻塞。
同时 Transaction (2) 要求插入意向锁以完成其插入动作，但因为前者已请求共享锁而尚未释放，所以它也被挂起。
最终 InnoDB 引擎检测到了循环依赖关系并回滚了其中一个事务(Transaction 1)来解决死锁问题。

7、死锁流程

7.1. 索引冲突

两个事务都操作了同一个表
都使用了相同的唯一索引
插入的数据在索引上有重叠，指向相同的记录位置(heap no 489)

7.2 具体死锁条件

Transaction 1:
持有: 共享锁(S lock) - 等待状态
等待: 对同一记录的共享锁
Transaction 2:
持有: 排他锁(X lock)
等待: 插入意向锁(insert intention lock)

7.3 死锁根本原因

这是典型的插入意向锁死锁场景：
两个事务试图插入具有相同索引键值的记录
MySQL为了保证唯一性约束，在插入前需要检查是否存在重复记录
这种检查需要获取共享锁，而实际插入需要排他锁
当多个事务同时操作相同索引键时，就可能发生循环等待

mermaid:

sequenceDiagram
    participant T1 as Transaction 1
    participant T2 as Transaction 2
    participant R as Record(Heap 489)
    
    T1->>R: 请求共享锁(S lock)
    R-->>T1: 发现已有排他锁，等待
    
    T2->>R: 持有排他锁(X lock)
    T2->>R: 尝试插入意向锁(insert intention)
    R-->>T2: 发现T1在等待S锁，插入被阻塞
    
    Note over T1,T2: 形成循环等待，死锁产生

7.4 事务1为什么持有S锁了还需要申请S锁

7.4.1 锁状态

事务1实际锁状态
事务1 (ID: 4655205627) 并没有持有S锁
从死锁日志信息看，事务1在两条记录上都处于等待状态 (waiting)
RECORD LOCKS space id 15025 page no 883 n bits 560 index uk_date_store_target ... lock mode S waiting

锁等待情况
等待获取S锁：事务1尝试在记录上获取S锁但被阻塞
未成功持有：死锁信息显示的是事务1正在等待的状态，而不是已持有的锁

死锁形成原因
事务2 先持有了该记录的 X锁 (lock_mode X locks rec but not gap)
事务1 尝试获取 S锁时被事务2的X锁阻塞
同时事务2又等待获取插入意向锁，形成了循环等待