记录一次生产死锁

本文主要是介绍记录一次生产死锁，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

杂谈

入职新公司三个月了，这三个月可以说充实到爆，每天忙到忘记吃饭，忙到忘记回复女朋友消息，忙到忘了自己做了啥，还有啥没做。来到电商公司，确实是感受到了业务的复杂，写代码的谨慎，很多想法也可以大胆的去实现，也学到了很多性能优化的思路。虽然不比较累，但总算是不再平庸！年轻人嘛！熬过来就是成长！
前段时间我负责的物流服务在生产出现MySQL死锁了,第一次在生产遇到这种情况，所以特意花了时间去研究了一下，发现其实还蛮有趣的！

死锁分析

一开始看到这个是懵逼的，事务1没持有锁，只有事务2持有锁，哪里的死锁？

这纯属是瞎扯蛋嘛！不甘心的我决定打开晦涩难懂的死锁日志来看看

死锁日志

LATEST DETECTED DEADLOCK

2022-07-14 16:00:14 0x7f17884d4700
*** (1) TRANSACTION:
TRANSACTION 48437745192, 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 831755398, OS thread handle 139737578731264, query id 6431017352 172.19.222.57 order_logistics update
INSERT INTO SQL语句 ON DUPLICATE KEY UP
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14652 page no 17063 n bits 280 index uk_order_item_logistics of table pro_order_logistics.pro_order_item_logistics_2206 trx id 48437745192 lock_mode X waiting
Record lock, heap no 207 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 27; hex 323230363133313635373639363032353336333333333834343537; asc 220613165769602536333384457;;
1: len 8; hex 80000000010b1220; asc ;;
2: len 14; hex 3735383937373039313032303334; asc 75897709102034;;
3: len 8; hex 8000000000227312; asc "s ;;

*** (2) TRANSACTION:
TRANSACTION 48437745191, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 6
MySQL thread id 831762106, OS thread handle 139739047741184, query id 6431017350 172.19.109.179 order_logistics update
INSERT INTO SQL语句 ON DUPLICATE KEY U
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 14652 page no 17063 n bits 280 index uk_order_item_logistics of table pro_order_logistics.pro_order_item_logistics_2206 trx id 48437745191 lock_mode X locks rec but not gap
Record lock, heap no 207 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 27; hex 323230363133313635373639363032353336333333333834343537; asc 220613165769602536333384457;;
1: len 8; hex 80000000010b1220; asc ;;
2: len 14; hex 3735383937373039313032303334; asc 75897709102034;;
3: len 8; hex 8000000000227312; asc "s ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14652 page no 17063 n bits 280 index uk_order_item_logistics of table pro_order_logistics.pro_order_item_logistics_2206 trx id 48437745191 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 207 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 27; hex 323230363133313635373639363032353336333333333834343537; asc 220613165769602536333384457;;
1: len 8; hex 80000000010b1220; asc ;;
2: len 14; hex 3735383937373039313032303334; asc 75897709102034;;
3: len 8; hex 8000000000227312; asc "s ;;

*** WE ROLL BACK TRANSACTION (1)

经过我这查那查之后，终于把日志翻译成可以看懂的

LATEST DETECTED DEADLOCK

2022-07-14 16:00:14 0x7f17884d4700
*** (1) TRANSACTION:
TRANSACTION 48437745192, 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 831755398, OS thread handle 139737578731264, query id 6431017352 172.19.222.57 order_logistics update
INSERT INTO SQL语句 ON DUPLICATE KEY UP

事务1：涉及的表有1个，有2个锁结构 加锁的索引 uk_order_item_logistics(order_no, item_id, logistics_no)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14652 page no 17063 n bits 280 index uk_order_item_logistics of table pro_order_logistics.pro_order_item_logistics_2206 trx id 48437745192 lock_mode X waiting
Record lock, heap no 207 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 27; hex 323230363133313635373639363032353336333333333834343537; asc 220613165769602536333384457;;
1: len 8; hex 80000000010b1220; asc ;;
2: len 14; hex 3735383937373039313032303334; asc 75897709102034;;
3: len 8; hex 8000000000227312; asc "s ;;

事务1处于等待状态，锁等待：写锁等待Next-Key锁（Gap+Record锁）

*** (2) TRANSACTION:
TRANSACTION 48437745191, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 6
MySQL thread id 831762106, OS thread handle 139739047741184, query id 6431017350 172.19.109.179 order_logistics update
INSERT INTO SQL语句 ON DUPLICATE KEY U
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 14652 page no 17063 n bits 280 index uk_order_item_logistics of table pro_order_logistics.pro_order_item_logistics_2206 trx id 48437745191 lock_mode X locks rec but not gap
Record lock, heap no 207 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 27; hex 323230363133313635373639363032353336333333333834343537; asc 220613165769602536333384457;;
1: len 8; hex 80000000010b1220; asc ;;
2: len 14; hex 3735383937373039313032303334; asc 75897709102034;;
3: len 8; hex 8000000000227312; asc "s ;;

持有锁：写锁持有不含gap锁的Record锁

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14652 page no 17063 n bits 280 index uk_order_item_logistics of table pro_order_logistics.pro_order_item_logistics_2206 trx id 48437745191 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 207 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 27; hex 323230363133313635373639363032353336333333333834343537; asc 220613165769602536333384457;;
1: len 8; hex 80000000010b1220; asc ;;
2: len 14; hex 3735383937373039313032303334; asc 75897709102034;;
3: len 8; hex 8000000000227312; asc "s ;;

*** WE ROLL BACK TRANSACTION (1)

插入意向锁在等待写锁释放gap锁，只有写锁持有Next-Key锁时才可以进行写操作

分析：

KEY U =====根据唯一索引，存在则更新，不存在则新增

了解锁：

Gap锁（间隙锁） 作用在索引之间的间隙

Record锁：在索引上加锁，行记录的锁

Next-Key锁：本质上就是Gap锁和Record锁的结合，锁住索引外还要锁住索引间的间隙 即Next-Key = Gap + Record;

MySQL有两种常规锁模式

LOCK_S（读锁，共享锁）

LOCK_X（写锁，排它锁）

读加共享锁，写加排它锁

锁的属性

LOCK_REC_NOT_GAP ==========（锁索引）

LOCK_GAP =================（锁索引之间的间隙）

LOCK_ORDINARY =============（同时锁索引+索引间间隙，即Next Key锁）

LOCK_INSERT_INTENTION ========（插入意向锁，其实是特殊的GAP锁）

锁组合：

lock -> type_mode 可以是Lock_X 或者Lock_S

locks gap before rec 表示为gap锁：lock->type_mode & LOCK_GAP

locks rec but not gap 表示为记录锁，非gap锁：lock->type_mode & LOCK_REC_NOT_GAP

insert intention 表示为插入意向锁：lock->type_mode & LOCK_INSERT_INTENTION

waiting 表示锁等待：lock->type_mode & LOCK_WAIT

前置知识：

MySQL在RR隔离级别的等级下，为了防止幻读的产生，在写操作时，不仅要加行锁（record锁）还要加间隙锁（gaps锁），也就是所谓的Next-Key锁

解析死锁逻辑

事务1：持有：无（这里说无，不太标准！）
事务1：等待：next-key锁 对应日志中lock_mode X waiting Record lock

事务2：持有：不完整的next-key锁,只有锁索引的record,还差gap锁，对应日志中 lock_mode X locks rec but not gap Record lock
事务2：等待：要执行insertOrupdate存在插入意向锁,所以事务2的插入意向锁需要等待排他锁释放gap锁，对应日志中 lock_mode X locks gap before rec insert intention waiting Record lock

同时事务2无gap锁，那么只能等事务1的释放gap,而事务1还在等待事务2的行锁(包含gap+record)，因此成环

避免死锁

1.减少insertOrUpdate的使用，改为insert和update两个不同的事务
2.修改数据库隔离级别为RC，但需要自己解决幻读的问题
3.乐观锁

这篇关于记录一次生产死锁的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！