MySQL中由load data语句引起死锁的解决案例

一个线上项目报的死锁,简要说明一下产生原因、处理方案和相关的一些点.

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更轻量些,不需要修改数据和表结构。

(0)

相关推荐

  • Mysql 数据库死锁过程分析(select for update)

    近期有一个业务需求,多台机器需要同时从Mysql一个表里查询数据并做后续业务逻辑,为了防止多台机器同时拿到一样的数据,每台机器需要在获取时锁住获取数据的数据段,保证多台机器不拿到相同的数据. 我们Mysql的存储引擎是innodb,支持行锁.解决同时拿数据的方法有很多,为了更加简单,不增加其他表和服务的情况下,我们考虑采用select... for update的方式,这样X锁锁住查询的数据段,表里其他数据没有锁,其他业务逻辑还是可以操作. 这样一台服务器比如select .. for upda

  • MySQL redo死锁问题排查及解决过程分析

    问题背景 周一上班,首先向同事了解了一下上周的测试情况,被告知在多实例场景下 MySQL Server hang 住,无法测试下去,原生版本不存在这个问题,而新版本上出现了这个问题,不禁心头一颤,心中不禁感到奇怪,还好现场环境还在,为排查问题提供了一个好的环境,随即便投入到紧张的问题排查过程当中.问题实例表现如下: 复制代码 代码如下: 并发量为 384 的时候出现的问题: MySQL 服务器无法执行事务相关的语句,即使简单的 select 语句也无法执行: 所有线程处于等待状态,无法 KILL

  • MySQL死锁问题分析及解决方法实例详解

    MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁及解决方法详解如下: 1.MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 2.各种锁特点 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低 行级锁:开销大,加锁慢;

  • 一次Mysql死锁排查过程的全纪录

    前言 之前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁.借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景.在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多.虽然是后端程序员,我们不需要像DBA一样深入地去分析与锁相关的源码,但是如果我们能够掌握基本的死锁排查方法,对我们的日常开发还是大有裨益的. PS:本文不会介绍死锁的基本知识,mysql的加锁原理可以参考本文的参考资料提供的链接. 死锁起因 先介绍一下数

  • Mysql 行级锁的使用及死锁的预防方案

    一.前言 mysql的InnoDB,支持事务和行级锁,可以使用行锁来处理用户提现等业务.使用mysql锁的时候有时候会出现死锁,要做好死锁的预防. 二.MySQL行级锁 行级锁又分共享锁和排他锁. 共享锁: 名词解释:共享锁又叫做读锁,所有的事务只能对其进行读操作不能写操作,加上共享锁后其他事务不能再加排他锁了只能加行级锁. 用法: SELECT `id` FROM table WHERE id in(1,2) LOCK IN SHARE MODE 结果集的数据都会加共享锁 排他锁: 名词解释:

  • MySQL数据库的一次死锁实例分析

    1.故事起因于2016年11月15日的一个生产bug.业务场景是:归档一个表里边的数据到历史表里边,同是删除主表记录. 2.背景场景简化如下(数据库引擎InnoDb,数据隔离级别RR[REPEATABLE]) -- 创建表test1 CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) ); insert into test1 values('hel

  • MySQL Innodb表导致死锁日志情况分析与归纳

    案例描述在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志.两个sql语句如下:(1)insert into backup_table select * from source_table(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'teamUser表的表结构如下:PRIMARY

  • mysql 数据库死锁原因及解决办法

    死锁(Deadlock) 所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力协助下,永远分配不到必需的资源而无法继续运行,这就产生了一种特殊现象死锁. 一种情形,此时执行程序中两个或多个线程发生永久堵塞(等待),每个线程都在等待被其他线程占用并堵塞了的资源.例如,如果线程A锁住了记

  • 详解MySQL中的死锁情况以及对死锁的处理方法

    当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁.死锁发生在事务试图以不同的顺序锁定资源.以StockPrice表上的两个事务为例: 事务1 START TRANSACTION; UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01'; UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '20

  • 查找MySQL线程中死锁的ID的方法

    如果遇到死锁了,怎么解决呢?找到原始的锁ID,然后KILL掉一直持有的那个线程就可以了, 但是众多线程,可怎么找到引起死锁的线程ID呢? MySQL 发展到现在,已经非常强大了,这个问题很好解决. 直接从数据字典连查找. 我们来演示下. 线程A,我们用来锁定某些记录,假设这个线程一直没提交,或者忘掉提交了. 那么就一直存在,但是数据里面显示的只是SLEEP状态. mysql> set @@autocommit=0; Query OK, 0 rows affected (0.00 sec) mys

随机推荐