insert into bak select * from test会锁表吗

答案是跟隔离级别有关系，
RR级别下会将test表所有记录上行锁，以及所有记录间隔上gap锁；对bak插入的记录加行锁；
RC级别下test表不加任何锁；对bak插入的记录加行锁；

即区别在于test表上的锁。

实验证明：
1.RR级别
会话1执行insert into bak select * from test;

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bak select * from test;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from bak;
+-----+------+------+
| id  | c    | d    |
+-----+------+------+
|   0 |    0 |    0 |
|   5 |    5 |    5 |
|  10 |   10 |   10 |
|  15 |   15 |   15 |
|  20 |   10 |   20 |
|  25 |   25 |   25 |
| 100 |  100 |  100 |
+-----+------+------+
7 rows in set (0.00 sec)

会话2执行delete from test where id=0，会话2被堵塞，检查发现是在等待行锁lock_mode X locks rec but not gap waiting

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=0;

mysql> show engine innodb status\G
...
---TRANSACTION 12940, ACTIVE 37 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 36, OS thread handle 140619987740416, query id 259 localhost sam updating
delete from test where id=0
------- TRX HAS BEEN WAITING 37 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 53 page no 3 n bits 80 index PRIMARY of table `sam`.`test` trx id 12940 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000000; asc     ;;
 1: len 6; hex 000000002e52; asc     .R;;
 2: len 7; hex c7000001bc0110; asc        ;;
 3: len 4; hex 80000000; asc     ;;
 4: len 4; hex 80000000; asc     ;;

会话2执行insert into test values (30,30,30)，会话2被堵塞，检查发现是在等待next-key锁lock_mode X locks gap before rec insert intention waiting

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=0;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into test values (30,30,30);

mysql> show engine innodb status\G
...
---TRANSACTION 12940, ACTIVE 124 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 36, OS thread handle 140619987740416, query id 263 localhost sam update
insert into test values (30,30,30)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 53 page no 3 n bits 80 index PRIMARY of table `sam`.`test` trx id 12940 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000064; asc    d;;
 1: len 6; hex 000000002e7c; asc     .|;;
 2: len 7; hex e10000016a0110; asc     j  ;;
 3: len 4; hex 80000064; asc    d;;
 4: len 4; hex 80000064; asc    d;;

会话2执行delete from bak where id=0，会话2被堵塞，检查发现是在等待行锁lock_mode X locks rec but not gap

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=0;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into test values (30,30,30);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> delete from bak where id=0;

mysql> show engine innodb status\G
...
---TRANSACTION 12940, ACTIVE 270 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 36, OS thread handle 140619987740416, query id 267 localhost sam updating
delete from bak where id=0
------- TRX HAS BEEN WAITING 32 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 3 n bits 80 index PRIMARY of table `sam`.`bak` trx id 12940 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000000; asc     ;;
 1: len 6; hex 00000000327f; asc     2 ;;
 2: len 7; hex e20000018c0110; asc        ;;
 3: len 4; hex 80000000; asc     ;;
 4: len 4; hex 80000000; asc     ;;

会话2执行delete from bak where id=1，执行成功，证明bak上面没有gap锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=0;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into test values (30,30,30);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> delete from bak where id=0;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> delete from bak where id=1;
Query OK, 0 rows affected (0.00 sec)

2.RC级别
会话1执行insert into bak select * from test;

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bak select * from test;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from bak;
+-----+------+------+
| id  | c    | d    |
+-----+------+------+
|   0 |    0 |    0 |
|   5 |    5 |    5 |
|  10 |   10 |   10 |
|  15 |   15 |   15 |
|  20 |   10 |   20 |
|  25 |   25 |   25 |
| 100 |  100 |  100 |
+-----+------+------+
7 rows in set (0.00 sec)

会话2执行delete from test where id=0，insert into test values (30,30,30)，均成功，证明test表上没有行锁，也没有gap锁。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=0;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (30,30,30);
Query OK, 1 row affected (0.00 sec)

会话2执行delete from bak where id=0，会话2被堵塞，这时bak表上的加锁情况和RR一样。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=0;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (30,30,30);
Query OK, 1 row affected (0.00 sec)

mysql> delete from bak where id=0;

^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

为什么在RC和RR级别下，加锁的情况不一样？
可以这么理解：
由于insert into bak select * from test，select * from test是一致性快照读，
1.RR级别
那么对于RR的statement格式binlog，这个语句就会受到binlog记录顺序的影响。例如在执行insert into bak select * from test，还有一个并发事务A insert into test values …，如果此时test表没有锁，也就是说事务A可以正常执行，那么这时以下两个binlog记录顺序都会导致bak有不一样的结果：
a)先记录事务A的binlog，再记录insert into bak select * from test的binlog，即利用binlog来恢复的执行顺序是：
insert into test values …
insert into bak select * from test
b)先记录insert into bak select * from test的binlog，在记录事务A的binlog，即利用binlog来恢复的执行顺序是：
insert into bak select * from test
insert into test values …
很明显，以上两种执行顺序得到的结果是不一样的，解决办法是将test表锁住。
2.RC级别
为什么RC级别不需要锁表呢？因为RC级别强制要求是row格式的binlog，也就可以忽略insert into bak select * from test和事务A在binlog里的顺序。