前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL死锁案例分析

MySQL死锁案例分析

作者头像
SEian.G
发布2021-10-22 11:56:11
2.3K0
发布2021-10-22 11:56:11
举报
文章被收录于专栏:SEian.G学习记录

本文针对上一篇《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[产生死锁,回滚]

死锁日志:

代码语言:javascript
复制
------------------------
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)

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

代码语言:javascript
复制
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缓存锁,防止多个事务同时更新一个数据,如果有其他的解决方法,欢迎大家留言讨论;

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-10-12,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA的辛酸事儿 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 案例分析
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档