//
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