INSERT...SELECT语句对查询的表加锁吗-51CTO.COM

前言

insert into t2 select * from t1; 这条语句会对查询表 t1 加锁吗？不要轻易下结论。对GreatSQL的锁进行研究之前，首先要确认一下事务的隔离级别，不同的事务隔离级别，锁的表现是不一样的。

实验

创建测试表t1，t2

greatsql> create table t1(id int primary key ,c1 varchar(10),c2 datetime,key idx_c1(c1));
greatsql> create table t2 like t1;

# id 列为主键，c1列上有普通索引

创建存储过程，向t1表插入测试数据

greatsql> delimiter //
CREATE or replace PROCEDURE p1()
BEGIN
DECLARE p1 int default 0;
while p1<5 do
insert into t1(id,c1,c2) values(p1*2,round(rand()*10000),now());
SET p1 = p1 + 1;
end while;
END;
//
delimiter ;

greatsql> call p1;

greatsql> select * from t1;
+----+------+---------------------+
| id | c1   | c2                  |
+----+------+---------------------+
|  0 | 2660 | 2024-02-21 15:45:00 |
|  2 | 4627 | 2024-02-21 15:45:00 |
|  4 | 5158 | 2024-02-21 15:45:00 |
|  6 | 1907 | 2024-02-21 15:45:00 |
|  8 | 4061 | 2024-02-21 15:45:00 |
+----+------+---------------------+
5 rows in set (0.01 sec)

REPEATABLE-READ隔离级别

查询当前事务隔离级别：

greatsql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

connection 1：

greatsql> select ps_current_thread_id();
+------------------------+
| ps_current_thread_id() |
+------------------------+
|                     92 |
+------------------------+
1 row in set (0.00 sec)

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

greatsql> insert into t2 select * from t1;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

connection2：

greatsql> select ps_current_thread_id();
+------------------------+
| ps_current_thread_id() |
+------------------------+
|                     93 |
+------------------------+
1 row in set (0.00 sec)

greatsql> begin;
Query OK, 0 rows affected (0.01 sec)

greatsql> insert into t1(id,c1) values(1,'a');

connection3：

greatsql> select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
+-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE              | LOCK_STATUS | LOCK_DATA              |
+-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
|        93 | t1          | NULL       | TABLE     | IX                     | GRANTED     | NULL                   |
|        93 | t1          | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 2                      |
|        92 | t2          | NULL       | TABLE     | IX                     | GRANTED     | NULL                   |
|        92 | t1          | NULL       | TABLE     | IS                     | GRANTED     | NULL                   |
|        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | supremum pseudo-record |
|        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 0                      |
|        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 2                      |
|        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 4                      |
|        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 6                      |
|        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 8                      |
+-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
10 rows in set (0.00 sec)

connection1的语句中select的表t1上每条记录及最大伪记录supremum pseudo-record都加了S锁，这个S锁是nextkey lock锁，当connection2试图向t1表中插入一条表中不存在的数据时也会被阻塞，connect1的S锁与connect2需要的 X,GAP,INSERT_INTENTION锁不兼容。

在 REPEATABLE-READ 隔离级别下，INSERT ... SELECT 操作并未采用MVCC来保证事务一致性和隔离性，而是使用了锁机制。

加锁的目的是确保事务在读取数据时能够看到一个一致的数据快照。如果在执行 INSERT ... SELECT 时不加锁，那么可能会出现以下情况：

不可重复读：如果在 INSERT ... SELECT 执行期间，另一个事务修改了被查询的数据，那么 INSERT ... SELECT 可能会读取到不同的数据，导致插入的数据不一致。
幻读：在某些情况下，另一个事务可能会在 INSERT ... SELECT 执行期间插入新的行，导致插入操作插入到不应该插入的行。

通过加锁，InnoDB 能够确保 INSERT ... SELECT 语句在执行期间读取到的数据是一致的，并且不会被其他事务修改，从而维护了事务的隔离性和一致性。尽管 MVCC 可以在大多数情况下提供高效的数据读取和写入，但它并不能完全替代锁机制。在 INSERT ... SELECT 这样的操作中，使用 MVCC 可能无法提供足够的保证。

READ-COMMITTED隔离级别

查询当前事务隔离级别：

greatsql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

connection 1

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

greatsql> insert into t2 select * from t1;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

connection 2

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

greatsql> insert into t1(id,c1) values(1,'a');
Query OK, 1 row affected (0.00 sec)

connection3

greatsql> select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
|       104 | t1          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
|       103 | t2          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

可以看出事务隔离级别设置为READ-COMMITTED后，表现截然不同。connection2并没有被阻塞，两个会话持有的锁都只有插入表意向排他锁(IX)。

结论

INSERT...SELECT语句是否对查询表加锁跟事务隔离级别有关，REPEATABLE-READ隔离级别下加共享读锁，此共享读锁属于Nextkey lock，会影响其他事务对查询表的DML操作；READ-COMMITTED下不加锁，不影响其他事务对表进行DML操作。