insert into bak select * from test会锁表吗

叁歲伎倆 2023-05-22 06:56 6阅读 0赞

答案是跟隔离级别有关系，  
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> 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> 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;

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

会话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里的顺序。