mysql隔离级别和死锁_MySQL RR隔离级别死锁问题剖析

本案例在RR隔离级别下，模拟数据如下：

CREATE TABLE `ty` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`a` int(11) DEFAULT NULL,

`b` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idxa` (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

insert into ty(a,b) values(2,3),(5,4),(6,7);

1

2

3

4

5

6

7

8

9

CREATETABLE`ty`(

`id`int(11)NOTNULLAUTO_INCREMENT,

`a`int(11)DEFAULTNULL,

`b`int(11)DEFAULTNULL,

PRIMARYKEY(`id`),

KEY`idxa`(`a`)

)ENGINE=InnoDBAUTO_INCREMENT=8DEFAULTCHARSET=utf8mb4;

insertintoty(a,b)values(2,3),(5,4),(6,7);

下面操作发生死锁：

TRX-1

TRX-2

begin;

delete from ty where a=5;

begin;

delete from ty where a=5;#–等待TRX-1的X Lock –#

insert into ty(a,b) values(2,10);#– 等待TRX-2的 –#

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

来分析一下死锁产生的原因：

本死锁的堵塞主要集中在二级索引中，我们将二级索KEY idxa (a)和主键的数据按照InnoDB引擎存储的方式大概排列一下则如图：

TRX-1：delete from ty where a=5

产生的锁信息如下，记得打开 innodb_status_output_locks 参数。

---TRANSACTION 42206, ACTIVE 3 sec

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

MySQL thread id 887, OS thread handle 123145316831232, query id 8815 localhost root

TABLE LOCK table `test`.`ty` trx id 42206 lock mode IX

RECORD LOCKS space id 617 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 42206 lock_mode X

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000005; asc ;;

1: len 4; hex 80000009; asc ;;

RECORD LOCKS space id 617 page no 3 n bits 72 index PRIMARY of table `test`.`ty` trx id 42206 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 4; hex 80000009; asc ;;

1: len 6; hex 00000000a4de; asc ;;

2: len 7; hex 3c000001a70110; asc < ;;

3: len 4; hex 80000005; asc ;;

4: len 4; hex 80000004; asc ;;

RECORD LOCKS space id 617 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 42206 lock_mode X locks gap before rec

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

0: len 4; hex 80000006; asc ;;

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

---TRANSACTION42206,ACTIVE3sec

4lockstruct(s),heapsize1136,3rowlock(s),undologentries1

MySQLthreadid887,OSthreadhandle123145316831232,queryid8815localhostroot

TABLELOCKtable`test`.`ty`trxid42206lockmodeIX

RECORDLOCKSspaceid617pageno4nbits72indexidxaoftable`test`.`ty`trxid42206lock_modeX

Recordlock,heapno3PHYSICALRECORD:n_fields2;compactformat;infobits32

0:len4;hex80000005;asc;;

1:len4;hex80000009;asc;;

RECORDLOCKSspaceid617pageno3nbits72indexPRIMARYoftable`test`.`ty`trxid42206lock_modeXlocksrecbutnotgap

Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits32

0:len4;hex80000009;asc;;

1:len6;hex00000000a4de;asc;;

2:len7;hex3c000001a70110;asc

3:len4;hex80000005;asc;;

4:len4;hex80000004;asc;;

RECORDLOCKSspaceid617pageno4nbits72indexidxaoftable`test`.`ty`trxid42206lock_modeXlocksgapbeforerec

Recordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits0

0:len4;hex80000006;asc;;

1:len4;hex8000000a;asc;;

根据这个记录我们可以画图如下，红色部分为锁定的部分箭头为Gap Lock：

TRX-2：delete from ty where a=5

此时 TRX-2 产生阻塞，等待 TRX-1 的锁。所信息如下：

---TRANSACTION 42207, ACTIVE 3 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 888, OS thread handle 123145316274176, query id 8818 localhost root updating

delete from ty where a=5

------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 617 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 42207 lock_mode X waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000005; asc ;;

1: len 4; hex 80000009; asc ;;

1

2

3

4

5

6

7

8

9

10

---TRANSACTION42207,ACTIVE3secstartingindexread

mysqltablesinuse1,locked1

LOCKWAIT2lockstruct(s),heapsize1136,1rowlock(s)

MySQLthreadid888,OSthreadhandle123145316274176,queryid8818localhostrootupdating

deletefromtywherea=5

-------TRXHASBEENWAITING3SECFORTHISLOCKTOBEGRANTED:

RECORDLOCKSspaceid617pageno4nbits72indexidxaoftable`test`.`ty`trxid42207lock_modeXwaiting

Recordlock,heapno3PHYSICALRECORD:n_fields2;compactformat;infobits32

0:len4;hex80000005;asc;;

1:len4;hex80000009;asc;;

从日志里我们可以看到 TRX-2 当前正在执行delete from test where a = 5，该条语句正在申请索引 idxa 的X锁，所以提示lock_mode X waiting。

根据这个记录我们可以画图如下，黄色部分为事务 TRX-2 准备上锁但是被堵塞的部分，包含黄色部分和红色部分的记录说明它既被 TRX-1 锁定了，并且 TRX-1 拿不到这条记录的锁，它实际上就是一个next key lock的堵塞：

TRX-1：insert into ty(a,b) values(2,10)

则会发生死锁，实际上这一条记录在二级索引的值为(2,11)，11是主键的值，自增的。这里 insert 会申请一把 LOCK_INSERT_INTENTION(插入意向锁)。

INSERT INTENTION LOCK是GAP锁的一种，在事务执行 insert 的时候会申请一把插入意向锁(Insert Intention Lock)。在多事务并发写入不同数据记录至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。例如当前索引上有记录4和8，两个并发session同时插入记录6，7。他们会分别为(4,8)加上GAP锁，但相互之间并不冲突(因为插入的记录不冲突)。

但这把锁并非每次插入需要加的。另外，在RC事务隔离级别下，由于插入大部分是不需要等待的，所以这把锁大部分时候也是不存在的。只有当发生锁等待时，即插入的这条记录的下一条记录(next_rec)有锁，并且带有GAP属性时，则需要对next_rec再加一个插入意向锁。由于插入意向锁和S/X Lock不兼容，因此需要等待。

很明显，这里 insert 操作会申请 INSERT INTENTION LOCK，并会产生等待，画图如下：

这里 insert 产生等待也就意味着 TRX-1 被堵塞，然后因为这个区域 TRX-2 也处于堵塞下，则会发生死锁(对同一个对象加锁是需要排序等待的，但在堵塞队列已经有 TRX-2 了，所以 TRX-1 这个GAP锁是拿不到的，此时就形成了 TRX-2 等待 TRX-1，TRX-1 也等待 TRX-2，产生循环等待)。死锁记录如下：

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

LATEST DETECTED DEADLOCK

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

2018-09-29 15:30:16 0x700000dd9000

*** (1) TRANSACTION:

TRANSACTION 42207, ACTIVE 615 sec starting index read

mysql tables in use 1, locked 1

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

MySQL thread id 888, OS thread handle 123145316274176, query id 8820 localhost root updating

delete from ty where a=5

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

RECORD LOCKS space id 617 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 42207 lock_mode X waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000005; asc ;;

1: len 4; hex 80000009; asc ;;

*** (2) TRANSACTION:

TRANSACTION 42206, ACTIVE 691 sec inserting

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 887, OS thread handle 123145316831232, query id 8821 localhost root update

insert into ty(a,b) values(2,10)

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

RECORD LOCKS space id 617 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 42206 lock_mode X

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000005; asc ;;

1: len 4; hex 80000009; asc ;;

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

RECORD LOCKS space id 617 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 42206 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000005; asc ;;

1: len 4; hex 80000009; asc ;;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

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

LATESTDETECTEDDEADLOCK

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

2018-09-2915:30:160x700000dd9000

***(1)TRANSACTION:

TRANSACTION42207,ACTIVE615secstartingindexread

mysqltablesinuse1,locked1

LOCKWAIT2lockstruct(s),heapsize1136,2rowlock(s)

MySQLthreadid888,OSthreadhandle123145316274176,queryid8820localhostrootupdating

deletefromtywherea=5

***(1)WAITINGFORTHISLOCKTOBEGRANTED:

RECORDLOCKSspaceid617pageno4nbits72indexidxaoftable`test`.`ty`trxid42207lock_modeXwaiting

Recordlock,heapno3PHYSICALRECORD:n_fields2;compactformat;infobits32

0:len4;hex80000005;asc;;

1:len4;hex80000009;asc;;

***(2)TRANSACTION:

TRANSACTION42206,ACTIVE691secinserting

mysqltablesinuse1,locked1

5lockstruct(s),heapsize1136,4rowlock(s),undologentries2

MySQLthreadid887,OSthreadhandle123145316831232,queryid8821localhostrootupdate

insertintoty(a,b)values(2,10)

***(2)HOLDSTHELOCK(S):

RECORDLOCKSspaceid617pageno4nbits72indexidxaoftable`test`.`ty`trxid42206lock_modeX

Recordlock,heapno3PHYSICALRECORD:n_fields2;compactformat;infobits32

0:len4;hex80000005;asc;;

1:len4;hex80000009;asc;;

***(2)WAITINGFORTHISLOCKTOBEGRANTED:

RECORDLOCKSspaceid617pageno4nbits72indexidxaoftable`test`.`ty`trxid42206lock_modeXlocksgapbeforerecinsertintentionwaiting

Recordlock,heapno3PHYSICALRECORD:n_fields2;compactformat;infobits32

0:len4;hex80000005;asc;;

1:len4;hex80000009;asc;;

从死锁日志可以看出， (1) TRANSACTION 在等待 (5, 9) 的 X 锁，而 (2) TRANSACTION 持有  (5, 9) 的 X 锁，同时也等待  (5, 9) 的 X 锁，这里肯定就是在阻塞队列里面等待 (1) TRANSACTION 了，因为它不可能在同一个事务中等待自己持有的  (5, 9) 的 X 锁。所以可以看出两个事务产生了循环等待，死锁出现。

这一步如果是：insert into ty(a,b) values(5,10)，则不会发生死锁，实际上这一条记录记录在二级索引的值为(5,11)，11是主键的值，则画图如下：

如果是这种情况，不会发生死锁，我们可以看到对于二级索引而言这个区域没有其他事务堵塞，只是 TRX-1 最开始获取过，本事务再次获取不会有问题。

本案例实际上就是看最后触发死锁的插入操作插入的记录到底落在二级索引的哪个区域。

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。