insert语句的加锁情况分析
//
insert语句的加锁情况分析
//
今天分享的内容是MySQL里面insert语句的加锁情况，废话就不多说了，直接从线上的例子开始吧。
今天在线上执行了一个insert into select的操作，背景是从一个9000w记录的日志表中，根据时间字段，过滤出来最近一个月的数据，将近1000w，回写到一张新的表中，时间字段上有索引。乍一看可能比较简单，执行的过程还是出现了一些问题。先看SQL执行的时间吧：
mysql>>insert into table_log select * from table_log_old where request_time>'2020-05-03 00:00:00'\G
Query OK, 13512527 rows affected (5 min 13.57 sec)
Records: 13512527 Duplicates: 0 Warnings: 0可以看到，执行了大约5分钟左右吧，执行期间，我使用show engine innodb status查看了锁的情况，部分日志输入如下：
---TRANSACTION B13519B1, ACTIVE 55 sec fetching rows
mysql tables in use 2, locked 2
258949 lock struct(s), heap size 27195832, 38047866 row lock(s)
MySQL thread id 4098573, OS thread handle 0x7f25a6c28700, query id 5388156694 127.0.0.1 dba_admin Sending data
insert into table_log select * from table_log_old where request_time>'2020-05-03 00:00:00'可以很明显的看到，已经产生了20多w的锁结构。执行的过程中，该表出现了不可插入数据的情况，也就是锁等待。下面的信息更详细的指明了，该过程中存在auto inc锁的等待。
---TRANSACTION B1354DEF, ACTIVE 109 sec setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 376, 0 row lock(s)
MySQL thread id 4019277, OS thread handle 0x7f25a4eae700, query id 5388180935 192.168.10.73 xxxx update
insert into table_log(xxxxxxx)
------- TRX HAS BEEN WAITING 109 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `xxxxxx`.`table_log` trx id B1354DEF lock mode AUTO-INC waiting为了解释这个现象，我们需要知道在MySQL中，对于insert into select这个语句是如何加锁的。
01
insert into select的加锁情况
假如我们有一个表t，它有三个字段，id，c，d，其中id是主键，c是唯一索引，d是普通列，有4条记录：
mysql> select * from t;
+----+------+------+
| id | c | d |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
+----+------+------+
4 rows in set (0.00 sec)在RR隔离级别下，binlog_format为statement，如果我们执行：
create table t2 like t;
session 1：
insert into t2 select (c,d) from t;
session 2：
insert into t value (-1,-1,-1)；
在不加锁的情况下，这两个会话如果并发，在从库上的执行顺序可能变成：
insert into t value (-1,-1,-1)；
insert into t2 select (c,d) from t;
那么此时，-1也会被写入到t2表中，主从的结果就不一样了。
因此，为了避免这种情况发生，MySQL会在session 1中对表t添加了(-无穷，1]之间的next-key锁，来避免session2的insert语句并发插入。
除此之外，因为要对表t做全表扫描，MySQL在扫描到每一条记录的时候，都会对这个记录加记录锁，并且在记录之间的间隙添加间隙锁。
当然，上面的例子是在binlog=statement的模式下进行测试的，回到我们的例子，当binlog=row的时候，我们进行
insert into table_log select * from table_log_old where xxxxx。
而参数innodb_autoinc_lock_mode设置的值是1。关于这个参数，需要再次声明一下：
在MySQL5.7中，参数innodb_autoinc_lock_mode被用来控制自增锁的模式，该参数可以设置为三个值：0、1、2.
a、当该值为0的时候，是等insert语句执行完成之后才释放自增锁；
b、当该值为1的时候，普通的insert语句，自增锁在申请之后马上释放，insert into select语句，自增锁还是要等语句结束之后才释放
c、该值为2的时候，所有的申请自增主键的动作都是申请完成之后就释放锁
之所以对insert into select语句单独处理，是因为这种语句"预先不知道要申请多少个id"，如果我们要select的表有1000w行记录，那么要做1000w次的申请自增id的动作。MySQL认为这是欠妥当的，因此，对这种批量insert语句，包括load data等，它在内部做了一个自增值生成策略的优化：
1、批量执行的insert语句，第一次申请1个自增id
2、一个id用完了，第二次申请2个自增id
3、2个id用完了，第三次申请4个自增id
而在自增主键生成的过程中，其他事务是不能向目标表中插入数据的。
解释下我们的例子，也就是：
当对表table_log进行insert操作的时候，会批量申请自增值，产生auto-inc的锁，由于MySQL不确定这个SQL会插入多少数据，而且我们设置了innodb_autoinc_lock_mode=1，所以这个锁是事务级别的，当满足select的where条件的值足够多的时候，其他事务要进行insert操作，会产生table_log表上的auto inc锁等待。也就是日志中的：
------- TRX HAS BEEN WAITING 109 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `xxxxxx`.`table_log` trx id B1354DEF lock mode AUTO-INC waiting