前段时间系统老是出现insert死锁,很是纠结。经过排查发现是间隙锁!间隙锁是innodb中行锁的一种, 但是这种锁锁住的却不止一行数据,他锁住的是多行,是一个数据范围。间隙锁的主要作用是为了防止出现幻读,但是它会把锁定范围扩大,有时候也会给我们带来麻烦,我们就遇到了。 在数据库参数中, 控制间隙锁的参数是:innodb_locks_unsafe_for_binlog, 这个参数默认值是OFF, 也就是启用间隙锁, 他是一个bool值, 当值为true时表示disable间隙锁。那为了防止间隙锁是不是直接将innodb_locaks_unsafe_for_binlog设置为true就可以了呢? 不一定!而且这个参数会影响到主从复制及灾难恢复, 这个方法还尚待商量。
间隙锁的出现主要集中在同一个事务中先delete 后 insert的情况下, 当我们通过一个参数去删除一条记录的时候, 如果参数在数据库中存在, 那么这个时候产生的是普通行锁, 锁住这个记录, 然后删除, 然后释放锁。如果这条记录不存在,问题就来了, 数据库会扫描索引,发现这个记录不存在, 这个时候的delete语句获取到的就是一个间隙锁,然后数据库会向左扫描扫到第一个比给定参数小的值, 向右扫描扫描到第一个比给定参数大的值, 然后以此为界,构建一个区间, 锁住整个区间内的数据, 一个特别容易出现死锁的间隙锁诞生了。
举个例子:
表task_queue
Id taskId
1 2
3 9
10 20
40 41
开启一个会话: session 1
##
sql> delete from task_queue where taskId = 20;
sql> insert into task_queue values(20, 20);
在开启一个会话: session 2
##
sql> delete from task_queue where taskId = 25;
sql> insert into task_queue values(30, 25);
在没有并发,或是极少并发的情况下, 这样会可能会正常执行,在Mysql中, 事务最终都是串行执行, 但是在高并发的情况下, 执行的顺序就极有可能发生改变, 变成下面这个样子:
sql> delete from task_queue where taskId = 20;
sql> delete from task_queue where taskId = 25;
sql> insert into task_queue values(20, 20);
sql> insert into task_queue values(30, 25);
这个时候最后一条语句:insert into task_queue values(30, 25); 执行时就会报死锁错误。因为删除taskId = 20这条记录的时候,20 -- 41 都被锁住了, 他们都取得了这一个数据段的共享锁, 所以在获取这个数据段的排它锁时出现死锁。(这里需要注意数据库的共享锁和独占锁的区别:可参见http://hi.baidu.com/cuttinger/item/da2c190e99155c8a03ce1bca)
这种问题的解决办法:前面说了, 通过修改innodb_locaks_unsafe_for_binlog参数来取消间隙锁从而达到避免这种情况的死锁的方式尚待商量, 那就只有修改代码逻辑, 存在才删除,尽量不去删除不存在的记录。
相关推荐
这个项目收集了一些常见的 MySQL 死锁案例,大多数案例都来源于网络,并对其进行分类汇总,试图通过死锁日志分析出每种死锁的原因,还原出死锁现场。 实际上,我们在定位死锁问题时,不仅应该对死锁日志进行分析,...
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。 先来做几个小实验,简化的表结构如下 CREATE TABLE `t1` ( `id` int(11...
│ 9_MySQL Insert课堂练习和Update命令.mp4 │ ├─新版MySQL DBA综合实战班 第03天 │ 1_课堂作业讲解.mp4 │ 2_MySQL Delete语法讲解.mp4 │ 3_MySQL Select语法讲解.mp4 │ 4_MySQL Select多表连接讲解.mp4 │ ...
案例描述在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。两个sql语句如下:(1)insert into backup_table select * from source_table...
疫情期间在家工作时,同事使用了 insert into on duplicate key update 语句进行插入去重,但是在测试过程中发现了死锁现象: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting ...
day038-purge死锁举例_MySQL backup备份_1 day039-MySQL backup备份恢复_2 MySQL 复制技术与高可用 day040-MySQL 备份恢复backup_3_replication_1 day041-backup_4-replication_2 day042-replication_3 day043-...
• 并发事务,间隙锁可能互斥 (1)A删除不存在的记录,获取共享间隙锁; (2)B插入,必须获得排他间隙锁,故互斥; • 并发插入相同记录,可能死锁(某一个回滚) ... 可以查看InnoDB的锁情况,也可以调试死锁
1、故事起因于2016年11月15日的一个生产bug。业务场景是:归档一个表里边的数据到历史表里边,同是删除主表记录。 ... insert into test1 values('hello'); — 创建表test2 CREATE TABLE test2
一张表,里面有 ID 自增主键, 当 insert 了 17 条记录之后, 删除了第 15,16,17 条记录, 再把 Mysql 重启, 再 insert 一条记 录, 这条记录的 ID 是 18 还是 15 ? Mysql 的技术特点是什么? Mysql 服务器默认...
# 0:如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘,提交事务的时候不做任何操作(执行是由mysql的master thread线程来执行的。 # 主线程中每秒会将重做日志缓冲写入磁盘的...
前几篇文章介绍了用源码的方式来调试锁相关的信息,这里同样用这个工具来解决一个线上实际的死锁案例,也是我们介绍的第一个两条 SQL 就造成死锁的情况。因为线上的表结构比较复杂,做了一些简化以后如下 CREATE ...
1、故事起因于2016年11月15日的一个生产bug。业务场景是:归档一个表里边的数据到历史表里边,同是删除主表记录。...insert into test1 values('hello'); -- 创建表test2 CREATE TABLE test2 ( id int(1
分析代码后发现有复合主键的update情况,更新复合主键表时只使用了一个字段更新,同时在事务内又有对该表的insert操作,结果出现了偶发的死锁问题。 比如表t_lock_test中有两个主键都为primary key(a,b) ,但是更新...
问题描述 在进行高并发性能调优的时候发现了如下的一个问题: 1. 在一个事务中同时包括了SELECT,UPDATE语句 2. SELECT和UPDATE涉及到的数据为同一张表中的同一...MYSQL的默认隔离级别(可重复度)中,UPDATE,INSERT和
11.6 死锁 11.7 本章习题 第12章 异常处理 12.1 异常的概念 12.2 异常的基本样式 12.3 Java异常类 12.3.1 异常类层次结构 12.3.2 异常处理方法 12.4 异常捕获 12.4.1 异常捕获处理语句块 12.4.2 必须执行...
1. 开启一个事务执行插入数据的操作。 BEGIN TRAN t INSERT INTO Customer SELECT 'a','a' 2. 执行一条查询语句。 SELECT * FROM Customer WITH (NOLOCK) 结果中显示”a”和”a”。当1中事务回滚后,那么a将...
12_多线程-死锁问题 13_字符集问题' X4 e; v9 q' U2 W% f" l7 f$ F 14_String-StringBuffer-StringBuilder 15_集合-list-arrayList-linkedlist 16_集合-hashset-hashmap-迭代器-entryset$ d3 b$ ~5 b! @- Z* }- C 17...
5.1.5 控制insert和update语句 5.2 处理SQL引用标识符 5.3 创建命名策略 5.4 设置数据库Schema 5.5 设置类的包名 5.6 运行本章的范例程序 5.7 小结 5.8 思考题 第6章 映射对象标识符 6.1 关系...
5.1.5 控制insert和update语句 5.2 处理SQL引用标识符 5.3 创建命名策略 5.4 设置数据库Schema 5.5 设置类的包名 5.6 运行本章的范例程序 5.7 小结 5.8 思考题 第6章 映射对象标识符 6.1 关系...