记一次mysql死锁问题的排查

最近在工作中遇到一些死锁的问题，所以简单研究了一下后，写下一篇文章分享一下。

1.如何查看看mysql中出现的死锁？

通过show engine innodb status 查看的日志是最新一次记录死锁的日志。

通过查看死锁日志可以看到如下格式的日志

------------------------

LATEST DETECTED DEADLOCK

------------------------

2020-08-27 10:59:02 0x7f0a2a44d700

*** (1) TRANSACTION:

TRANSACTION 40124869845, ACTIVE 0 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 23518445, OS thread handle 139681814148864, query id 159005367992 127.0.0.1 table updating

/*id:da7f25fb*/delete from table

where A = 1537169 and D = 10

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 3053 page no 264692 n bits 392 index idx_deal of table `database`.`table` trx id 40124869845 lock_mode X waiting

*** (2) TRANSACTION:

TRANSACTION 40124869844, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1

MySQL thread id 23515886, OS thread handle 139681635555072, query id 159005367991 10.27.218.212 table update

/*id:a4f70f31*/insert into table (A, B, C, D)

VALUES (1537167, 150539097,2427520, 10)

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 3053 page no 264692 n bits 392 index idx_deal of table `database`.`table` trx id 40124869844 lock mode S

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 3053 page no 264692 n bits 392 index idx_deal of table `database`.`table` trx id 40124869844 lock_mode X locks gap before rec insert intention waiting

*** WE ROLL BACK TRANSACTION (1)

------------

TRANSACTIONS

------------

2.如何读懂死锁日志

TRANSACTION 40124869845, ACTIVE 0 sec starting index read

事务编号为 40124869845 ，活跃0秒，starting index read 表示事务状态为根据索引读取数据。常见的其他状态:

fetching rows 表示事务状态在row_search_for_mysql中被设置，表示正在查找记录。

updating or deleting 表示事务已经真正进入了Update/delete的函数逻辑（row_update_for_mysql）

thread declared inside InnoDB 说明事务已经进入innodb层。通常而言不在innodb层的事务大部分是会被回滚的。

(1)WAITING FOR THIS LOCK TO BE GRANTED: 表示事务（1）正在等待lock_mode X waiting 此处就是X锁的排它锁。

(2) HOLDS THE LOCK(S):表示事务40124869844 持有的lock mode s锁（S锁），同时(2) WAITING FOR THIS LOCK TO BE GRANTED:表示事务（2）正在等到lock_mode X locks gap before rec insert intention waiting锁也就是插入意向锁。

3.mysql死锁日志中出现的锁解释

3.1基本锁

首先我们要知道对于MySQL有两种常规锁模式

LOCK_S（读锁，共享锁）

LOCK_X（写锁，排它锁）

最容易理解的锁模式，读加共享锁，写加排它锁.

3.2 意向锁（Intention Locks）

InnoDB为了支持多粒度(表锁与行锁)的锁并存，引入意向锁。意向锁是表级锁，可分为意向共享锁(IS锁)和意向排他锁(IX锁)。

事务在请求S锁和X锁前，需要先获得对应的IS、IX锁。

意向锁产生的主要目的是为了处理行锁和表锁之间的冲突，用于表明“某个事务正在某一行上持有了锁，或者准备去持有锁”。

3.3 记录锁（也叫行锁）

记录锁(Record Locks)

记录锁, 仅仅锁住索引记录的一行。

单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

3.4 间隙锁（Gap Lock）

区间锁, 仅仅锁住一个索引区间(开区间)。

在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

3.5 next-key锁(Next-Key Locks)

record lock + gap lock, 左开右闭区间。

默认情况下，innodb使用next-key locks来锁定记录。

但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

3.6插入意向锁(Insert Intention Locks)

Gap Lock中存在一种插入意向锁（Insert Intention Lock），在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。

假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

锁之间的冲突与兼容

4.insert加锁过程

简单的insert会在insert的行对应的索引记录上加一个排它锁，这是一个record lock，并没有gap，所以并不会阻塞其他session在gap间隙里插入记录。

不过在insert操作之前，还会加一种锁，官方文档称它为insertion intention gap lock，也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。

假设发生了一个唯一键冲突错误，那么将会在重复的索引记录上加读锁（lock s）。当有多个session同时插入相同的行记录时，如果另外一个session已经获得该行的排它锁，那么将会导致死锁。

5.insert死锁场景分析

创建一张表

CREATE TABLE `aa` (

`id` int(10) unsigned NOT NULL COMMENT '主键',

`name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',

`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',

`stage` int(11) NOT NULL DEFAULT '0' COMMENT '关卡数',

PRIMARY KEY (`id`),

UNIQUE KEY `udx_name` (`name`),

KEY `idx_stage` (`stage`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据

事务执行顺序

如果T1未rollback，而是commit的话，T2和T3会报唯一键冲突：ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’。

事务T1 提交以后等到的死锁日志如下：

------------------------

LATEST DETECTED DEADLOCK

------------------------

2020-08-28 12:28:34 700000a3f000

*** (1) TRANSACTION:

TRANSACTION 36831, ACTIVE 17 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1

MySQL thread id 38, OS thread handle 0x700000b0b000, query id 953 localhost root update

insert into t values (4,5)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 25 page no 4 n bits 72 index `idx_b` of table `test`.`t` trx id 36831 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000016; asc ;;

1: len 4; hex 8000000b; asc ;;

*** (2) TRANSACTION:

TRANSACTION 36832, ACTIVE 13 sec inserting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 360, 2 row lock(s)

MySQL thread id 39, OS thread handle 0x700000a3f000, query id 954 localhost root update

insert into t values (4,5)

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 25 page no 4 n bits 72 index `idx_b` of table `test`.`t` trx id 36832 lock_mode X locks gap before rec

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000016; asc ;;

1: len 4; hex 8000000b; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 25 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 36832 lock mode S locks rec but not gap waiting

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000004; asc ;;

1: len 6; hex 000000008fdf; asc ;;

2: len 7; hex 8d000001d00110; asc ;;

3: len 4; hex 80000005; asc ;;

*** WE ROLL BACK TRANSACTION (2)

死锁成因分析：

事务T1成功插入记录，并获得索引id=6上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)。

紧接着事务T2、T3也开始插入记录，请求排他插入意向锁(LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION)；但由于发生重复唯一键冲突，前文提到过，如果插入出现重复键冲突时，各自请求的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)转成共享记录锁(LOCK_S | LOCK_REC_NOT_GAP)。此时T2和T3分别持有了不冲突的lock s 的共享记录锁。

T1回滚释放索引id=6上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)，T2和T3都要请求索引id=6上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)。

由于X锁与S锁互斥，对于T2来讲，需要获取id=6 的排它锁，由于T3此时获取了id=6 的S锁，所以T2要等到T3把锁释放了，同样的道理T3要等T2把S锁释放了。T2和T3都等待对方释放S锁。于是，死锁便产生了。

如果此场景下，只有两个事务T1与T2或者T1与T3，则不会引发如上死锁情况产生。

最后：打一个小广告，后续的文章会在微信公众号“程序员之家QAQ”推送，欢迎大家搜索关注~~