读懂死锁日志

环境准备

数据库隔离级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
复制代码

select @@transaction_isolation;

自动提交关闭：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

表结构:

//id是自增主键，name是非唯一索引，balance普通字段
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

表中的数据：

模拟并发

开启两个终端模拟事务并发情况，执行顺序以及实验现象如下：

1）事务A执行更新操作，更新成功

mysql> update  account  set balance =1000 where name ='Wei';
Query OK, 1 row affected (0.01 sec)

2）事务B执行更新操作，更新成功

mysql> update  account  set balance =1000 where name ='Eason';
Query OK, 1 row affected (0.01 sec)

3）事务A执行插入操作，陷入阻塞~

mysql> insert into account values(null,'Jay',100);

这时候可以用select * from information_schema.innodb_locks;查看锁情况(5.7)(8不行)：

4）事务B执行插入操作，插入成功，同时事务A的插入由阻塞变为死锁error。

mysql> insert into account values(null,'Yan',100);
Query OK, 1 row affected (0.01 sec)

如何读懂死锁日志？

show engine innodb status

可以用show engine innodb status，查看最近一次死锁日志哈~，执行后，死锁日志如下：

2020-04-11 00:35:55 0x243c
*** (1) TRANSACTION:
TRANSACTION 38048, ACTIVE 92 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 2300, query id 2362 localhost ::1 root update
insert into account values(null,'Jay',100)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38048 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;
*** (2) TRANSACTION:
TRANSACTION 38049, ACTIVE 72 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 52, OS thread handle 9276, query id 2363 localhost ::1 root update
insert into account  values(null,'Yan',100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)

我们如何分析以上死锁日志呢？

第一部分

1）找到关键词TRANSACTION，事务38048

2）查看正在执行的SQL

insert into account values(null,'Jay',100)

3）正在等待锁释放(WAITING FOR THIS LOCK TO BE GRANTED)，插入意向排他锁（lock_mode X locks gap before rec insert intention waiting），普通索引（idx_name），物理记录(PHYSICAL RECORD)，间隙区间（未知，Wei）;

第二部分

1）找到关键词TRANSACTION，事务38049

2）查看正在执行的SQL

insert into account  values(null,'Yan',100)

3）持有锁(HOLDS THE LOCK)，间隙锁(lock_mode X locks gap before rec)，普通索引(index idx_name)，物理记录(physical record)，区间（未知，Wei）;

4）正在等待锁释放(waiting for this lock to be granted)，插入意向锁(lock_mode X insert intention waiting)，普通索引上(index idx_name)，物理记录(physical record)，间隙区间（未知，+∞）;

5）事务1回滚(we roll back transaction 1)；

查看日志结果

查看日志可得：

• 事务A正在等待的插入意向排他锁（事务A即日志的事务1，根据insert语句来对号入座的哈），正在事务B的怀里~
• 事务B持有间隙锁，正在等待插入意向排它锁

这里面，有些朋友可能有疑惑，

• 事务A持有什么锁呢？日志根本看不出来。它又想拿什么样的插入意向排他锁呢？
• 事务B拿了具体什么的间隙锁呢？它为什么也要拿插入意向锁？
• 死锁的死循环是怎么形成的？目前日志看不出死循环构成呢？

  死锁分析

  事务A持有什么锁呢？它又想拿什么样的插入意向排他锁呢？

  为了方便记录，例子用W表示Wei，J表示Jay，E表示Eason哈~

  我们先来分析事务A中update语句的加锁情况~

  update  account  set balance =1000 where name ='Wei';

间隙锁：

• Update语句会在非唯一索引的name加上左区间的间隙锁，右区间的间隙锁(因为目前表中只有name=’Wei’的一条记录，所以没有中间的间隙锁~)，即（E,W) 和（W，+∞）
• 为什么存在间隙锁？因为这是RR的数据库隔离级别，用来解决幻读问题用的~

记录锁

• 因为name是索引，所以该update语句肯定会加上W的记录锁

Next-Key锁

• Next-Key锁=记录锁+间隙锁，所以该update语句就有了（E，W]的 Next-Key锁

综上所述，事务A执行完update更新语句，会持有锁：

• Next-key Lock：（E，W]
• Gap Lock ：（W，+∞）

  我们再来分析一波事务A中insert语句的加锁情况

  insert into account values(null,'Jay',100);

间隙锁：

• 因为Jay(J在E和W之间)，所以需要请求加(E,W)的间隙锁

插入意向锁（Insert Intention）

• 插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即事务A需要插入意向锁(E,W)

因此，事务A的update语句和insert语句执行完，它是持有了 （E，W]的 Next-Key锁，（W，+∞）的Gap锁，想拿到 (E,W)的插入意向排它锁，等待的锁跟死锁日志是对上的，哈哈~

事务B拥有了什么间隙锁？它为什么也要拿插入意向锁？

同理，我们再来分析一波事务B，update语句的加锁分析：

update  account  set balance =1000 where name ='Eason';

间隙锁：

• Update语句会在非唯一索引的name加上左区间的间隙锁，右区间的间隙锁(因为目前表中只有name=’Eason’的一条记录，所以没有中间的间隙锁~)，即（-∞，E）和（E，W）

记录锁

• 因为name是索引，所以该update语句肯定会加上E的记录锁

Next-Key锁

• Next-Key锁=记录锁+间隙锁，所以该Update语句就有了（-∞，E]的 Next-Key锁

综上所述，事务B执行完update更新语句，会持有锁：

• Next-key Lock：（-∞，E]

• Gap Lock ：（E，W）

  我们再来分析一波B中insert语句的加锁情况

  insert into account  values(null,'Yan',100);

  间隙锁：

• 因为Yan(Y在W之后)，所以需要请求加(W,+∞)的间隙锁

插入意向锁（Insert Intention）

• 插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即事务A需要插入意向锁(W,+∞)

所以，事务B的update语句和insert语句执行完，它是持有了 （-∞，E]的 Next-Key锁，（E，W）的Gap锁，想拿到 (W,+∞)的间隙锁，即插入意向排它锁，加锁情况跟死锁日志也是对上的~

死锁真相还原

接下来呢，让我们一起还原死锁真相吧~哈哈~

• 事务A执行完Update Wei的语句，持有（E，W]的Next-key Lock，（W，+∞）的Gap Lock ，插入成功~
• 事务B执行完Update Eason语句，持有（-∞，E]的 Next-Key Lock，（E，W）的Gap Lock，插入成功~
• 事务A执行Insert Jay的语句时，因为需要（E，W）的插入意向锁，但是（E，W）在事务B怀里，所以它陷入心塞~
• 事务B执行Insert Yan的语句时，因为需要(W,+∞) 的插入意向锁，但是(W,+∞) 在事务A怀里，所以它也陷入心塞。
• 事务A持有（W，+∞）的Gap Lock，在等待（E，W）的插入意向锁，事务B持有（E，W）的Gap锁，在等待(W,+∞) 的插入意向锁，所以形成了死锁的闭环~（Gap锁与插入意向锁会冲突的，可以看回锁介绍的锁模式兼容矩阵哈~）
• 事务A,B形成了死锁闭环后，因为Innodb的底层机制，它会让其中一个事务让出资源，另外的事务执行成功，这就是为什么你最后看到事务B插入成功了，但是事务A的插入显示了Deadlock found ~