乐趣区

关于mysql:手把手教你分析Mysql死锁问题

jiezi

前言

产生死锁了，如何排查和解决呢？本文将跟你一起探讨这个问题

  • 筹备好数据环境
  • 模仿死锁案发
  • 剖析死锁日志
  • 剖析死锁后果

环境筹备

数据库隔离级别：

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

主动提交敞开：

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; 查看锁状况：

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

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

锁介绍

在剖析死锁日志前，先做一下锁介绍，哈哈~


次要介绍一下兼容性以及锁模式类型的锁：

共享锁与排他锁

InnoDB 实现了规范的行级锁，包含两种：共享锁（简称 s 锁）、排它锁（简称 x 锁）。

  • 共享锁（S 锁）：容许持锁事务读取一行。
  • 排他锁（X 锁）：容许持锁事务更新或者删除一行。

如果事务 T1 持有行 r 的 s 锁，那么另一个事务 T2 申请 r 的锁时，会做如下解决：

  • T2 申请 s 锁立刻被容许，后果 T1 T2 都持有 r 行的 s 锁
  • T2 申请 x 锁不能被立刻容许

如果 T1 持有 r 的 x 锁，那么 T2 申请 r 的 x、s 锁都不能被立刻容许，T2 必须期待 T1 开释 x 锁才能够，因为 X 锁与任何的锁都不兼容。

意向锁

  • 动向共享锁(IS 锁)：事务想要取得一张表中某几行的共享锁
  • 动向排他锁(IX 锁)：事务想要取得一张表中某几行的排他锁

比方：事务 1 在表 1 上加了 S 锁后，事务 2 想要更改某行记录，须要增加 IX 锁，因为不兼容，所以须要期待 S 锁开释；如果事务 1 在表 1 上加了 IS 锁，事务 2 增加的 IX 锁与 IS 锁兼容，就能够操作，这就实现了更细粒度的加锁。

InnoDB 存储引擎中锁的兼容性如下表：

记录锁（Record Locks）

  • 记录锁是最简略的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
  • 记录锁 永远都是加在索引上 的，即便一个表没有索引，InnoDB 也会隐式的创立一个索引，并应用这个索引施行记录锁。
  • 会阻塞其余事务对其插入、更新、删除

记录锁的事务数据（关键词：lock_mode X locks rec but not gap），记录如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

间隙锁（Gap Locks）

  • 间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最初一个索引之后的间隙。
  • 应用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
  • 间隙锁只阻止其余事务插入到间隙中，他们不阻止其余事务在同一个间隙上取得间隙锁，所以 gap x lock 和 gap s lock 有雷同的作用。

间隙锁的事务数据（关键词：gap before rec），记录如下：

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     ;;

Next-Key Locks

  • Next-key 锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录后面间隙上的锁。

插入意向锁（Insert Intention）

  • 插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁开释了一种插入方式的信号，亦即多个事务在雷同的索引间隙插入时如果不是插入间隙中雷同的地位就不须要相互期待。
  • 假如有索引值 4、7，几个不同的事务筹备插入 5、6，每个锁都在取得插入行的独占锁之前用插入意向锁各自锁住了 4、7 之间的间隙，然而不阻塞对方因为插入行不抵触。

事务数据相似于上面：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

锁模式兼容矩阵（横向是已持有锁，纵向是正在申请的锁）：

如何读懂死锁日志？

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 拿了具体什么的间隙锁呢？它为什么也要拿插入意向锁？
  • 死锁的死循环是怎么造成的？目前日志看不出死循环形成呢？

咱们接下来一大节详细分析一波，一个一个问题来~

死锁剖析

死锁死循环四因素

  • 互斥条件：指过程对所调配到的资源进行排它性应用，即在一段时间内某资源只由一个过程占用。如果此时还有其它过程申请资源，则请求者只能期待，直至占有资源的过程用毕开释。
  • 申请和放弃条件：指过程曾经放弃至多一个资源，但又提出了新的资源申请，而该资源已被其它过程占有，此时申请过程阻塞，但又对本人已取得的其它资源放弃不放。
  • 不剥夺条件：指过程已取得的资源，在未应用完之前，不能被剥夺，只能在应用完时由本人开释。
  • 环路期待条件：指在产生死锁时，必然存在一个过程——资源的环形链，即过程汇合 {P0，P1，P2，···，Pn} 中的 P0 正在期待一个 P1 占用的资源；P1 正在期待 P2 占用的资源，……，Pn 正在期待已被 P0 占用的资源。

事务 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 ~

总结

最初，遇到死锁问题，咱们应该怎么剖析呢？

  • 模仿死锁场景
  • show engine innodb status; 查看死锁日志
  • 找出死锁 SQL
  • SQL 加锁剖析，这个能够去官网看哈
  • 剖析死锁日志（持有什么锁，期待什么锁）
  • 相熟锁模式兼容矩阵，InnoDB 存储引擎中锁的兼容性矩阵。

集体公众号

  • 感觉写得好的小伙伴给个点赞 + 关注啦，谢谢~
  • 如果有写得不正确的中央，麻烦指出，感激不尽。
  • 同时十分期待小伙伴们可能关注我公众号，前面缓缓推出更好的干货~ 嘻嘻
退出移动版