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

相关推荐

  • Navicat161 数据库文件修复

    错误分析 此错误一般为表损坏,修复即可。可能造成原因为服务器突然断电,而有程序还在往表里写数据或者表的数据很大或者数据搬迁的时候编码格式有误,避免浪费时间去修表,可采用Navicat去快速修复表!!! 登录Navicat数据库,找到报错的数据表,右键维护->修复表->拓展或者快速

    2022年11月17日
    1.2K00
  • Sql Server数据库显示中文乱码的解决方案

    .数据库中文乱码原因 1. 一种情况是实际生产环境的电脑并不支持中文语言,但是数据库的数据表里面,有些说明字段需要用中文显示,这个时候就出现了乱码了 2. 另外一种情况是,安装数据库的时候,使用的是默认的编码规则。这个时候如果电脑本身就不支持中文语言。然后数据库表里面录入中文,也会出现乱码。支持中文的电脑,安装使用默认的选项,数据表录入中文,好像没有出现过乱…

    2023年2月18日
    92200
  • KeyDB: 一款比 Redis 性能更强的分布式数据库

    eyDB 是一个开源的,高性能的,多线程的内存键值存储。它是流行的 Redis 数据库的分支版本,完全兼容 Redis 命令和数据类型。KeyDB 提供了很多的高级功能,如 Lua 脚本,LRU 淘汰和命令的并行执行。它是为企业环境构建的,提供高可用性和集群支持。KeyDB 也针对速度进行了优化,基准测试表明它是可用的键值存储之一。总的来说,KeyDB 是内…

    2023年6月28日
    1.1K00
  • 数据库慢查询及其优化

    我们经常会谈到数据库慢查询。那么什么是数据库慢查询以及导致数据库慢查询的常见原因,以及对应的解决方法。 1、什么是数据库慢查询 数据库慢查询,就是查询时间超过了我们设定的时间的语句。可以通过语句查看设定的时间: 默认的设定时间是10秒,也可以通过下面这个语句修改默认的设定时间: 2、MySQL 慢查询的相关参数解释 3、慢查询日志配置 默认情况下slow_q…

    2022年6月11日
    1.1K00
  • 关于mysql now()显示的时间不正确

    如果MySQL中的NOW()函数返回的时间不正确,可能有以下几个原因: 时区设置不正确。请使用SELECT @@global.time_zone;命令检查MySQL服务器的全局时区设置,并确保它与你所在地区的时区匹配。如果时区设置不正确,请使用SET GLOBAL time_zone = ‘timezone’;命令进行更正。 系统时间…

    2023年8月22日
    1.5K00

发表回复

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

在线咨询: QQ交谈

邮件:712342017@qq.com

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

关注微信