INSERT...SELECT语句对查询的表加锁吗

前言

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列上有普通索引1.2.3.4.

创建存储过程，对查向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)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.

REPEATABLE-READ隔离级别

查询当前事务隔离级别：

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

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: 01.2.3.4.5.6.7.8.9.10.11.12.13.14.

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);1.2.3.4.5.6.7.8.9.10.11.12.

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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.

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)1.2.3.4.5.6.7.

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: 01.2.3.4.5.6.

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)1.2.3.4.5.

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)1.2.3.4.5.6.7.8.

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

结论

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

本文地址：http://www.bzuk.cn/html/03b9199905.html