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)测试的两个表结构一样，如下：

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 ：

select * from test1  where id=9 for update;

(2)session 2：

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

(3)session  3:       

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

3、测试顺序

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

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

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执行完即释放自增锁，而无需等待整个事务提交。这个加锁顺序很重要，也是下一篇产生死锁的必要条件。