MySQL中由load data语句引起死锁的解决案例
作者:CrazyJinn 发布时间:2024-01-19 19:37:14
一个线上项目报的死锁,简要说明一下产生原因、处理方案和相关的一些点.
1、背景
这是一个类似数据分析的项目,数据完全通过LOAD DATA语句导入一个InnoDB表中。为方便描述,表结构简化为如下:
Create table tb(id int primary key auto_increment, c int not null) engine=innodb;
导入数据的语句对应为
Load data infile ‘data1.csv' into table tb;
Load data infile ‘data2.csv' into table tb;
cat Data1.csv
1 100
2 100
3 100
Cat data2.csv
10 100
11 100
12 100
产生死锁的证据是在show engine innodb status的LATEST DETECTED DEADLOCK段中看到死锁信息,简化为如下:
说明
从上面表格中看出,事务1在等待某一行的锁。而事务2持有这行的锁,但等待表的自增锁(AUTO_INC),判断为死锁,事务回滚。
这里事务1没有写出来,但是可以推断,事务1持有这个表的自增锁(否则就不是死锁了)。
2、背景知识1:AUTO_INC lock 及其选项
在InnoDB表中,若存在自增字段,则会维护一个表级别的锁,这里称为自增锁。每次插入新数据,或者update语句修改了此字段,都会需要获取这个锁
由于一个事务可能包含多个语句,而并非所有的语句都与自增字段有关,因此InnoDB作了一个特殊的处理,自增锁在一个语句结束后马上被释放。之所以说是特殊处理,是因为普通的锁,都是在事务结束后释放。
若一个表有自增字段,一个insert语句不指定该字段的值,或指定为NULL时,InnoDB会给它赋值为当前的AUTO_INCREMENT的值,然后AUTO_INCREMENT加1。
与这个自增锁相关的一个参数是innodb_autoinc_lock_mode. 默认值为1,可选为0,1,2。
我们先来看当这个值设置为0时,一个有自增字段的表,插入一行数据时的行为:
1) 申请AUTO_INC锁
2) 得到当前AUTO_INCREMNT值n,给AUTO_INCREMENT 加1
3) 执行插入操作,并将n填入新增的行对应字段中
4) 释放AUTO_INC锁
我们看到这个过程中,虽然InnoDB为了减少锁粒度,在语句执行完成就马上释放,但这锁还是太大了――它包括了插入操作的时间。这就导致了两个insert语句,实际上没办法并行。
没有这个参数之前,行为就是与设置为0相同,0这个选项就是留着兼容的。
很容易想到设置为1的时候,应该是将3) 和 4)对调。但是本文还是要讨论为0的情况,因为我们的前提是LOAD语句,而LOAD语句这类插入多行的语句中(包括insert …select …),即使设置为1也没用,会退化为0的模式。
3、背景知识2:LOAD DATA语句的主从行为
为什么插入多行的语句要即使将innodb_autoinc_lock_mode设置为1,也会用0的模式呢?
主要原因还是为了主从一致性。设想binlog_format='statement',一个LOAD DATA语句在主库的binlog直接记录为语句本身,那从库如何重放:
1) 将load data用到的文件发给slave,slave将文件保存在临时目录。
2) 在slave也执行一次LOAD DATA语句。
其间有一个问题:slave怎么保证load data语句的自增id字段与master相同?
为了解决这个问题,主库的binlog中还有一个set SET INSERT_ID命令,表明这个LOAD DATA语句插入的第一行的自增ID值。这样slave在执行load data之前,先执行了这个set SET INSERT_ID语句,用于保证执行结果与主库一模一样。
上述的机制能保证主从数据一致的前提是:主从库上LOAD DATA语句生成的自增ID值必须是连续的。
4、背景知识1+2:分析
回到前面说的模式0和1的区别,我们看到,如果AUTO_INC锁在整个语句开始之前就获取,在语句结束之后才释放,这样就能保证整个语句生成的id连续――模式0的保证。
对于1,每次拿到下一个值就释放,插入数据后,若需要再申请,则不连续。
这就是为什么,即使设置为1,对于多行操作,会退化成0。
至此我们知道这个死锁出现的原因,是这两个LOAD DATA语句不仅会访问相同的记录,还会访问同一个AUTO_INC锁,造成互相等待。
到此没完,因为我们知道虽然两个线程访问两个锁可能造成死锁,但是死锁还有另外一个条件,与申请顺序有关。既然AUTO_INC是一个表锁,不论谁先拿到,会阻塞其他同表的LOAD DATA的执行,又为什么会在某个记录上出现锁等待?
5、背景知识3:AUTO_INC的加锁时机
前面我们说到每次涉及到插入新数据,就会要求对AUTO_INC加锁,并列出了流程。但这个流程是对于需要从InnoDB中得到自增值来设置列值的情况。另一种情况是在语句中已经指定了该列的值。
比如对于这个表,执行 insert into tb values(9,100). 此时id的值已经明确是9,虽然不需要取值来填,但是插入这行后有可能需要改变AUTO_INCREMENT的值(若原来是<10,则应该改为10),所以这个锁还是省不了。流程变成:
1) 插入数据
2) 若失败则流程结束
3) 若成功,申请AUTO_INC锁
4) 调用set_max….函数,如有必要则修改AUTO_INCREMENT
5) 语句结束时释放AUTO_INC锁。
6、为什么修改AUTO_INC顺序
这么调整的好处是什么? 主要是为了减少不必要的锁访问。若在插入数据期间发生错误,比如其他字段造成DUPLICATE KEY error,这样就不用访问AUTO_INC锁。
7、死锁过程复现
必须强调是“语句结束时”。这样我们来看一个每行都已经指定了自增列值的LOAD DATA语句的流程(也就是本文例子的情况):
1) 插入第一条数据
2) 申请AUTO_INC锁
3) 插入第二条
4) 申请AUTO_INC 锁(因为已经是自己的,直接成功)
5) 。。。。。。插入剩余所有行
6) 释放AUTO_INC锁。
所以这个流程就简单描述为:插入第一行,申请AUTO_INC锁,然后插入剩下的所有行后再释放。
我们前面提到过,插入第一条数据时可能需要访问的记录锁,是要等到整个事务结束后才释放的.
有了上面的这些背景知识,我们来复现一下死锁出现的过程
可以看到触发条件还是比较苛刻的,尤其是session2要刚好要用到session1锁住的那个记录锁。需要说明,由于InnoDB内部对记录的表示,同一个记录锁并不表示主键值一定相同。
8、解决方案1:去掉不必要的AUTO_INCREMENT字段
在这个业务中,由于所有的数据都是通过LOAD DATA进去,而且都已经指定了自增字段的值,因此这个AUTO)INCREMENT属性是不需要的。
少了一个,就死锁不了了。
9、解决方案2:强制模式1
前面我们说到innodb_autoinc_lock_mode这个参数的可选值有0、1、2。当设置为1的时候,在LOAD DATA语句会退化为模式0。但若设置为2,则无论如何都会使用模式1。
我们前面说到使用模式1会导致LOAD DATA生成的自增id值不连续,这样会导致在binlog_format是1时主从不一致,因此设置为2的前提,是binlog_format 是row.
在binlog_format='row'时,设置innodb_autoinc_lock_mode为2是安全的。
若允许,方案2比方案1更轻量些,不需要修改数据和表结构。
猜你喜欢
- 有在论坛上看到一帖,《请教查询出连续日期记录的方法》,截图如下:Insus.NET尝试写了程序并做了测试,可以得到预期的结果,SQL代码可参
- 基本介绍约束用于确保数据库的数据满足特定的商业规则在mysql中,约束包括:not null,unique,primary key,fore
- 这是我以前发表在经典论坛的帖子,现在转贴回来。仿淘宝网站的导航效果。此方法有几个优点:根据字数自适应项目长度不同的项目使用不同的颜色来区分无
- inet_pton是一个IP地址转换函数,可以在将IP地址在“点分十进制”和“二进制整数”之间转换,而且inet_pton和inet_nto
- 一、Array数组的更改array数组中修改的API示例如下://创建大量相同元素的数组//创建有10个String类型元素的数组,并且每个
- 删除字符串中不需要的内容1、strip()方法strip:默认是去掉首尾的空白字符,但是也可以指定其他字符;lstrip:只去掉左边的;rs
- 就在刚才,我给GMAIL安装了Twitter插件。安装完插件后,Gmail提示我要连接Twitter的账号才能使用:我顺着提示操作下去,就看
- 内涵:正则匹配,正则替换,页面抓取,图片保存 。实用的第一次 Python 代码 参考#!/usr/bin/env pythonimport
- 1、 isalnum() :判断字符串所有的字符都是字母或者数字。返回true和falseIn [1]: str1='jiangwe
- tensorflow官方提供了3种方法来读取数据:预加载数据(preloaded data):在TensorFlow图中定义常量或变量来保存
- ImageDataGenerator位于keras.preprocessing.image模块当中,可用于做数据增强,或者仅仅用于一个批次一
- API说明调用地址:http://yzxyzm.market.alicloudapi.com/yzx/verifySms请求方式:POST返
- [Hack] 意为”劈”、”砍”。 [Hacker] 意为”黑客”CSS Hack 是指针对不同的浏览器写不同的CSS code的过程,简单
- Rand()函数是系统自带的获取随机数的函数,可以直接运行select rand() 获取0~1之间的float型的数字。如果想要获取0~1
- FORMAT() 函数用于对字段的显示进行格式化。SQL FORMAT() 语法SELECT FORMAT(column_name,form
- 由于工作需要,在做最优分箱的时候,始终写不出来高效的代码,所以就找到了R语言中的最优分箱的包,这个时候考虑到了在python中调用R语言,完
- 前言动态生成表格是前端开发中非常重要的内容,常常是后端返回数据(大多是json类型),我们前端通过js循环来动态添加,所以这部分内容是十分重
- 本文研究的主要是Python编程通过pandas将数据分割成时间跨度相等的数据块的相关内容,具体如下。先上数据,有如下dataframe格式
- 目录1.任务要求2.简单设计3.模块实现4.总结由于一些小原因,被迫开始了tkinter一次实战演练。在此做一些记录,总结以及给自己留一些轮
- 本文实例讲述了Python创建xml的方法。分享给大家供大家参考。具体实现方法如下:from xml.dom.minidom import