这个不可思议的死锁你会解吗?
共 5095字,需浏览 11分钟
·
2021-04-30 22:52
| 作者 王起帆,腾讯CSIG数据库产品中心后台开发工程师,目前主要参与DBbrain开发工作,热爱技术,欢迎留言进行交流。
DELETE FROM dept_manager WHERE num = 0;
DELETE FROM dept_manager WHERE dept_no = 'd001';
一、死锁模拟
死锁模拟
首先介绍下表结构,这个表除了主键索引 PRIMARY,还有一个唯一索引 num 和一个非唯一索引 dept_no ,建表语句如下:
CREATE TABLE `dept_manager` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`num` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`),
unique index(`num`),
KEY `dept_no` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
然后再准备下数据:
INSERT INTO `dept_manager` VALUES (1001,'d001',0,'1991-10-01'),
(1002,'d005',1,'9999-01-01'),
(1005,'d002',3,'1989-12-17'),
(1007,'d002',4,'9999-01-01'),
(1008,'d004',7,'1988-09-09'),
(1009,'d004',8,'1992-08-02'),
(1010,'d005',9,'1996-08-30');
使用执行两个sql很难,使用 mysqlslap 来高并发碰碰运气:
"begin;DELETE FROM dept_manager WHERE num = 0; rollback;" --number-of-queries=100000 -uroot -p123456 & mysqlslap --create-schema dldb -q
"begin;DELETE FROM dept_manager WHERE dept_no = 'd001'; rollback;" --number-of-queries=100000 -uroot -p123456 & mysqlslap --create-schema dldb -q
这两个事物非常都是删除一行相同的数据 (1001,'d001',0,'1991-10-01')只不过一个根据索引 num ,一个根据索引 dept_no 。
二、原因分析
1. 数据是怎么找到的?
要说清楚死锁产生原因,就要先理清楚这条SQL是怎么执行的,会在那些地方加锁。在此之前先说说数据库是怎么找到我们要删除的这行数据的。下面两幅图展示根据年龄为30来查记录的示意图。首先根据 name 为 seven, 在 name 这个辅助索引查找,但是只能拿到主键的 id。随后再根据主键id 去主键查找,这个过程称为回表。访问数据是要通过索引的,而且数据就在主键索引上面,所以加锁就是加在索引上面的。
2. Delete 是怎么执行的
Delete 删除数据其实并不是把数据删除了,只是把数据标记一下,表示这里可以复用的,如果下次这里有数据要插入就可以直接复用原来空间里。所以Delete 和 Update 操作比较类似。Delete 和 Update 是根据条件找到第一条数据,进行修改,然后找到第二条数据,以此类推直到再也查不到符合条件的数据。
3. 加锁分析
我们以 DELETE FROM dept_manager WHERE num = 0; 为例,只有一个条件 num = 0, 因该是根据 num = 0 在 num 索引中找到对应的主键id, 随后根据主键 id,找到对应记录,标记成可复用状态。除了删除数据行记录,对应的索引也需要维护下,其他索引对应位置也需要标记成删除状态。这个表中主键索引 PRIMARY,唯一索引 num,非唯一索引 dept_no 的对应位置都会加上锁。同理第二个SQL语句执行时候,加锁位置也是一样的。(可重复度隔离级别上,非唯一索引还要加上间隙锁)。
既然加锁上一样的,那应该是在不同索引加锁顺序是不一样的。推测下对于 WHERE num = 0 应该先在 num 上加锁,随后在主键加锁,最后在 dept_no上,num ->PRIMARY-> dept_no。WHERE dept_no = 'd001';加锁顺序应该是dept_no -> PRIMARY -> num。尽管这条SQL数据很简单,但是由于数据中索引比较多,加锁顺序也不一样,导致了死锁。
三、场景验证
可以用 show engine innodb status ,来查看最近一次死锁日志。事物1等待索引dept上的锁 0: len 4; hex 64303031; asc d001;; 这里“64303031” 16进制转为字符为“d001” 与 WHERE dept_no = 'd001' 相对应。事物2持有这个锁的,事物1持有的锁没有显示,应该是主键上的锁,这是符合预期的。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-04-27 16:41:19 0x70000a6b1000
*** (1) TRANSACTION:
TRANSACTION 1681994, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 30, OS thread handle 123145456488448, query id 343687 localhost 127.0.0.1 root updating
DELETE FROM dept_manager WHERE num = 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #请求 dept_no上锁
RECORD LOCKS space id 367 page no 5 n bits 80 index dept_no of table `employees`.`dept_manager` trx id 1681994 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 64303031; asc d001;;
1: len 4; hex 800003e9; asc ;;
*** (2) TRANSACTION:
TRANSACTION 1681554, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 106, OS thread handle 123145477099520, query id 341105 localhost 127.0.0.1 root updating
DELETE FROM dept_manager WHERE dept_no = 'd001'
*** (2) HOLDS THE LOCK(S): # 持有 dept_no 上锁
RECORD LOCKS space id 367 page no 5 n bits 80 index dept_no of table `employees`.`dept_manager` trx id 1681554 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 64303031; asc d001;;
1: len 4; hex 800003e9; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: # 请求主键
RECORD LOCKS space id 367 page no 3 n bits 80 index PRIMARY of table `employees`.`dept_manager` trx id 1681554 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 800003e9; asc ;;
1: len 6; hex 00000019aa4a; asc J;;
2: len 7; hex 2c000001b80ede; asc , ;;
3: len 4; hex 64303031; asc d001;;
4: len 4; hex 80000000; asc ;;
5: len 3; hex 8f8f41; asc A;;
*** WE ROLL BACK TRANSACTION (2)
四、总结
本文介绍的样例中,尽管SQL语句很简单,但由于表中有多个索引,对索引的访问顺序不同,造成死锁风险。为了避免数据库中发生死锁,建议:
1. 尽量开启死锁检测;
2. 尽量使用小事务,在业务允许范围内,将隔离级别改成读已提交,可以减少不些不必要的锁;
3. 避免全表扫描;
4. 避免较多索引;
5. 不同事务对表和行操作的顺序尽量一致。
- End -
更多精彩
![](https://filescdn.proginn.com/d0d6de0c4890400d1064b18b093760a2/5d4cd58cb75b4b7e733809780f4208a3.webp)
绝了!这个MySQL故障定位方法太好用了
![](https://filescdn.proginn.com/dd2c3be31e4a6e5ad648ff1ac949e3f3/ad900e9db65ef501fb372bb4b765269f.webp)
数据库诊断不了的,腾讯大神来“诊断”