记一次生产死锁问题排查

某日,运维反馈数据库日志显示最近有死锁发生
** (1) TRANSACTION:
TRANSACTION 182471376, ACTIVE 0.007 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
LOCK BLOCKING MySQL thread id: 8445282 block 8445336
MySQL thread id 8445336, OS thread handle 0x2babb4440700, query id 252220878 172.31.0.168 reward_prod Searching rows for update
update ———— set ———— where ———— *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 834 page no 467 n bits 552 index ———— of table ————.———— trx id 182471376 lock_mode X locks rec but not gap
waitingRecord lock, heap no 177 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 18; hex 313230313032313936343130313032343638; asc 120102196410102468;;
1: len 8; hex 8000000000023ee7; asc > ;;

*** (2) TRANSACTION:
TRANSACTION 182471377, ACTIVE 0.005 sec fetching rows
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 8445282, OS thread handle 0x2babb4481700, query id 252220880 172.31.0.157 reward_prod Searching rows for update
update ———— set ———— where ———— *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 834 page no 467 n bits 552 index IDX_IDNO of table ————d.———— trx id 182471377 lock_mode X locks rec but not gap
Record lock, heap no 177 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 18; hex 313230313032313936343130313032343638; asc 120102196410102468;;
1: len 8; hex 8000000000023ee7; asc > ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 834 page no 467 n bits 552 index ———— of table ————.———— trx id 182471377 lock_mode X locks rec but not gap
waitingRecord lock, heap no 176 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 18; hex 313230313032313936343130313032343638; asc 120102196410102468;;
1: len 8; hex 8000000000023ee6; asc > ;;

*** WE ROLL BACK TRANSACTION (2)
经排查,系多个服务同时更新了同一条数据导致死锁。
最终对更新操作添加了分布式锁。

发表评论

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