insert into select加锁规则补充

AsiaYe

发布于 2020-06-11 10:14:50

1.9K1

发布于 2020-06-11 10:14:50

文章被收录于专栏：DBA随笔DBA随笔

insert into select加锁规则补充

昨天的文章中，针对insert into select语句的加锁情况进行了分析：

insert into A select * from B;

形如这样的语句，在statement模式的binlog下，会对B加记录锁和间隙锁，A上会有自增锁；而在row模式下，经过测试，B表上并不会有锁。

row格式下的测试过程如下(下面分别是执行顺序和代码)：

会话1：

----------------会话1---------------
mysql>>select * from table_log order by id desc limit 2\G
*************************** 1. row ***************************
  id: 10000000
code: 10000000
time: 2020-06-04 12:57:42
*************************** 2. row ***************************
  id: 9999999
code: 9999999
time: 2020-06-04 12:57:42

2 rows in set (0.00 sec)

mysql>>insert into table_log_new select * from table_log where time>='2020-06-04 12:30:00';



#在会话1的insert into select返回结果前执行会话2中的update，发现update并没有阻塞
#
-----------------会话2---------------
mysql>>update table_log set code=9999999000 where id=9999999; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

----------------会话1---------------
此时insert into select 返回结果
Query OK, 9096543 rows affected (1 min 39.29 sec)
Records: 9096543  Duplicates: 0  Warnings: 0

mysql>>select * from table_log order by id desc limit 5\G                                  
*************************** 1. row ***************************
  id: 10000000
code: 10000000
time: 2020-06-04 12:57:42
*************************** 2. row ***************************
  id: 9999999
code: 9999999000
time: 2020-06-04 12:57:42

可以看到，insert into select之前，id=9999999的code值是9999999，而在执行insert into select的时候，在另外一个会话里面，对这个id=9999999的code值进行更改，在row模式下并未产生阻塞。

因此可以判断：

insert into A select * from B；

这个语句的row模式下，并未对表B产生全表的记录锁。

而在statement模式下，上述语句会对表B产生记录锁和间隙锁。

关于这个语句的加锁方法，可以参看percona官网的一篇博客和stackoverflow的一篇讨论，这里给出链接，有兴趣的同学可以继续研究：

https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

http://stackoverflow.com/questions/2640898/how-to-improve-insert-into-select-locking-behavior

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-06-05，如有侵权请联系 cloudcommunity@tencent.com 删除

sql