前言：
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()BEGINDECLARE p1 int default 0;while p1<5 doinsert 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 操作。
评论