作者 王文安,腾讯CSIG数据库专项的数据库工程师,主要负责腾讯云数据库 MySQL 的相关的工作,热爱技术,欢迎留言进行交流。
Part1 背景
锁作为 MySQL 知识体系的主要部分之一,是每个 DBA 都需要学习和掌握的知识。锁保证了数据库在并发的场景下数据的一致性,同时锁冲突也是影响数据库性能的因素之一。而锁冲突中,有一类很经典的场景经常会拿出来讨论:死锁。最近刚好也遇到了一个典型的死锁案例,本文会基于这个案例,做一次详细的分析与拆解。
Part2 问题
由于innodb engine status会记录最近一次死锁的细节信息,因此案例现场的信息是可以完整拿到的。用户针对这个死锁的问题,提出了疑问:数据更新的并不是同一行,使用的也是不同的索引,为什么会发生死锁?(以下细节信息均已脱敏)
死锁的两个语句如下:
UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354)
UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354)
精简之后的 MySQL 死锁信息如下:
=====================================
2020-10-26 12:14:30 7fd2642f5700 INNODB MONITOR OUTPUT
=====================================
...省略...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-10-26 12:12:03 7fd2846ed700
*** (1) TRANSACTION:
TRANSACTION 1795660514, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 21829887, OS thread handle 0x7fd28d14a700, query id 178279444 172.21.0.15 username updating
UPDATE tbl_deadlock SET col1= 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660514 lock_mode X waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 00721f45; asc r E;;
*** (2) TRANSACTION:
TRANSACTION 1795660513, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
20 lock struct(s), heap size 2936, 40 row lock(s)
MySQL thread id 21905203, OS thread handle 0x7fd2846ed700, query id 178279443 172.21.0.15 username updating
UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 00721f45; asc r E;;
Record lock, heap no 430 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 00721fe3; asc r ;;
Record lock, heap no 431 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 0072218f; asc r! ;;
...省略很多 Record lock...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 344554 n bits 120 index `PRIMARY` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 44; compact format; info bits 0
0: len 4; hex 00722663; asc r&c;;
...省略无关的两行...
3: len 4; hex 005f5434; asc _T4;;
4: len 4; hex 00012272; asc "r;;
...省略很多行...
*** WE ROLL BACK TRANSACTION (1)
...省略...
Part3 原因分析
首先简单了解一下死锁的几个要素:
- 互斥条件:一个资源每次只能被一个进程占用。 MySQL 的锁机制天然具备这个条件。
- 请求与保持条件:资源请求被阻塞时,已持有的资源不会被释放。 MySQL 不触发死锁回滚,且未进入 lockwait_timeout 的时候,具备这个条件。
- 不剥夺条件:已获得的资源,在末使用完之前,不能强行剥夺。 MySQL 的锁机制天然具备这个条件。
- 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系,通常会表现为有向环。
由于 MySQL 的锁机制的原因,只需要判断出两个 SQL 语句的锁存在循环等待,那么死锁的条件就会成立了。
接下来对 MySQL 记录的死锁信息进行详细的分析,首先观察死锁的事务详情这一部分信息:
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)。
......
20 lock struct(s), heap size 2936, 40 row lock(s)
可以很明显可以发现,这两个语句涉及到的数据行还是比较多的,用户的疑问:数据更新的并不是同一行,其实是个误解。那么理论上,“循环等待:互相持有对方需要的锁”,这种典型的死锁场景是可能会存在的。
接下来,重点放在更细节的信息上:
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660514 lock_mode X waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 00721f45; asc r E;;
......
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 344554 n bits 120 index `PRIMARY` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 44; compact format; info bits 0
0: len 4; hex 00722663; asc r&c;;
...省略无关的两行...
3: len 4; hex 005f5434; asc _T4;;
4: len 4; hex 00012272; asc "r;;
...省略很多行...
用户提出的疑问:使用的也是不同的索引,为什么会发送死锁?实际上二级索引上的记录锁,最终也会加到主键上。
这个很好理解,如果二级索引上,通过搜索商品表的商品名称索引(二级索引)搜索“iphone12”,并给这一行数据加上了锁,锁住了“iphone12”这个商品的详情数据行,如果别的事务可以通过搜索主键来修改这一行数据,明显是不行的。
因此本案例中,虽然死锁信息中记录的索引名称不一样,但是锁争用的条件是成立的,即:trx1 通过二级索引向主键上执行了加锁操作,而 trx2 在其他的二级索引上拿到了锁,但是主键锁拿不到,因此进入了等待状态。所以只需要定位到具体锁的数据,找到循环等待的逻辑关系,就可以完成整个案例分析了。
参考上文引用的信息,具体发生死锁的行的信息都记录在类似0: len 4; hex 00722663; asc r&c;;的信息中。
trx1 记录的锁等待信息是二级索引 id2,因为 id2 是一个单行索引,因此只会有 0 和 1 两行信息,0 代表的就是具体的行 id2,1 即为主键。通过 16 进制转换工具,转成 10 进制,可以发现对应的数据如下:
pk = 7479109 and id2 = 74354
那么再看看 trx2 记录的信息,锁等待方面,记录的信息是主键,所以这个地方会有完整的表数据,过滤掉无效的数据之后,留下了三行:0 为主键,3 为 id1,4 为 id2。转换进制之后,对应的数据如下:
pk = 7480931 and id1 = 6247476 and id2 = 74354
可以看到,trx2 等待的锁,id1 和 id2 刚好满足 trx1 的查询条件。而 trx2 持有的锁信息中,第一个刚好就是 trx1 等待的:
那么关于这个死锁案例的具体场景,就可以用下有向环的图例进行说明:
至此为止,这个死锁的案例分析就完成了,从最初的死锁成立条件分析,到解读具体的锁内容,最终完成了死锁的有向环图例。
实际上,自己观察一下这个死锁的有向环图例,会发现这两个语句用到了两个单列索引,那么进一步思考的话,如果这两个列建成了联合索引,这个死锁的案例是不是就可能不会发生了?
Part4 总结
对于死锁的问题,只需要根据四个条件,一步一步过滤与分析,通过解读死锁现场的详细内容,就可以准确的还原整个死锁的发生原因以及涉及到的数据行。当然,在实际的业务环境中,可能还会有更复杂和隐蔽的死锁案例,但是不论多么隐蔽和复杂,死锁分析的思路和步骤都是相似的。
本文由博客一文多发平台 OpenWrite 发布!