作者：李锡超
一个爱笑的江苏苏宁银行 数据库工程师，次要负责数据库日常运维、自动化建设、DMP平台运维。善于MySQL、Python、Oracle，喜好骑行、钻研技术。
本文起源：原创投稿
*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
一、前言
死锁，作为数据库一个常见的并发问题。此类问题：
1.触发起因往往与利用的逻辑相干，参加的事务可能是两个、三个、甚至更多；
2.因为不同数据库的锁实现机制简直齐全不同、实现逻辑简单，还存在多种锁类型；
3.数据库产生死锁后，会立刻终止局部事务，预先无奈看到死锁前的期待状态。
即，死锁问题具备业务关联、机制简单、类型多样等特点，导致当数据库产生死锁问题时，不是那么容易剖析。
基于解决死锁问题存在的难点，本文以MySQL数据库一则并发Insert导致的死锁为例，从发现问题、重现问题、根因剖析、解决问题4个步骤，冀望能提供一套对于死锁的迷信无效计划，供读者敌人参考。
二、问题景象
某零碎在进行上线前压测时，发现利用日志存在如下日志提醒触发死锁问题：
Deadlock found when trying to get lock; try restarting transaction好在压测时，就发现了问题，防止上线后影响生产。
随后，执行 show engine innodb status，有如下内容（脱敏后）：
------------------------LATEST DETECTED DEADLOCK------------------------2023-03-24 19:07:50 140736694093568*** (1) TRANSACTION:TRANSACTION 56118, ACTIVE 6 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1192, 1 row lock(s), undo log entries 1MySQL thread id 9, OS thread handle 140736685700864, query id 57 localhost root updateinsert into dl_tab(id,name) values(30,10)*** (1) HOLDS THE LOCK(S):RECORD LOCKS space id 11 page no 5 n bits 72 index ua of table `testdb`.`dl_tab` trx id 56118 lock mode S waitingRecord lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; # 十进制: 10 1: len 4; hex 8000001a; asc ;; # 十进制: 26*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 11 page no 5 n bits 72 index ua of table `testdb`.`dl_tab` trx id 56118 lock mode S waitingRecord lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; # 十进制: 10 1: len 4; hex 8000001a; asc ;; # 十进制: 26*** (2) TRANSACTION:TRANSACTION 56113, ACTIVE 12 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1192, 2 row lock(s), undo log entries 2MySQL thread id 8, OS thread handle 140736952903424, query id 58 localhost root updateinsert into dl_tab(id,name) values(40,8)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 11 page no 5 n bits 72 index ua of table `testdb`.`dl_tab` trx id 56113 lock_mode X locks rec but not gapRecord lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; # 十进制: 10 1: len 4; hex 8000001a; asc ;; # 十进制: 26*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 11 page no 5 n bits 72 index ua of table `testdb`.`dl_tab` trx id 56113 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; # 十进制: 10 1: len 4; hex 8000001a; asc ;; # 十进制: 26*** WE ROLL BACK TRANSACTION (1)------------1、死锁信息梳理
依据以上信息，发现是 dl_tab 执行insert操作导致死锁。初步梳理如下。
版本: 8.0.27
隔离级别: Read-Commited
表构造:
*************************** 1. row *************************** Table: dl_tabCreate Table: CREATE TABLE `dl_tab` ( `id` int NOT NULL AUTO_INCREMENT, `name` int NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `ua` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci留神，以上innodb status 输入，不同的数据库版本会有差别。次要有:
A.在MySQL 8.0.18及之后，输入后果包含两个事务各自持有的锁、期待的锁，对剖析死锁问题很有帮忙。在 8.0.18之前，只包含事务1期待的锁，事务2持有的锁、期待的锁，而不包含事务1持有的锁信息；
B.以上示例还包含具体的索引记录值（如{10,26}：第一字段为索引记录的值，第二个字段为对应的主键记录）。如果没有索引记录值，可能只有heap no，该编号作为外部实现锁机制十分要害，但无奈和具体的索引记录对应起来。此外，找了其它几个MySQL版本发现原生版本≥5.7.21 、≥8.0有这个性能，Percona mysql 5.7.21 竟然没有这个性能；
C.事务T1 期待的锁从输入后果看到的是 LOCK S, 但其实获取的锁是 lock s next key lock, 这点从前面的源码剖析后果中会进一步阐明。
innodb status 日志梳理：
从以上innodb status输入。能够看到死锁产生在惟一索引 ua 上。这确实也是在RC隔离级别配置下，比拟常见的死锁场景。进一步梳理死锁过程:
A.首先事务T1获取到了ua中记录10的 lock x,rec not not gap 锁
B.事务T2尝试获取ua中记录10的lock s, next key lock，因为T1持有了记录的独占锁，因而被T1梗塞
C.事务T1尝试获取ua中记录10的lock x, gap before rec,insert intention，但被梗塞
2、提出问题
除了以上景象外，无奈从输入后果失去更多的信息，比方：
Q1: T1为什么会持有 ua中记录10 的锁？
Q2: T1既然持有了锁，为什么又会期待锁？
Q3: T2持有和期待雷同的锁，到底是持有还是在期待？
Q4: 死锁到底是如何产生的？
为此，与研发同学沟通，理解死锁产生的业务场景，并对问题进行了再次复现。
三、重现问题
研发同学发现是在某业务的定时工作进行并发解决时触发，并很快在对应的开发环境复现了问题。问题复现后，同样在利用日志和innodb status输入看到对应日志，确认是同一问题。
倡议读者敌人思考1分钟，如何进一步剖析
1、尝试解决
本着解决问题优先的准则，在惟一索引ua并发时产生的，那是否能够将惟一索引改为一般索引？如果不能够，是否能够升高并发（或者间接改为单并发）？不过很快研发同学就进行了确认，uname的惟一索引是外围框架依赖的，改不了！该性能的实时性要求很高，上线后业务量比拟大，不能升高或调小并发。既然无奈防止，那只能进一步剖析死锁产生的起因，并据此确认解决方案！
研发在复现问题后，除了能看到利用日志和innodb status输入，还是没有更多的信息。此外，研发是参考测试环境，造了一批数据，而后进行模仿复现的。即: 尽管能复现这个死锁，但无法回答最后提出的问题（Q1,Q2,Q3,Q4）。
2、跟踪死锁产生过程
随后找到Percona提供一篇文章（链接: How to deal with MySQL deadlocks），大抵对死锁的问题的剖析提供了思路:以利用日志和innodb status提供的数据为根底，联合events_statements_history、binlog(最好是statement格局)、 慢查问日志（slow log）、个别日志（general log）进行剖析。
依据文章利用已有的性能(events_statements_history/slow log/general log)，去找到数据库连贯运行过那些SQL语句。随后，总结了如下脚本：
-- 将events_statements_history 中的启动工夫转换为规范工夫create database IF NOT EXISTS perf_db;use perf_db;DELIMITER //create function f_convert_timer_to_utc(pi_timer bigint) returns timestamp(6)DETERMINISTICbegin declare value_utc_time timestamp(6); select FROM_UNIXTIME( (unix_timestamp(sysdate()) - variable_value) + pi_timer/1000000000000 ) from performance_schema.global_status where variable_name = 'Uptime' into value_utc_time; return value_utc_time;end;//DELIMITER ;--innodb status 输入中，死锁信息中MySQL thread id，理论示意是PROCESSLIST ID。执行语句找到thread_id 与PROCESSLIST_ID的对应关系select PROCESSLIST_ID,THREAD_ID,PROCESSLIST_INFO from performance_schema.threads where PROCESSLIST_ID in (8,10);-- 找到上一步找到的线程ID找到运行过的SQL语句select THREAD_ID, perf_db.f_convert_timer_to_utc(TIMER_START) run_start_time, perf_db.f_convert_timer_to_utc(TIMER_END) run_end_time, TIMER_WAIT/1000000000000 wait_time_s, 'False' is_current, CURRENT_SCHEMA, SQL_TEXT from performance_schema.events_statements_history where thread_id=51union select THREAD_ID, perf_db.f_convert_timer_to_utc(TIMER_START) run_start_time, perf_db.f_convert_timer_to_utc(TIMER_END) run_end_time, TIMER_WAIT/1000000000000 wait_time_s, 'True' is_current, CURRENT_SCHEMA, SQL_TEXT from performance_schema.events_statements_current where thread_id=51 and (THREAD_ID,EVENT_ID,END_EVENT_ID) not in (select THREAD_ID,EVENT_ID,END_EVENT_ID from performance_schema.events_statements_history )order by run_start_time;注：以上脚本中红色文字，须要依据理论状况替换。
整顿脚本后，研发同学再次尝试进行了复现死锁。查问失去如下后果：
select PROCESSLIST_ID,THREAD_ID,PROCESSLIST_INFO from performance_schema.threads where PROCESSLIST_ID in (8,10);+----------------+-----------+------------------+| PROCESSLIST_ID | THREAD_ID | PROCESSLIST_INFO |+----------------+-----------+------------------+| 8 | 49 | NULL | | 10 | 51 | NULL | +----------------+-----------+------------------+thread_id=49的sql运行状况：| 49 | 2023-03-25 02:15:59.173352 | 2023-03-25 02:15:59.173612 | 0.0003 | False | testdb | begin || 49 | 2023-03-25 02:16:08.349311 | 2023-03-25 02:16:08.350678 | 0.0014 | False | testdb | insert into dl_tab(id,name) values(26,10) || 49 | 2023-03-25 02:16:26.824176 | 2023-03-25 02:16:26.826121 | 0.0019 | False | testdb | insert into dl_tab(id,name) values(40,8) |+-----------+----------------------------+----------------------------+-------------+------------+----------------+-------------------------------------------+thread_id=51 的sql运行状况：| 51 | 2023-03-25 02:15:58.040749 | 2023-03-25 02:15:58.041057 | 0.0003 | False | testdb | begin || 51 | 2023-03-25 02:16:17.408110 | 2023-03-25 02:16:26.828374 | 9.4203 | False | testdb | insert into dl_tab(id,name) values(30,10) |梳理后果如下：
根据上述梳理后果，通过人工形式在开发环境执行上述SQL语句，再次发生死锁，且innodb status的死锁信息与测试环境基本相同。
至此，答复了最开始提出的问题Q1：
Q1: T1为什么会持有 ua中记录10 的锁？
答：因为该事务后面执行了语句如下语句，所以持有了记录(26,10)的锁：insert into dl_tab(id,name) values(26,10);
3、对于跟踪死锁额定的思考
从这个死锁的产生过程，刚好是innodb status死锁信息输入后果中的两个会话导致了死锁。但参加死锁的可能波及3个、4个或者更多的事务，因而还有如下几个额定的问题：
Q5: 如果是3个或更多的事务参加死锁，如何跟踪？
Q6: 执行的SQL语句应该是导致死锁最间接的起因，其本质锁的是记录、锁类型及梗塞关系，如何查看？
Q7: 死锁产生后，因为MySQL死锁检测机制会主动发现死锁，并会筛选事务进行回退。事务被回退了之后，就毁坏了死锁的第一现场。除了innodb status提供的最近一次死锁信息外（特地是8.0.18之前不包含事务1持有的锁信息），再无其它可用的剖析数据。
综合以上3个问题，总结了如下补充计划，以采集相干的性能数据：
- 针对Q7：在测试环境长期敞开死锁检测，而后再次复现：
innodb_deadlock_detect = offinnodb_lock_wait_timeout = 10innodb_rollback_on_timeout = on- 针对Q5,Q6：联合MySQL已有的实时锁与锁期待性能数据，总结了如下脚本：
-- 创立工作目录cd <path-to-dir>mkdir deadlock_datacd deadlock_data-- 创立死锁数据保留表mysql -uroot -S /tmp/mysql.sockcreate database IF NOT EXISTS perf_db;use perf_dbCREATE TABLE `tab_deadlock_info` ( `id` int primary key auto_incrment, `curr_dt` datetime(6) NOT NULL, `thread_id` bigint unsigned DEFAULT NULL, `conn_id` bigint unsigned DEFAULT NULL, `trx_id` bigint unsigned DEFAULT NULL, `object_name` varchar(64) DEFAULT NULL, `INDEX_NAME` varchar(64) DEFAULT NULL, `lock_type` varchar(32) NOT NULL, `lock_mode` varchar(32) NOT NULL, `lock_status` varchar(32) NOT NULL, `LOCK_DATA` varchar(8192) CHARACTER SET utf8mb4 DEFAULT NULL, `blk_trx_id` bigint unsigned DEFAULT NULL, `blk_thd_id` bigint unsigned DEFAULT NULL, index idx_curr_dt(curr_dt)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 查看以后存在的锁及锁梗塞信息-- data_locks/data_lock_waits 自MySQL 8.0.1提供，之前版本查问information_schema.innodb_locks/ information_schema.innodb_lock_waits获取相似信息vi save_lock_data.sqlinsert into tab_deadlock_info(curr_dt,thread_id,conn_id,trx_id,object_name,index_name, lock_type,lock_mode,lock_status,lock_data,blk_trx_id,blk_thd_id)select NOW(6) curr_dt,a.thread_id,b.PROCESSLIST_ID conn_id, ENGINE_TRANSACTION_ID trx_id, object_name, INDEX_NAME,lock_type,lock_mode,lock_status,LOCK_DATA, c.BLOCKING_ENGINE_TRANSACTION_ID blk_trx_id, c.BLOCKING_THREAD_ID blk_thd_idfrom performance_schema.data_locks a left join performance_schema.threads b on a.thread_id=b.thread_idleft join performance_schema.data_lock_waits c on a.ENGINE_TRANSACTION_ID=c.REQUESTING_ENGINE_TRANSACTION_ID and a.thread_id=c.REQUESTING_THREAD_IDwhere a.thread_id=b.thread_id order by thread_id,trx_id;-- 查问脚本mysql -uroot -S /tmp/mysql.sock perf_db -e "source save_lock_data.sql"-- 定时查问脚本vi run_save_lock.shwhile truedosleep 0.1 # 指定查问间隔时间，结合实际需要调整mysql -uroot -S /tmp/mysql.sock perf_db -e "source save_lock_data.sql" 2>>run_save_lock.errdone-- 执行查问sh run_save_lock.sh阐明：
A.配置上述innodb_deadlock_detect参数敞开死锁检测，innodb status将不在会持续输入最初LATEST DETECTED DEADLOCK信息；
B.利用日志看到的告警为锁超时告警：Lock wait timeout exceeded; try restarting transaction。能够据此找到锁超时产生工夫。
再次复现后应用应用tab_deadlock_info查问锁数据如下：
同时，应用步骤2)查问的语句信息如下：
综合上述查问后果，梳理失去如下后果：
再次梳理出了死锁的产生过程。
四、根因剖析
通过上述过程，能够看到死锁产生的过程，获取的锁及其属性信息。但要剖析出为什么会产生死锁，还须要联合MySQL的锁实现机制。因为以上死锁场景，波及惟一索引的插入实现逻辑，将联合源码进行解读。
1、单列惟一索引插入逻辑
下图中：
蓝色线示意T1第一次插入执行的逻辑；
紫色线示意T2第一次插入执行的逻辑；
彩色线示意T1第二次插入执行的逻辑；
与惟一索引插入记录相干锁操作，应用了红色短箭头标记；
竖线与红色短尖头穿插示意执行了函数，否则示意未执行。
2、最终死锁过程
以工夫维度，联合以上的mysql加锁逻辑进行剖析：
A. T1、T2开启了一个事务，随后T1执行了插入(26,10)的insert语句
B. T2执行了插入(30,10)的insert语句。进行唯一性抵触查看，尝试获取LOCK_S | LOCK_ORDINARY[line 15]。那为什么看到时LOCK S，是因为LOCK_ORDINARY对应的数字示意为0，任何与之进行“与”运算都等于自身，所以看不出来：
C. 而后T2所在的连贯会将T1中的隐式锁转换为显示锁[line 17]，此时T1将获取LOCK_REC | LOCK_X | LOCK_REC_NOT_GAP，即看到的Lock X, Rec_not_gap。因为是T2所在的线程为T1创立的锁，因而该锁对应的thread_id为T2的线程ID，但trx_id为T1的事务ID
D. 但因为 T1的LOCK X|LOCK_REC_NOT_GAP与T2的LOCK S| LOCK_ORDINARY不兼容[line 23]，因而T2被梗塞
E. 当T2被梗塞时，外部函数add_to_waitq 在解决时，同样会记录创立一个锁，并设置属性 LOCK_S | LOCK_ORDINARY | LOCK_WAIT | LOCK_REC，以批示锁期待[line 24]。随后T2返回下层函数，以期待锁资源[line 38]
F. 随后，T1又执行了(40,8)的insert语句。因为其插入的惟一索引值是8（留神不是10），因而不存在主键抵触，间接执行乐观插入操作[line 43]。
G. 执行乐观插入时，须要查看其它事务是否梗塞insert操作。其外围是获取待插入记录的下一个值[line 46-47]（这里刚好是10），并获取该记录上的所有锁，与须要增加的锁（LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION）判断是否存在抵触[line 51]。
H. 第E步T2持有了记录10的LOCK_S | LOCK_ORDINARY | LOCK_WAIT | LOC
K_REC 锁与T1的LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION不兼容，因而T1被T2梗塞[line 51]
I. 死锁造成。
如需理解更多实现细节，大家能够联合源码进一步确认。
至此，答复了最开始提出的问题Q2,Q3,Q4：
Q2: T1既然持有了锁，为什么又会期待锁？
答：持有锁应该没有疑难，在剖析相似问题留神隐式锁转换为显示锁的机制(lock_rec_convert_impl_to_expl)。期待锁次要因为T2被梗塞后，会创立锁（LOCK_S | LOCK_ORDINARY | LOCK_WAIT | LOCK_REC）。而后T1在执行乐观插入时，须要遍历记录上存在的所有>锁进行锁抵触判断，因为锁模式不兼容，因而被梗塞
Q3: T2持有和期待雷同的锁，到底是持有还是在期待？
答：尽管从innodb status看到T2持有和期待都是lock s, next key lock。但实际上期待lock s, next key lock；因为锁抵触，退出期待队列时会持有锁LOCK_S | LOCK_ORDINARY | LOCK_WAIT | LOCK_REC
Q4: 死锁到底是如何产生的？
答：见以上剖析死锁过程剖析
五、解决问题
综合以上死锁产生的产生过程和起因，总结起因如下：
起因1：次要研发适度依赖惟一索引，插入的数据不合乎惟一索引要求，须要进行唯一性抵触查看。
起因2：批量插入的数据不是有序的。两种状况同时存在，导致死锁产生。
起因2在并发场景下，管制起来较为简单。起因1该场景为并发批量插入逻辑，能够在执行插入时，防止插入反复的uname。随后，研发同学进行逻辑优化后，问题不再产生。
对于死锁问题，倡议联合业务状况尽量抉择Read Committed隔离级别，适当的缩小Unique索引。如的确产生死锁，读者敌人能够参考本次故障案例，正当利用性能数据来跟踪死锁问题，联合源码或者已有的案例剖析死锁根本原因和解决方案。
以上信息仅左右交换，作者程度无限，如有不足之处，欢送在评论区交换。