MySQL InnoDB调试死锁

前提

1、在RR隔离级别下。
2、查看间隙锁是否关闭
区间锁(间隙锁,临键锁)是InnoDB特有施加在索引记录区间的锁,MySQL5.6可以手动关闭区间锁,它由innodb_locks_unsafe_for_binlog参数控制:

  • 设置为ON,表示关闭区间锁,此时一致性会被破坏(所以是unsafe)
  • 设置为OFF,表示开启区间锁

show global variables like “innodb_locks%”;

3、show global variables like “autocommit”; 查看事务是否自动提交。

数据准备

InnoDB的行锁都是实现在索引上的,实验可以使用主键,建表时设定为innodb引擎:

create table t (
id int(10) primary key
)engine=innodb;

-- 插入一些实验数据:
start transaction;
insert into t values(1);
insert into t values(3);
insert into t values(10);
commit;

这是实验的初始状态,不同实验开始之初,都默认回到初始状态。

实验一、间隙锁互斥

开启区间锁,RR的隔离级别下,上例会有:
(-infinity, 1)
(1, 3)
(3, 10)
(10, infinity)
这四个区间。

事务A删除某个区间内的一条不存在记录,获取到共享间隙锁,会阻止其他事务B在相应的区间插入数据,因为插入需要获取排他间隙锁。

-- session A:
set session autocommit=0;
start transaction;
delete from t where id=5;

-- session B:
set session autocommit=0;
start transaction;
insert into t values(0);
insert into t values(2);
insert into t values(12);
insert into t values(7);

事务B插入的值:0, 2, 12都不在(3, 10)区间内,能够成功插入,而7在(3, 10)这个区间内,会阻塞。

可以使用:show engine innodb status; 来查看锁的情况。

MySQL InnoDB调试死锁

insert into t values(7); 正在等待共享间隙锁的释放。

如果事务A提交或者回滚,事务B就能够获得相应的锁,以继续执行。

如果事务A一直不提交,事务B会一直等待,直到超时,超时后会显示:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

实验二、共享排他锁死锁

回到数据的初始状态,这次需要三个并发的session。

-- session A先执行:
set session autocommit=0;
start transaction;
insert into t values(7);
 
-- session B后执行:
set session autocommit=0;
start transaction;
insert into t values(7);
 
-- session C最后执行:
set session autocommit=0;
start transaction;
insert into t values(7);

三个事务都试图往表中插入一条为7的记录:
(1)A先执行,插入成功,并获取id=7的排他锁;
(2)B后执行,需要进行PK校验,故需要先获取id=7的共享锁,阻塞;
(3)C后执行,也需要进行PK校验,也要先获取id=7的共享锁,也阻塞;

如果此时,session A执行:
rollback;
id=7排他锁释放。

则B,C会继续进行主键校验:
(1)B会获取到id=7共享锁,主键未互斥;
(2)C也会获取到id=7共享锁,主键未互斥;

B和C要想插入成功,必须获得id=7的排他锁,但由于双方都已经获取到id=7的共享锁,它们都无法获取到彼此的排他锁,死锁就出现了。

当然,InnoDB有死锁检测机制,B和C中的一个事务会插入成功,另一个事务会自动放弃:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

实验三、并发间隙锁的死锁

共享排他锁,在并发量插入相同记录的情况下会出现,相应的案例比较容易分析。而并发的间隙锁死锁,是比较难定位的。

回到数据的初始状态,这次需要两个并发的session,其SQL执行序列如下:

A:set session autocommit=0;
A:start transaction;
A:delete from t where id=6;
         B:set session autocommit=0;
         B:start transaction;
         B:delete from t where id=7;
A:insert into t values(5);
         B:insert into t values(8);

A执行delete后,会获得(3, 10)的共享间隙锁。
B执行delete后,也会获得(3, 10)的共享间隙锁。
A执行insert后,希望获得(3, 10)的排他间隙锁,于是会阻塞。
B执行insert后,也希望获得(3, 10)的排他间隙锁,于是死锁出现。

仍然使用:
show engine innodb status;
来查看死锁的情况。

MySQL InnoDB调试死锁

另外,检测到死锁后,事务2自动回滚了:WE ROLL BACK TRANSACTION (2)
事务1将会执行成功。

总结

  • 并发事务,间隙锁可能互斥
    (1)A删除不存在的记录,获取共享间隙锁;
    (2)B插入,必须获得排他间隙锁,故互斥;
  • 并发插入相同记录,可能死锁(某一个回滚)
  • 并发插入,可能出现间隙锁死锁(难排查)
  • show engine innodb status; 可以查看InnoDB的锁情况,也可以调试死锁

文章来源:https://www.cnaaa.net,转载请注明出处:https://www.cnaaa.net/archives/9886

(0)
凯影的头像凯影
上一篇 2023年10月23日 下午5:18
下一篇 2023年10月24日 下午5:15

相关推荐

  • SQL Server 2008 R2双机热备之发布、订阅实现实时同步实践过程

    一、复制的功能概述          SQL Server 复制功能实现了主从库的读写分离,从而将主库的压力分解掉,主库就主要负责数据的增删改等,而从库主要负责查询。另外有了主、从库,也给生产数据增加了一层安全性,即备份。万一主库出了什么问题,从库却还在。       …

    2023年2月10日
    2.0K00
  • 【mySQL】left join、right join和join的区别

    首先,我们先来建两张表,第一张表命名为kemu,第二张表命名为score: 一、left join顾名思义,就是“左连接”,表1左连接表2,以左为主,表示以表1为主,关联上表2的数据,查出来的结果显示左边的所有数据,然后右边显示的是和左边有交集部分的数据。如下: 结果集: 二、right join “右连接”,表1右连接表2,以右为主,表示以表2为主,关联查…

    2023年8月29日
    1.2K00
  • 数据库压力测试

    一、前言 在前面的压力测试过程中,主要关注的是对接口以及服务器硬件性能进行压力测试,评估请求接口和硬件性能对服务的影响。但是对于多数Web应用来说,整个系统的瓶颈在于数据库。 原因很简单:Web应用中的其他因素,例如网络带宽、负载均衡节点、应用服务器(包括CPU、内存、硬盘、连接数等)、缓存,都很容易通过水平的扩展(俗称加机器)来实现性能的提高。而对于MyS…

    2023年3月21日
    82900
  • sql server 日志文件过大的处理方法

    选择文件–日志-限制大小为500m

    2023年6月13日
    69800
  • count(1)、count(*)与count(列名)的执行区别

    在工作中遇到count(*)、count(1)、count(col) ,可能会让你分不清楚,都是计数,干嘛这么搞这么多东西。 count 作用 COUNT(expression):返回查询的记录总数,expression 参数是一个字段或者 * 号。 测试 MySQL版本:5.7.29 创建一张用户表,并插入一百万条数据,其中gender字段有五十万行是为n…

    2023年1月12日
    1.0K00

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

在线咨询: QQ交谈

邮件:712342017@qq.com

工作时间:周一至周五,8:30-17:30,节假日休息

关注微信