记一次业务操作导致的数据库死锁

作者: admin 分类: 事务,数据库 发布时间: 2020-11-16 21:22

数据库日志:

LATEST DETECTED DEADLOCK
————————
2020-11-12 03:09:00 0x7f05ffb9c700
*** (1) TRANSACTION:
TRANSACTION 2016518362, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
6 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 12993316, OS thread handle 139664419370752, query id 36107452849 10.96.11.109 xiaodai_rw_v2 Searching rows for update
UPDATE BUS_ORDER
SET TRADE_STATUS = ‘002009007’ ,
UPDATE_TIME = NOW()
WHERE
LOAN_ORDER_NO = ‘41073732352106496000’ AND TRADE_STATUS = ‘002009006’
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1072 page no 578276 n bits 720 index IDX_TRADE_STATUS of table `ku`.`loan_order` trx id 2016518362 lock_mode X locks rec but not gap waiting
Record lock, heap no 469 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 9; hex 303032303039303036; asc 002009006;;
1: len 8; hex 80000000010b2464; asc $d;;

*** (2) TRANSACTION:
TRANSACTION 2016518361, ACTIVE 0 sec
mysql tables in use 3, locked 3
6 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 12998706, OS thread handle 139663741929216, query id 36107452848 10.96.11.109 xiaodai_rw_v2 Searching rows for update
UPDATE BUS_ORDER
SET TRADE_STATUS = ‘002009007’ ,
UPDATE_TIME = NOW()
WHERE
LOAN_ORDER_NO = ‘41077658202614988800’ AND TRADE_STATUS = ‘002009006’
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1072 page no 578276 n bits 720 index IDX_TRADE_STATUS of table `ku`.`loan_order` trx id 2016518361 lock_mode X locks rec but not gap
Record lock, heap no 469 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 9; hex 303032303039303036; asc 002009006;;
1: len 8; hex 80000000010b2464; asc $d;;

Record lock, heap no 480 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 9; hex 303032303039303036; asc 002009006;;
1: len 8; hex 80000000010b2465; asc $e;;

Record lock, heap no 520 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 9; hex 303032303039303036; asc 002009006;;
1: len 8; hex 80000000010b2468; asc $h;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1072 page no 655144 n bits 160 index PRIMARY of table `ku`.`loan_order` trx id 2016518361 lock_mode X locks rec but not gap waiting

 

说明:

LOAN_ORDER_NO:唯一索引

TRADE_STATUS :普通索引

ID:主键索引

执行计划:

 

问题分析:

在TRADE_STATUS同时出现在where条件中和要更新的字段中,实际的索引会是LOAN_ORDER_NO 和TRADE_STATUS,并且是两个分别索引查找之后将结果集交在一起,这样在锁定索引时,TRADE_STATUS会查出多条符合的行,两个SQL在分别逐一锁定的时候,顺序可能是不一致,这样就会产生死锁

 

避免方案:

使用ID做更新操作

 

 

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注