最近在看沈剑老师的mysql相关公众号专栏，看到一个死锁的案例，自己复现了下，然后又学习了一些其他的资料，把死锁发生的流程以及回滚的流程，都研究了一下，写篇文章整理一下。其实之前研究过死锁的排查，但当时有点死记硬背锁的感觉，没有总结出什么可以拿起来就用的工具。mysql8.0版本新增了data_locks元数据表，可以得知事务的加锁情况，个人感觉就是学习mysql锁的福音，文章主要就是依赖这个data_locks来分析死锁的。
老规矩，看一下目录
文章目录
1、构建死锁
1.1、环境信息
版本：mysql8.0.36
隔离级别：可重复读
1.2、新建测试表
CREATE TABLE t
(
id int NOT NULL
PRIMARY KEY,
name varchar(32) NULL,
age int NULL
);
INSERT INTO t (id, name, age) VALUES (1, 'aaa', 1);
INSERT INTO t (id, name, age) VALUES (3, 'bbb', 2);
INSERT INTO t (id, name, age) VALUES (10, 'ccc', 3);
1.3、构建两个事务
如下图有事务A和事务B两个事务，在T1和T2时刻，各自开启事务并且各自执行了一条delete语句
1.4、查看事务持有的锁
事务A和事务B执行delete后，都持有哪些锁呢？mysql8新增了data_locks，可以非常直观的查看事务持有锁的情况。
执行以下sql语句，从data_locks中获取加锁信息
select
THREAD_ID,index_name, LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from
performance_schema.data_locks;
输出
1.4.1、data_locks表字段含义
1)、thread_id
加锁的线程id。
一共两个线程，133和134，分别对应两个事务
2)、index_name
锁加在了哪个索引上。
可以看到有两个PRIMARY，代表是主键索引，也就是我们的id字段。还有两个索引列没有数据，因为这两个索引列对应的锁是表锁
3)、lock_type
锁类型。
在InnoDB中，我们最常接触到的两个锁类型，一个是表锁，一个是行锁
4)、lock_mode
索引模式。
表锁，有多种表现形式。像本例中的IX，代表意向写锁，因为我们执行了一个DELETE语句，这是一个写操作，在执行前，先要加一把意向锁。
行锁也有多种表现形式.像本例中的"X , GAP"。代表这是一把GAP锁，并且也是一把写锁
为什么要加一把IX锁和一把GAP锁呢，如果不加会有什么问题？
这个问题，我们在附录章节的4.1讨论
5)、lock_status
持有锁的状态。
GRANTED：已获取到锁
WAITING：等待锁
6)、lock_data
锁住的数据。
本例中，事务A和事务B在T1、T2时刻分别删除了id=6和id=7的数据，这两条数据在我们构建的表t中都是不存在的，所以事务A和事务B只锁住了一个gap，lock_data中只展示了10，紧挨着10的左边的数据是3，所以锁住的gap是(3,10)
data_locks表的字段不止这些，还有其他的，感兴趣，可以到官网看一下
1.5、构建死锁
下面，我们在T3和T4时刻再分别执行两条insert语句，来构建一个死锁出来
先执行T3时刻的insert语句，执行完后，会发生事务等待的情况，再看一下事务持有锁的情况
可以看到线程id为134的线程多了一条数据，持有锁的状态为：WAITING，这意思是在等待获取这把锁。这条WAITING的记录是134的这个线程执行的，所以134这个线程代表事务A。锁的类型是行锁，锁类型是"X , GAP , INSERT_INTENTION"。
这说明事务A在T3执行的这条insert语句，要获取两把锁，一把是间隙锁，另外一把是插入意向锁。
我们继续执行T4时刻对应的sql语句，此时便会发生死锁。
死锁报错如下：
发生死锁后，InnoDB会根据事务的大小来选择一个事务进行回滚。
那InnoDB选择哪个事务进行回滚呢?这个问题，我们在下面的附录4.2讨论。
2、死锁排查
2.1、查看最近一次的死锁日志
死锁已经出现了，如何排查死锁出现的原因呢？这里我们可以执行一下：show engine innodb status这条命令便会得到最近的一次死锁日志。
顺便提一下：如果我们想看到所有发生过的死锁信息，可以开启如下参数
innodb_print_all_deadlocks = ON
此时mysql会将所有的死锁信息都记录在mysql的error.log中，等我们排查完问题，再将这个配置关闭即可。
我们继续看show engine innodb status输出内容中的死锁日志部分
这个命令会返回很多信息，我们找里面的"LATEST DETECTED DEADLOCK"信息，这就是死锁发生时的日志。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-11-17 03:28:46 276914706176
*** (1) TRANSACTION:
TRANSACTION 3453, ACTIVE 56 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 68, OS thread handle 280346871552, query id 5798 172.17.0.1 root update
//事务发生时，执行的sql语句
INSERT INTO t (`id`,`name`,`age`) VALUES (5,'ddd',15)
*** (1) HOLDS THE LOCK(S):
//持有一把gap锁.该锁作用在user_skill库的t表的主键索引上
RECORD LOCKS space id 26 page no 4 n bits 72 index PRIMARY of table `user_skill`.`t` trx id 3453 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
//8000000a如何转换成十进制？
//如果是unsigned类型,需要先把8000000a这个16进制数转换成二进制,如果转换结果是负数,比如本例的8000000a,此时需要先将首位的1变0,然后就可以得到正确的结果了
//如果是unsigned类型,或者转换结果是正数,就不需要做1变0的操作了,16进制转10进制,直接就可以得到结果
0: len 4; hex 8000000a; asc ;;//持有的锁，锁住的记录对应的主键id,这个值是10
1: len 6; hex 000000000d78; asc x;;//最近一次修改该记录的事务ID
2: len 7; hex 81000000c7012a; asc *;;//undo的记录
3: len 3; hex 636363; asc ccc;;//持有的锁，锁住的记录对应的name字段值,
4: len 4; hex 80000003; asc ;;//持有的锁,锁住的记录对应的age字段值
*** (1) WAITING FOR THIS LOCK TO BE GRANTED://正在等待该锁释放
RECORD LOCKS space id 26 page no 4 n bits 72 index PRIMARY of table `user_skill`.`t` trx id 3453 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000000d78; asc x;;
2: len 7; hex 81000000c7012a; asc *;;
3: len 3; hex 636363; asc ccc;;
4: len 4; hex 80000003; asc ;;
*** (2) TRANSACTION:
TRANSACTION 3454, ACTIVE 52 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 67, OS thread handle 280075265792, query id 5820 172.17.0.1 root update
INSERT INTO t (`id`,`name`,`age`) VALUES (8,'eee',25)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 4 n bits 72 index PRIMARY of table `user_skill`.`t` trx id 3454 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000000d78; asc x;;
2: len 7; hex 81000000c7012a; asc *;;
3: len 3; hex 636363; asc ccc;;
4: len 4; hex 80000003; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 4 n bits 72 index PRIMARY of table `user_skill`.`t` trx id 3454 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000000d78; asc x;;
2: len 7; hex 81000000c7012a; asc *;;
3: len 3; hex 636363; asc ccc;;
4: len 4; hex 80000003; asc ;;
*** WE ROLL BACK TRANSACTION (2)//回滚了事务2
2.2、根据死锁日志倒推事务加锁顺序
我们整理一下上面的死锁日志。
1)、事务A执行delete语句，持有了一把gap锁，锁住了(3,10)的间隙
2)、事务B执行delete语句，也持有了一把gap锁，也锁住了(3,10)的间隙
3)、事务A接着执行INSERT INTO t (id,name,age) VALUES (5,‘ddd’,15);的语句，想获取一把插入意向锁，但因为事务B此时已经获取了(3,10)的间隙锁，这是一把X锁，所以事务A的insert语句发生了等待，两把X锁是互斥的
4)、事务B接着执行INSERT INTO t (id,name,age) VALUES (8,‘eee’,25);的语句，也想获取一把插入意向锁，但因为事务A此时也已经获取了(3,10)的间隙锁，此时就形成了死锁，两个事务互相等待各自的锁
我画了一张图，描述一下这个死锁形成的过程
3、如何避免死锁
mysql官网上有一篇博客，就是讲这块的，mysql官方博客
我把其中的措施总结一下
1)、按mysql官网的说法，死锁并不可怕，我们只要确保死锁不要频繁发生并且不要影响到正常业务即可，所以业务中要做好重试事务的逻辑。这一条其实并不算是避免死锁，更像是如何进行补偿
2)、如果我们要使用SELECT … FOR UPDATE 或者SELECT … FOR SHARE，此时可以考虑将mysql的隔离级别降到读已提交。在这个隔离级别下是没有间隙锁的，出现死锁的可能会降低
3)、以一致的操作顺序来加锁，比如：业务逻辑包括insert、delete、update三个操作，那避免死锁的一条措施是：每次都按照一个固定的顺序执行这三个语句，不要改变操作顺序
4)、建立合适的索引，降低扫描行的数量
5)、尽量不要执行大事务，降低加锁的时间
6)、使用lock tables来锁定整张表，执行事务，避免死锁，如下：
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
4、附录
4.1、删除时为什么要加一把IX锁，以及一把gap锁？加这两把锁要解决什么问题呢？
1)、为什么要加意向写锁?
意向写锁是一把表锁，加这把锁主要是为了防止lock tables这一类的表锁。如果执行删除操作时，不加意向写锁，会有什么问题呢？
试想：有一个事务A正在执行删除操作，来了一个事务B执行了一个lock tables语句，lock tables的意思是锁住整张表，不让表中的数据再发生变化，但事务A是先执行的，在删除表中的数据，事务B执行了锁表语句后，事务A还能继续删除数据，这违背了表锁的定义
2)、为什么要加gap锁?
是为了防止出现幻读。如果不加这把gap锁，事务A刚执行了delete语句，把id = 5的数据删除了，影响行数为1行。但是事务B又新增了一条id = 5的数据，那事务A再次执行delete，就会发现又影响到了一行数据，这就出现了幻读
4.2、发生死锁时，要回滚哪个事务?
mysql选择要回滚的事务时，内部有一个权重的计算，权重值低的事务会被回滚。
权重的计算公式，源码中是这样写的
权重值 = sql影响行数 + 事务持有锁的数量。
下面我们就来计算一下，事务A和事务B，影响的行数和持有锁的数量
影响行数，是一样的，都是2个
持有锁的数量上，因为语句一样，所以发生死锁时，持有的锁也是一样的。
所以事务A和事务B最后计算出来的权重也是一样的，那这个时候回滚谁呢?
回滚后来的那个，也就是事务B
5、参考资料
1)、<架构师之路>公众号
2)、<mysql中的锁>，丁奇的mysql单元课
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
