本篇内容介绍了“MySQL死锁的案例详解”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

一 前言
死锁,其实是一个很有意思,也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。
二 案例分析
2.1 环境说明
MySQL 5.6 事务隔离级别为RR

CREATE TABLE `ty`(

`id`int(11)NOTNULL 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

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

2.2 测试用例

T2

T1

begin;


delete from ty where a=5;

begin;


delete from ty where a=5;

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



delete from ty where a=5;

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


2.3 死锁日志

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

LATEST DETECTED DEADLOCK

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

2017-09-09 22:34:13 7f78eab82700

***(1)TRANSACTION:

TRANSACTION 462308399,ACTIVE 33 sec starting index read

mysql tablesinuse 1,locked 1

LOCKWAIT2lockstruct(s),heapsize360,1 rowlock(s)

MySQL thread id 3525577,OS thread handle 0x7f896cc4b700,query id 780039657 localhost root updating

delete from ty where a=5

***(1)WAITINGFORTHISLOCKTO BE GRANTED:

RECORDLOCKS space id 219 page no 4 n bits 72 index `idxa`oftable `test`.`ty` trx id 462308399 lock_mode X waiting

***(2)TRANSACTION:

TRANSACTION 462308398,ACTIVE 61 sec inserting,thread declared inside InnoDB 5000

mysql tablesinuse 1,locked 1

5lockstruct(s),heapsize1184,4 rowlock(s),undo log entries 2

MySQL thread id 3525490,OS thread handle 0x7f78eab82700,query id 780039714 localhost root update

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

***(2)HOLDS THELOCK(S):

RECORDLOCKS space id 219 page no 4 n bits 72 index `idxa`oftable `test`.`ty` trx id 462308398 lock_mode X

***(2)WAITINGFORTHISLOCKTO BE GRANTED:

RECORDLOCKS space id 219 page no 4 n bits 72 index `idxa`oftable `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting

***WE ROLL BACK TRANSACTION(1)

2.3分析死锁日志
首先要理解的是 对同一个字段申请加锁是需要排队. S GAP 于
其次表ty中a为普通索引字段,我们根据事务执行的时间顺序来解释,这样比较好理解。
a 根据死锁日志显示 事务2 也即sess1执行的事务,根据 HOLDS THE LOCK(S)显示
sess1 先执行 delete from ty where a=5 ,该事务持有索引a=5 的行锁lock_mode X ,因为是RR隔离级别,所以sess1 还持有两个gap锁[1,2]-[2,5], [2,5]-[3,6] 。
b 事务1的日志也即sess2执行的事务,申请对 a=5 加锁,一个rec lock 和两个gap锁,因为sess1中delete还没释放,故sess2的事务1等待sess1的事务2释放a=5的锁资源。
c 然后根据WAITING FOR THIS LOCK TO BE GRANTED,提示事务2 insert语句正在等待 lock_mode X locks gap before rec insert intention waiting,
因为insert语句 [4,2] 介于gap锁[1,2]-[2,5]之间,所以有了提示 "lock_mode X locks gap",insert语句必须等待前面 sess2中delete 获取锁并且释放锁。于是,sess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),循环等待,造成死锁。
问题 如果sess1 执行 insert into ty(a,b) values(5,10); sess2会遇到死锁吗?


三 案例二
3.1 索引为唯一键
MySQL 5.6 事务隔离级别为RR

CREATE TABLE `t2`(

`id`int(11)NOTNULL AUTO_INCREMENT,

`a`int(11)DEFAULT NULL,

`b`int(11)DEFAULT NULL,

PRIMARY KEY(`id`),

unique KEY `idxa`(`a`)

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

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

3.2 测试用例

T2

T1

begin;


delete from ty where a=5;

begin;


delete from ty where a=5;

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



delete from ty where a=5;

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


3.3 死锁日志

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

LATEST DETECTED DEADLOCK

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

2017-09-10 00:03:31 7f78ea936700

***(1)TRANSACTION:

TRANSACTION 462308445,ACTIVE 9 sec starting index read

mysql tablesinuse 1,locked 1

LOCKWAIT2lockstruct(s),heapsize360,1 rowlock(s)

MySQL thread id 3526009,OS thread handle 0x7f896cc4b700,query id 780047877 localhost root updating

delete from t2 where a=5

***(1)WAITINGFORTHISLOCKTO BE GRANTED:

RECORDLOCKS space id 221 page no 4 n bits 72 index `idxa`oftable `test`.`t2` trx id 462308445 lock_mode X waiting

***(2)TRANSACTION:

TRANSACTION 462308444,ACTIVE 17 sec inserting,thread declared inside InnoDB 5000

mysql tablesinuse 1,locked 1

4lockstruct(s),heapsize1184,3 rowlock(s),undo log entries 2

MySQL thread id 3526051,OS thread handle 0x7f78ea936700,query id 780047890 localhost root update

insert t2(a,b)values(5,10)

***(2)HOLDS THELOCK(S):

RECORDLOCKS space id 221 page no 4 n bits 72 index `idxa`oftable `test`.`t2` trx id 462308444 lock_mode X locks rec butnotgap

***(2)WAITINGFORTHISLOCKTO BE GRANTED:

RECORDLOCKS space id 221 page no 4 n bits 72 index `idxa`oftable `test`.`t2` trx id 462308444lockmode S waiting

***WE ROLL BACK TRANSACTION(1)

3.4 分析死锁日志
首先我们要特别说明delete的加锁逻辑

a 找到满足条件的记录,并且记录有效,则对记录加X锁,No Gap锁(lock_mode X locks rec butnotgap);

b 找到满足条件的记录,但是记录无效(标识为删除的记录),则对记录加next key锁(同时锁住记录本身,以及记录之前的Gap:lock_mode X);

c 未找到满足条件的记录,则对第一个不满足条件的记录加Gap锁,保证没有满足条件的记录插入(locks gap before rec)

undefinedlock_mode X locks rec but not gapundefinedlock mode S waitingundefinedInsert Intention Lock.
undefined
a 根据死锁日志显示 事务2 也即sess1执行的事务,根据 HOLDS THE LOCK(S)显示
sess1 先执行 delete from ty where a=5 ,该事务持有索引a=5 的行锁lock_mode X locks rec but not gap。因为本例中a是唯一键,故没有gap锁。
b 事务1的日志也即sess2执行的事务,申请对 a=5 加锁(X Next-key Lock),一个rec lock 但是因为sess1中delete 已经执行完成,记录无效没有被删除,锁还没释放,故sess2的事务1等待sess1的事务2释放a=5的锁资源,日志中提示 lock_mode X waiting.
c 然后根据WAITING FOR THIS LOCK TO BE GRANTED,提示事务2 insert语句正在等待 lock mode S waiting,为什么这次是 S 锁呢?因为a字段是一个唯一索引,所以insert语句会在插入前进行一次duplicate key的检查,需要申请S锁防止其他事务对a字段进行重复插入。而插入意向锁与T1已经insert语句必须等待前面 sess2中delete 获取a=5的行锁并且释放锁。
undefinedsess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),循环等待,造成死锁undefined

“MySQL死锁的案例详解”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!