MySQL死锁-线上问题排查案例
业务处理流程示意图
死锁日志
2024-10-16 13:51:36 139636894508800*** (1) TRANSACTION:TRANSACTION 4718724388, ACTIVE 0 sec insertingmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2MySQL thread id 82934132, OS thread handle 139633304270592, query id 35509253618 10.100.32.248 dscloud_agent updateINSERT INTO order_detail_model ( order_no,commodity_code,commodity_name,commodity_count,total_actual_amount,bill_price,goods_type,is_joint,
is_original,is_gift,status ) VALUES ( 1812845401680613892,'199965','双黄连口服液_林宝_10ml*12支',1,15.80,15.8000,1,0,
1,0,0 )
*** (1) HOLDS THE LOCK(S):RECORD LOCKS space id 26174 page no 590 n bits 824 index idx_order_no of table `dscloud`.`order_detail_model` trx id 4718724388 lock_mode X locks gap before recRecord lock, heap no 377 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 992885f6a1323d07; asc ( 2= ;; 1: len 8; hex 800000000003f03f; asc ?;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 26174 page no 590 n bits 824 index idx_order_no of table `dscloud`.`order_detail_model` trx id 4718724388 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 377 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 992885f6a1323d07; asc ( 2= ;; 1: len 8; hex 800000000003f03f; asc ?;;
*** (2) TRANSACTION:TRANSACTION 4718724391, ACTIVE 0 sec insertingmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2MySQL thread id 82934170, OS thread handle 139634086201088, query id 35509253624 10.100.39.10 dscloud_agent updateINSERT INTO order_detail_model ( order_no,commodity_code,commodity_name,commodity_count,total_actual_amount,bill_price,goods_type,is_joint,
is_original,is_gift,status ) VALUES ( 1812845401445735431,'169913','清热解毒口服液_伊龍_10ml*10支',1,9.50,9.5000,1,0,
1,0,0 )
*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 26174 page no 590 n bits 824 index idx_order_no of table `dscloud`.`order_detail_model` trx id 4718724391 lock_mode X locks gap before recRecord lock, heap no 377 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 992885f6a1323d07; asc ( 2= ;; 1: len 8; hex 800000000003f03f; asc ?;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 26174 page no 590 n bits 824 index idx_order_no of table `dscloud`.`order_detail_model` trx id 4718724391 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 377 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 992885f6a1323d07; asc ( 2= ;; 1: len 8; hex 800000000003f03f; asc ?;;
*** WE ROLL BACK TRANSACTION (2)
排查原因
这个死锁问题涉及两个事务 (1) 和 (2),它们都在尝试向同一个表 order_detail_model
插入数据,且都在等待锁。
死锁原因分析:
-
事务 (1) 和 (2) 都在同一索引
idx_order_no
上持有了 “gap” 锁,也即“间隙锁”(Gap Lock)。间隙锁通常用于阻止其他事务在索引的某个范围内插入新记录,以维持一致性。 -
事务 (1):
- 持有
idx_order_no
的 gap 锁,锁住了索引前的间隙。 - 但是它仍在等待该索引的一个位置上插入意图锁。
- 持有
-
事务 (2):
- 同样持有
idx_order_no
的 gap 锁,锁住了索引前的间隙。 - 但也在等待该索引上的插入意图锁。
- 同样持有
-
死锁形成:
- 事务 (1) 和 (2) 都已经持有了 gap 锁,并且都在等待对方释放锁以完成插入操作。然而,由于双方都需要对方的锁,而各自持有锁,形成了死锁。
解决方案建议:
-
减少并发插入的冲突:可以考虑通过优化业务逻辑,减少并发事务同时对同一索引位置插入的情况。
-
锁定策略调整:考虑调整表的索引策略,或者在可能的情况下,使用更低隔离级别(如
READ COMMITTED
)以减少 gap 锁的使用。 -
批量插入:如果可能,将多条插入操作批量执行,减少每次单条插入操作时加锁的次数。
-
间隙锁的避免:如果应用场景允许,减少唯一性约束,避免间隙锁的发生。
这样可以帮助缓解类似的死锁问题。如果问题频繁出现,建议增加监控和分析锁的使用情况。
解决思路
批量插入本身不会直接减少 gap 锁,但可以间接减少出现死锁的概率。以下是详细的分析:
Gap 锁的作用
Gap 锁通常在 REPEATABLE READ
隔离级别下,用于防止幻读(Phantom Reads),即防止其他事务在当前事务扫描的索引范围内插入新记录。尤其是在具有唯一性约束(如唯一索引)的情况下,插入操作会触发 gap 锁,来确保当前索引范围内的插入安全。
为什么批量插入可能减少死锁:
-
减少事务次数:批量插入将多次插入合并为一次事务,从而减少了每次插入时加锁和释放锁的频率。这降低了事务之间相互等待锁的机会,从而间接减少死锁的发生。
-
减少并发锁冲突:单次批量插入会一次性获取所需的锁,减少了多个事务同时请求锁的竞争,从而减少了并发场景下发生 gap 锁冲突的可能性。
-
锁持有时间减少:批量插入操作可以让事务更快地完成,从而缩短锁的持有时间,降低其他事务等待锁的时间。
注意事项:
-
批量插入并不能完全避免 gap 锁。如果多个批量插入操作同时执行,且插入的数据范围有重叠,仍然会发生 gap 锁。
-
表的设计和索引结构也对 gap 锁有影响。如果索引结构不合理,即使是批量插入,仍然可能遇到 gap 锁。
总结
批量插入可以通过减少事务频率和锁的持有时间,降低死锁的发生率,但它并不会直接减少 gap 锁的使用。如果 gap 锁是死锁的主要原因,可以考虑其他措施,如调整表结构、索引设计,或选择不同的事务隔离级别(如 READ COMMITTED
,该级别下不会产生 gap 锁)。
最终执行动作
- 修改为update方式
- 缩小事务范围
- 对于某些场景可以不操作数据库
基于Starlight构建 | 构建日期: 2025.04.02