文档章节

故障分析 | MySQL Insert 加锁与死锁分析

爱可生
 爱可生
发布于 07/03 13:41
字数 1913
阅读 55
收藏 0

原创: 胡呈清


前  言

本文是由爱可生运维团队出品的「MySQL专栏」系列文章,内容来自于运维团队一线实战经验,涵盖MySQL各种特性的实践,优化案例,数据库架构,HA,监控等,有扫雷功效。

爱可生开源社区持续运营维护的小目标:

  • 每周至少推送一篇高质量技术文章

  • 每月研发团队发布开源组件新版

  • 每年1024开源一款企业级组件

  • 2019年至少25场社区活动

欢迎大家持续关注~

 

在我们尝试回答这个问题前,一定要注意前提条件,如果你看过登博的《MySQL 加锁处理》,一定知道前提不同答案也就不同,如果还没看过建议你去看一下,链接:

http://hedengcheng.com/?p=771

 

那么这个问题缺少哪些前提条件?

  • 1. c2 字段建有唯一索引

  • 2. 隔离级别为:READ-COMMITTED

其实网络上有类似的案例分析,其中丁奇老师在《MySQL实战45讲》中的第40篇《insert语句的锁为什么这么多?》中有一样的例子和分析,但是我的理解有些微差异,所以来说说我个人的看法,如果有不对的地方请大家指正。首先我会分析一下这个场景的加锁情况和死锁原因,然后对于差异的点进行展开,最后总结 insert 的加锁情况(关于 insert 的加锁行为,其实不像 delete 那样简单清晰,里面有一些需要注意的点)。

 

加锁情况与死锁原因分析

为方便大家复现,完整表结构和数据如下:

CREATE TABLE `t3` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1 执行 commit 的瞬间,我们会看到 session2、session3 的其中一个报死锁。这个死锁是这样产生的:

  • **1. **session1 执行 delete  会在唯一索引 c2 的 c2 = 15 这一记录上加 X lock(也就是在MySQL 内部观测到的:X Lock but not gap);

  • **2. **session2 和 session3 在执行 insert 的时候,由于唯一约束检测发生唯一冲突,会加 S Next-Key Lock,即对 (1,15] 这个区间加锁包括间隙,并且被 seesion1 的 X Lock 阻塞,进入等待;

  • **3. **session1 在执行 commit 后,会释放 X Lock,session2 和 session3 都获得 S Next-Key Lock;

  • 4. session2 和 session3 继续执行插入操作,这个时候 INSERT INTENTION LOCK(插入意向锁)出现了,并且由于插入意向锁会被 gap 锁阻塞,所以 session2 和 session3 互相等待,造成死锁。

死锁日志如下:

 

INSERT INTENTION LOCK

在之前的死锁分析第四点,如果不分析插入意向锁,也是会造成死锁的,因为插入最终还是要对记录加 X Lock 的,session2 和 session3 还是会互相阻塞互相等待。

但是插入意向锁是客观存在的,我们可以在官方手册中查到,不可忽略:

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入意向锁其实是一种特殊的 gap lock,但是它不会阻塞其他锁。假设存在值为 4 和 7 的索引记录,尝试插入值 5 和 6 的两个事务在获取插入行上的排它锁之前使用插入意向锁锁定间隙,即在(4,7)上加 gap lock,但是这两个事务不会互相冲突等待。

当插入一条记录时,会去检查当前插入位置的下一条记录上是否存在锁对象,如果下一条记录上存在锁对象,就需要判断该锁对象是否锁住了 gap。如果 gap 被锁住了,则插入意向锁与之冲突,进入等待状态(插入意向锁之间并不互斥)。总结一下这把锁的属性:

  • 1. 它不会阻塞其他任何锁;

  • 2. 它本身仅会被 gap lock 阻塞。

在学习 MySQL 过程中,一般只有在它被阻塞的时候才能观察到,所以这也是它常常被忽略的原因吧...

 

GAP LOCK

在此例中,另外一个重要的点就是 gap lock,通常情况下我们说到 gap lock 都只会联想到 REPEATABLE-READ 隔离级别利用其解决幻读。但实际上在 READ-COMMITTED 隔离级别,也会存在 gap lock ,只发生在:唯一约束检查到有唯一冲突的时候,会加 S Next-key Lock,即对记录以及与和上一条记录之间的间隙加共享锁。

通过下面这个例子就能验证:

 

这里 session1 插入数据遇到唯一冲突,虽然报错,但是对 (15,20] 加的 S Next-Key Lock 并不会马上释放,所以 session2 被阻塞。另外一种情况就是本文开始的例子,当 session2 插入遇到唯一冲突但是因为被 X Lock 阻塞,并不会立刻报错 “Duplicate key”,但是依然要等待获取 S Next-Key Lock 。

有个困惑很久的疑问:出现唯一冲突需要加 S Next-Key Lock 是事实,但是加锁的意义是什么?还是说是通过 S Next-Key Lock 来实现的唯一约束检查,但是这样意味着在插入没有遇到唯一冲突的时候,这个锁会立刻释放,这不符合二阶段锁原则。这点希望能与大家一起讨论得到好的解释。

如果是在 REPEATABLE-READ,除以上所说的唯一约束冲突外,gap lock 的存在是这样的:

普通索引(非唯一索引)的S/X Lock,都带 gap 属性,会锁住记录以及前1条记录到后1条记录的左闭右开区间,比如有[4,6,8]记录,delete 6,则会锁住[4,8)整个区间

对于 gap lock,相信 DBA 们的心情是一样一样的,所以我的建议是

  • 1. 在绝大部分的业务场景下,都可以把 MySQL 的隔离界别设置为 READ-COMMITTED;

  • 2. 在业务方便控制字段值唯一的情况下,尽量减少表中唯一索引的数量。

 

锁冲突矩阵

前面我们说的 GAP LOCK 其实是锁的属性,另外我们知道 InnoDB 常规锁模式有:S 和 X,即共享锁和排他锁。锁模式和锁属性是可以随意组合的,组合之后的冲突矩阵如下,这对我们分析死锁很有帮助:

 

INSERT 加锁总结

无 Unique Key:X Lock but not gap

有 Unique Key:

  • 唯一性约束检查发生冲突时,会加 S Lock,带 gap 属性,会锁住记录以及与前1条记录之前的间隙;

  • 如果插入的位置有带 gap 属性的 S/X Lock,则插入意向锁(LOCK_INSERT_INTENTION)被阻塞,进入等待状态;

  • 如果新数据顺利插入,最后对记录加 X Lock but not gap。

select、delete 加锁行为是很简单的,刚我们看了 insert 的加锁稍有点复杂,那么 update 是怎么加锁的呢?或者更复杂一点的 replace into 呢?欢迎大家一起讨论。

© 著作权归作者所有

爱可生
粉丝 11
博文 133
码字总数 251218
作品 1
徐汇
私信 提问
mysql并发insert死锁问题——gap、插入意向锁冲突

问题描述 线上出现MySQL死锁报警,通过show engine innodb status命令查看死锁日志,结合异常代码,还原发生死锁的事务场景如下: 环境: mysql5.7,事务隔离级别REPEATABLE-READ 表结构 并发...

hebaodan
2018/06/26
6.3K
1
记录一次Mysql死锁排查过程

背景 以前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调...

wooyoo
2017/02/19
0
0
记录一次 Mysql 死锁排查过程

背景 以前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调...

wooyoo
2017/02/19
0
0
数据库开发 - 事务 死锁分析与解决

死锁分析与解决 事务并发执行 事务持锁 MySQL数据库是以行加锁的方式,避免不同事务,对同一行数据库进行同时修改的。首先来看事务一,对张三这条记录的Account字段进行修改,需要持有张三这...

抢小孩糖吃
2016/10/08
105
0
MySQL 加锁处理分析

MySQL/InnoDB的加锁分析,一直是一个比较困难的话题。我在工作过程中,经常会有同事咨询这方面的问题。同时,微博上也经常会收到MySQL锁相关的私信,让我帮助解决一些死锁的问题。本文,准备...

wangergui
2017/03/11
0
0

没有更多内容

加载失败,请刷新页面

加载更多

IntelliJ IDEA远程连接tomcat,实现单步调试

web项目部署到tomcat上之后,有时需要打断点单步调试,如果用的是Intellij idea,可以通过如下方法实现: 开启debug端口,启动tomcat 以tomcat7.0.75为例,打开bin目录下的catalina.bat文件,...

程序员欣宸
46分钟前
5
0
如何在 Bash 中对变量递增或者递减

导读 编写 Bash 脚本时最常见的算术运算之一是递增和递减变量。这通常在循环中用作计数器,但它也可以在脚本的其他地方出现。 递增和递减意味着分别从数值变量的值中添加或减去值(通常为 1 ...

问题终结者
51分钟前
3
0
如何使用ssh工具便于远程管理

前几天亲眼经历了Linux服务器运维过程,眼看着别人熟练运用Linux管理工具,自个心里不是滋味,所以自己特意整理了一篇“专题”:使用ssh服务远程管理主机。 首先在使用ssh服务工具之前,先熟...

Linux就该这么学
59分钟前
6
0
IT兄弟连 HTML5教程 介绍HTML5给你认识 习题

1.关于HTML5说法正确的是:(C) A.HTML5只是对HTML4的一个简单升级 B.所有主流浏览器都支持HTML5 C.HTML5新增了离线缓存机制 D.HTML5主要是针对移动端进行了优化 2.为了标识一个HTML文...

老码农的一亩三分地
今天
6
0
关于1加手机rom的分析过程

1、关于清理app缓存的信息 framework.jar和services.jar文件,都位于/system/framework目录下面 framework.jar的android.os.Intent类里面定义了action "android.intent.action.CLEAR_PKG",这......

shatian
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部