• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL死锁案例分析

MySQL SEian.G 3个月前 (10-11) 233次浏览 已收录 0个评论
文章目录[隐藏]

本文针对上一篇《MySQL优化案例分享》文章中提到的线上业务产生的一个死问题进行展开讨论,主要针对两个update操作导致的死的场景,借此机会正好总结下MySQL及分析下产生死的原因和解决方案;

首先,针对MySQL中提供的种类做一个简单的总结,关于更多MySQL相关的内容可参考官方文档

MySQL InnoDB存储引擎提供了如下几种锁:

1、共享/排他锁(S/X锁)

共享锁(S Lock):允许事务读取一行数据,多个事务可以拿到一把S锁(即读读并行);

排他锁(X Lock):允许事务删除或更新一行数据,多个事务有且只有一个事务可以拿到X锁(即写写/写读互斥);

2、意向锁(Intention Lock)

意向锁是一种表级别的锁,意味着事务在更细的粒度上进行加锁。

意向共享锁(IS Lock):事务想要获得一张表中某几行的共享锁;

意向排他锁(IX Lock):事务想要获得一张表中某几行的排他锁;

举个例子,事务1在表1上加了S锁后,事务2想要更改某行记录,需要添加IX锁,由于不兼容,所以需要等待S锁释放;如果事务1在表1上加了IS锁,事务2添加的IX锁与IS锁兼容,就可以操作,这就实现了更细粒度的加锁。

3、插入意向锁(Insert Intention Lock)

插入意向锁是间隙锁的一种,专门针对insert操作的。即多个事务在同一个索引、同一个范围区间内插入记录时,如果插入的位置不冲突,则不会阻塞彼此;

举个例子:在可重复读隔离级别下,对PK ID为10-20的数据进行操作:

事务1在10-20的记录中插入了一行:

insert into table value(11, xx)

事务2在10-20的记录中插入了一行:

insert into table value(12, xx)

由于两条插入的记录不冲突,所以会使用插入意向锁,且事务2不会被阻塞。

4、自增锁(Auto-inc Locks)

自增锁是一种特殊的表级别锁,专门针对事务插入AUTO-INCREMENT类型的列。

即一个事务正在往表中插入记录时,其他事务的插入必须等待,以便第1个事务插入的行得到的主键值是连续的。

举个例子:在可重复读隔离级别下,PK ID为自增主键

表中已有主键ID为1、2、3的3条记录。

事务1插入了一行:

insert into table value(‘aa’)

得到一条(4,’aa’)的记录,未提交;

此时,事务2中插入了一行:

insert into table value(‘bb’)

这时会被阻塞,即用到了插入意向锁的概念。

5、记录锁(Record Locks)- locks rec but not gap

记录锁是的单个行记录上的锁,会阻塞其他事务对其插入、更新、删除;

6、间隙锁(Gap Lock)

间隙锁锁定记录的一个间隔,但不包含记录本身。

举个例子:

假如数据库已有ID为1、6两条记录,现在想要在ID in (4,10)之间更新数据的时候,会加上间隙锁,锁住[4,5] [7,10] ,(不包含已有记录ID=5本身)

那么在更新ID=5的记录(只有一条记录)符合条件;

如果不加间隙锁,事务2有可能会在4、10之间插入一条数据,这个时候事务1再去更新,发现在(4,10)这个区间内多出了一条“幻影”数据。

间隙锁就是防止其他事务在间隔中插入数据,以导致“不可重复读”。

7、临键锁(Next-Key Lock)= Gap Lock + Record Lock

临建锁是记录锁与间隙锁的组合,即:既包含索引记录,又包含索引区间,主要是为了解决幻读。

案例分析

MySQL版本:MySQL 5.7

隔离级别:RC

Session1 Session2
T1 begin;
select * from locktest where name=’test’ lock in share mode;
begin;
select * from locktest where name=’test’ lock in share mode;
T2 update locktest set addr=’nanjin’ where name=’test’;[产生等待]
T3 update locktest set addr=’nanjin’ where name=’test’ and ctime=0;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
[产生死锁,回滚]

死锁日志:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-04-30 17:24:43 0x7facbf829700
*** (1) TRANSACTION:
TRANSACTION 10021140, ACTIVE 59 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 1111, OS thread handle 140379923085056, query id 52685 127.0.0.1 dba_admin updating
update locktest set addr='nanjin' where name='test'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 69 page no 4 n bits 72 index idx_name of table `wjqtest`.`locktest` trx id 10021140 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 10021141, ACTIVE 35 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 1112, OS thread handle 140379924109056, query id 52686 127.0.0.1 dba_admin updating
update locktest set addr='nanjin' where name='test' and ctime=0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 69 page no 4 n bits 72 index idx_name of table `wjqtest`.`locktest` trx id 10021141 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 69 page no 4 n bits 72 index idx_name of table `wjqtest`.`locktest` trx id 10021141 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

错误日志中同样可以看到死锁信息记录:

2021-04-30T17:24:43.164029Z 1112 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

那么我们通过对上述操作,结合死锁日志进行分析:

Session1的lock in share mode获取到S锁(lock mode S locks rec but not gap),Session1的lock in share mode获取到S锁(lock mode S locks rec but not gap),
Session1执行update操作申请X锁,等待Session2释放(lock_mode X locks rec but not gap waiting),Session2执行update操作同样申请X锁,等待Session1释放(lock_mode X locks rec but not gap waiting),从而导致死锁产生;Session2死锁后回滚并抛出错误ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

在该场景下,将update操作改为delete会得到同样的效果,同样也会产生死锁;

那么为什么会出现死锁呢?

两个事务同时对一条数据进行更新的时候(可能并发也可能非并发),两个update语句都拿到了S锁,但是升级X锁的时候,出现问题,因为S锁升级X锁,在隔离级别是RC的情况下,必须等所有的S锁释放才能S锁升X锁,所以两个事务相互等待,导致死锁了。

那么针对这种情况该如何解决呢?

MySQL之上加了一层redis缓存锁,防止多个事务同时更新一个数据,如果有其他的解决方法,欢迎大家留言讨论;

MySQL死锁案例分析


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL死锁案例分析
喜欢 (2)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址