MySQL中由loaddata语句引起死锁的解决案例
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL中由loaddata语句引起死锁的解决案例，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5125字，纯文字阅读大概需要8分钟。
内容图文
一个线上项目报的死锁，简要说明一下产生原因、处理方案和相关的一些点.
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;
产生死锁的证据是在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更轻量些，不需要修改数据和表结构。
内容总结
以上是互联网集市为您收集整理的MySQL中由loaddata语句引起死锁的解决案例全部内容，希望文章能够帮你解决MySQL中由loaddata语句引起死锁的解决案例所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。