继续关于死锁

深入解析MySQL死锁

最新推荐文章于 2022-11-30 11:44:11 发布

原创最新推荐文章于 2022-11-30 11:44:11 发布 · 277 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

MySQL 专栏收录该内容

3 篇文章

订阅专栏

本文详细探讨了MySQL中死锁的现象及其原因，特别是在InnoDB存储引擎的上下文中。通过具体的项目案例，分析了死锁产生的具体场景，如DELETE与INSERT操作并发导致的死锁，并深入解释了涉及的不同类型的锁。

继续关于死锁的分析：上一篇分析大多只是表面上，概念上的。这篇根据项目中实际出现的死锁问题我们再来进一步分析一下。
来分析之前，先的弄清楚几个问题：
MVCC：
在MySQL的InnoDB中，实现的是基于多版本的并发控制协议(MVCC)：读不加锁，读写不冲突。
在MVCC中读操作分为两种：
快照读：读取的是记录当前的版本(有可能是历史版本)，不用加锁。简单的select语句属于快照读，不加锁：select * from test where ?。
当前读：读取的是最新版本，为什么喃，因为在这种情况下的所读到的记录都会加上锁，其他的记录是不会有机会去改变这个记录的。特殊的读：select * from test where ? lock in share mode;select * from table where ? for update;insert/update/delete/;都属于当前读。

MySQL/InnoDB定义的4种隔离级别：
Read Uncommited：可以读取到未提交的记录。这种级别是不会用到的。
Read Committed(RC)：对读取到记录加锁(记录锁)，存在幻读现象。
Repeatable Read(RR)：对读取到记录加锁(记录锁)，同时要对读取记录的范围加锁，新的记录在范围内的不能插入(GAP锁)，不存在幻读。
Serializable：从MVCC并发控制退化为基于锁的并发控制。所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。

意向锁：事务在请求S锁和X锁前，需要先获得对应的IS、IX锁。
意向共享锁(IS):事务想要获得一个表中某几行的共享锁。
意向排它锁(IX):事务想要获得一个表中某几行的排它锁。
意向锁不会阻塞除全表查询以外的任何请求。事务A和事务B可以同时获取同几行数据的IS和IX。

行锁：
记录锁(Record Locks)：仅仅锁住索引记录的一行。在单条索引记录上加锁，永远锁住的是索引，而非记录本身。

间隙锁(Gap Locks)：区间锁，锁住一个索引区间：具体在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括索引本身。

next-key锁(Next-Key Locks)：record lock+gap lock,左开右闭区间。innodb默认使用此锁来锁定记录。但是当查询的索引含有唯一属性的时候，Next-Key Lock会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

插入意向锁(Insert Intention Locks)：在Gap Locks中存在一种插入意向锁，这个锁是在insert时产生的。在多个事务同时写入不同数据到同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

自增锁(AUTO-INC Locks)：一种特殊的表级锁，发生在涉及到AUTO_INCREMENT列的事务性插入操作时产生。

行锁的兼容性矩阵：

[img]http://dl2.iteye.com/upload/attachment/0123/9612/52f4b70e-b2a1-3153-a075-96967ee3db3e.png[/img]

表注：横向是已经持有的锁，纵向是正在请求的锁。

一：delete语句加锁机制：
无索引下的删除动作+RR：
delete from t1 where id = 10;id上没有索引，只能进行全表扫描。所有的记录都被加上X锁，每条记录间的间隙也都被加上GAP锁。

二：insert语句加锁机制：


INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

简单的insert会在insert的行对应的索引记录上加一个排它锁，这是一个record lock，并没有gap，所以并不会阻塞其他session在gap间隙里插入记录。
在insert之前，还会加一种锁，叫insertion intention gap lock，也就是意向的gap锁，这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。并发的事务可以对同一个gap加意向gap锁。
假设发生了一个唯一键冲突错误(duplicate-key error)，那么将会在重复的索引记录上加共享锁。这个共享锁在并发的情况下是会产生死锁的，比如有两个并发的insert都对要对同一条记录加共享锁。而此时这条记录又被其他事务加上了排它锁。当这个事务提交或者回滚后。两个并发的insert操作是会发生死锁的(Ta等待Tb释放共享锁才可以往下走，Tb等待Ta释放共享锁才可以往下走)。
在insert的时候用的是LOCK_X排他锁 | LOCK_GAP间隙锁 | LOCK_INSERT_INTENTION插入意向锁去检查插入的间隙，这个模式下与LOCK_S共享锁 | LOCK_GAP间隙锁的锁模式冲突，与LOCK_X排他锁 | LOCK_GAP间隙锁的锁模式冲突。但是对于相同的间隙GAP，两个锁模式为LOCK_X排他锁 | LOCK_GAP间隙锁 | LOCK_INSERT_INTENTION插入意向锁是兼容的。

insert死锁场景分析：以下场景是参考了[url]http://yeshaoting.cn/article/database/mysql%20insert%E9%94%81%E6%9C%BA%E5%88%B6/[/url]
1. duplicate key error引发的死锁
这个主要发生在两个以上的事务同时进行唯一键相同的记录插入操作。

[img]http://dl2.iteye.com/upload/attachment/0123/9793/fa86d04d-7c67-3da7-a7e2-5df93f3c24ed.png[/img]

如果T1是commit，T2和T3会报唯一键冲突：ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’
如果T1是rollback，那么T3就会报Deadlock。
这个在线下操作后的结果和以上的情景一致。

死锁成因

1，事务T1成功插入记录，并获得索引id=6上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)。
2，紧接着事务T2、T3也开始插入记录，请求排他插入意向锁(LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION)；但由于发生重复唯一键冲突，各自请求的排他插入意向锁(LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION)转成共享记录锁(LOCK_S | LOCK_REC_NOT_GAP)。
3，T1回滚释放索引id=6上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)，T2和T3都要请求索引id=6上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)。
由于X锁与S锁互斥，T2和T3都等待对方释放S锁。
死锁便产生了！

2，GAP与Insert Intention冲突引发的死锁
表结构：


CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据：


mysql> select * from t;
+----+------+
| a  | b    |
+----+------+
|  1 |    2 |
|  2 |    3 |
|  3 |    4 |
| 11 |   22 |
+----+------+

[img]http://dl2.iteye.com/upload/attachment/0123/9799/b5bee694-0576-39e5-945a-a16a53f973d7.png[/img]

死锁成因
1，事务T1执行查询语句，在索引b=6上加排他Next-key锁(LOCK_X | LOCK_ORDINARY)，会锁住idx_b索引范围(4, 22)。
2，事务T2执行查询语句，在索引b=8上加排他Next-key锁(LOCK_X | LOCK_ORDINARY)，会锁住idx_b索引范围(4, 22)。由于请求的GAP与已持有的GAP是兼容的，因此，事务T2在idx_b索引范围(4, 22)也能加锁成功。
3，事务T1执行插入语句，会先加排他Insert Intention锁。由于请求的Insert Intention锁与已有的GAP锁不兼容，则事务T1等待T2释放GAP锁。
4，事务T2执行插入语句，也会等待T1释放GAP锁。
死锁便产生了。

回到项目中产生死锁的情况：
请看下面的报错日志(MySQL的):


BACKGROUND THREAD
-----------------
srv_master_thread loops: 990 srv_active, 0 srv_shutdown, 8035 srv_idle
srv_master_thread log flush and writes: 9025
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3350
OS WAIT ARRAY INFO: signal count 3305
RW-shared spins 0, rounds 4136, OS waits 2044
RW-excl spins 0, rounds 561, OS waits 14
RW-sx spins 56, rounds 303, OS waits 3
Spin rounds per wait: 4136.00 RW-shared, 561.00 RW-excl, 5.41 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-03-21 15:10:36 0x2d08
*** (1) TRANSACTION:
TRANSACTION 3342674, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 142, OS thread handle 2932, query id 61023 localhost 127.0.0.1 root update
insert into XXXX
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4580 page no 3 n bits 72 index PRIMARY of table `XXX`.`XXX` trx id 3342674 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;;

*** (2) TRANSACTION:
TRANSACTION 3342675, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 147, OS thread handle 11528, query id 61024 localhost 127.0.0.1 root update
insert into XXX
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4580 page no 3 n bits 72 index PRIMARY of table `XXX`.`XXX` trx id 3342675 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4580 page no 3 n bits 72 index PRIMARY of table `XXX`.`XXX` trx id 3342675 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 (2)
------------
TRANSACTIONS
------------
Trx id counter 3343825
Purge done for trx's n:o < 3343825 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283307779134672, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283307779139904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283307779139032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283307779137288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283307779133800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283307779132928, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283307779135544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283307779132056, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283307779131184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

因为涉及到项目中我只是把表和insert的字段隐藏了。其他的都是原样Log。
上面涉及到2个事务，这2个事务里面分别在做delete和insert操作。delete语句删除是使用没有索引的字段在删除，并且删除一片数据；insert语句在新增的数据是在delete语句中删除的数据。
根据前面介绍的经验，delete删除的时候会加X锁和GAP锁，那么在两个事务在delete的时候，因为GAP锁是兼容的，所有两个事务会获取到同样间隙的锁。然后再执行insert的时候，会对同在一个间隙锁上申请插入意向锁，因为插入意向锁和GAP锁是不兼容的。所有事务A在等待事务B释放间隙锁，事务B在等待事务A释放间隙锁，这样就会出现上面的死锁了。