這個不可思議的死鎖你會解嗎？死鎖模拟本文介紹的樣例中，盡管SQL語句很簡單，但由于表中有多個索引，對索引的通路順序不同，造成死鎖風險。為了避免資料庫中發生死鎖，建議：

| 作者 王起帆，腾讯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 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 来高并发碰碰运气：

# 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*** (1) TRANSACTION:TRANSACTION 1681994, ACTIVE 0 sec updating or deletingmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1MySQL thread id 30, OS thread handle 123145456488448, query id 343687 localhost 127.0.0.1 root updatingDELETE 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 waitingRecord 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 readmysql tables in use 1, locked 13 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 updatingDELETE 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 XRecord 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 waitingRecord 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 -

 更多精彩

绝了！这个MySQL故障定位方法太好用了

数据库诊断不了的，腾讯大神来“诊断”

↓↓超值云数据库点这儿~