insert into select from加锁顺序

1、最近遇到一个小问题，由于insert into table1 select from  table2跟其他update事务造成了死锁，于是猜想这个insert into select的加锁顺序，实验环境如下：

(1)隔离级别：RC

(2)innodb_autoinc_lock_mode:1

(3)version: 5.6.37-log

(4)测试的两个表结构一样，如下：

?

1 2 3 4 5 6 7 8 9 10 11

mysql> show create table test1; +----------+---------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------+ | test1 | CREATE TABLE `test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `num` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `phone` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=31620222 DEFAULT CHARSET=utf8mb4

2、测试sql如下

(1)session 1 ：

?

1	select * from test1  where id=9 for update;

(2)session 2：

?

1	insert IGNORE  into test2 select * from  test1 where id >2 and id < 15 lock in share mode;

(3)session  3:       

?

1	insert IGNORE into test2 select 11,'9527','零零七','18888888007';

3、测试顺序

 (1)session 1开启事务后执行sql  ------->  session 2开启事务后执行 sql ------>session 3开启事务后执行sql；

   可以发现，session 2、session 3 都是会被阻塞的，以下是锁信息：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

show engine innodb status ---TRANSACTION 1742170, ACTIVE 4 sec setting auto-inc lock mysql tables in use 1, locked 1 MySQL thread id 3284, OS thread handle 0x7f20a48a5700, query id 2919013 localhost root executing insert IGNORE into test2 select 11,'9527','零零七','18888888007' ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `test`.`test2` trx id 1742170 lock mode AUTO-INC waiting -- session 1 需要等待这个锁 ------------------ TABLE LOCK table `test`.`test2` trx id 1742170 lock mode IX TABLE LOCK table `test`.`test2` trx id 1742170 lock mode AUTO-INC waiting     ---TRANSACTION 1742169, ACTIVE 30 sec fetching rows mysql tables in use 2, locked 2 LOCK WAIT 6 lock struct(s), heap size 1184, 13 row lock(s) MySQL thread id 3279, OS thread handle 0x7f209e356700, query id 2919010 localhost root Sending data insert IGNORE  into test2 select * from  test1 where id >2 and id < 15 lock in share mode ------- TRX HAS BEEN WAITING 30 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1536 page no 4 n bits 560 index `PRIMARY` of table `test`.`test1` trx id 1742169 lock mode S locks rec but not gap waiting -- session 2需要等待这个锁   TABLE LOCK table `test`.`test2` trx id 1742169 lock mode AUTO-INC        -- session 2 持有的锁 RECORD LOCKS space id 1536 page no 4 n bits 560 index `PRIMARY` of table `test`.`test1` trx id 1742169 lock mode S locks rec but not gap waiting     ---TRANSACTION 1742168, ACTIVE 68 sec 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 3283, OS thread handle 0x7f209e51d700, query id 2919007 localhost root TABLE LOCK table `test`.`test1` trx id 1742168 lock mode IX RECORD LOCKS space id 1536 page no 4 n bits 560 index `PRIMARY` of table `test`.`test1` trx id 1742168 lock_mode X locks rec but not gap --session 3持有的锁

　从锁信息可以知道，session 3对test1表的id=9这一行加上记录锁，session 2先对test2表加上AUTO-INC锁，然后等待test1表的记录锁，当session 3想要插入一条数据的时候因为session 2加上的AUTO-INC表级锁，故无法插入。

    由此可知，insert into table1 select from table2首先需要申请table1的自增锁（表级），然后再去申请table2的记录锁。好在table1的表级锁是基于SQL的，一旦sql执行完即释放自增锁，而无需等待整个事务提交。这个加锁顺序很重要，也是下一篇产生死锁的必要条件。