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

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

1、背景

这是一个类似数据分析的项目，数据完全通过LOAD DATA语句导入一个InnoDB表中。为方便描述，表结构简化为如下：

导入数据的语句对应为

产生死锁的证据是在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语句的主从行为