这个不可思议的死锁你会解吗？

　　| 作者王起帆，腾讯CSIG数据库产品中心后台开发工程师，目前主要参与DBbrain开发工作，热爱技术，欢迎留言进行交流。
　　我们都知道，数据库系统中，不同线程并发访问数据，为了保护数据，在执行SQL语句时候需要对数据加锁。而死锁是一个经常遇到问题，SQL语句加锁和事物隔离级别，访问的索引是不是唯一，访问数据是否存在都有关系，往往死锁分析非常复杂。这篇文章将介绍一个＂简单的死锁＂，这个死锁产生的事物中SQL语句都只有一条，而且业务非常简单就是删除一条记录。两个事物同时执行以下两个SQL语句就有可能死锁。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 , `dept_no` char NOT , `num` int(11) NOT , `to_date` date NOT , 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＂,,＂9999-01-01＂), (1005,＂d002＂,,＂1989-12-17＂), (1007,＂d002＂,,＂9999-01-01＂), (1008,＂d004＂,,＂1988-09-09＂), (1009,＂d004＂,,＂1992-08-02＂), (1010,＂d005＂,9,＂1996-08-30＂);
　　使用执行两个sql很难，使用 mysqlslap 来高并发碰碰运气：# mysqlslap --create-schema dldb -q ＂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 &
　　这两个事物非常都是删除一行相同的数据 (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***  TRANSACTION:TRANSACTION 1681994, ACTIVE 0 sec updating or deletingmysql tables in use , locked LOCK WAIT  lock struct(s), heap size 1136,  row lock(s), undo log entries MySQL thread id 30, OS thread handle 123145456488448, query id 343687 localhost 127.0.0. root updatingDELETE FROM dept_manager WHERE num = 0***  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 waitingRecord lock, heap no  PHYSICAL RECORD: n_fields ; compact format; info bits 0 0: len ; hex 64303031; asc d001;; : len ; hex 800003e9; asc ;;
　　***  TRANSACTION:TRANSACTION 1681554, ACTIVE 0 sec starting index readmysql tables in use , locked  lock struct(s), heap size 1136,  row lock(s)MySQL thread id 106, OS thread handle 123145477099520, query id 341105 localhost 127.0.0. root updatingDELETE FROM dept_manager WHERE dept_no = ＂d001＂***  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 XRecord lock, heap no  PHYSICAL RECORD: n_fields ; compact format; info bits 0 0: len ; hex 64303031; asc d001;; : len ; hex 800003e9; asc ;;
　　***  WAITING FOR THIS LOCK TO BE GRANTED: # 请求主键RECORD LOCKS space id 367 page no  n bits 80 index PRIMARY of table `employees`.`dept_manager` trx id 1681554 lock_mode X locks rec but not gap waitingRecord lock, heap no  PHYSICAL RECORD: n_fields 6; compact format; info bits 32 0: len ; hex 800003e9; asc ;; : len 6; hex 00000019aa4a; asc J;; : len ; hex c000001b80ede; asc , ;; : len ; hex 64303031; asc d001;; : len ; hex 80000000; asc ;; 5: len ; hex f8f41; asc A;;
　　*** WE ROLL BACK TRANSACTION
　　四、总结
　　本文介绍的样例中，尽管SQL语句很简单，但由于表中有多个索引，对索引的访问顺序不同，造成死锁风险。为了避免数据库中发生死锁，建议：
　　1. 尽量开启死锁检测；
　　2. 尽量使用小事务，在业务允许范围内，将隔离级别改成读已提交，可以减少不些不必要的锁；
　　3. 避免全表扫描；
　　4. 避免较多索引；
　　5. 不同事务对表和行操作的顺序尽量一致。
　　- End -