一、前言:
1.1、演示环境
centos7系统安装 GA版 mysql8.4.3 LTS版 二进制安装
insert into t2 select * from t1;
这条语句会对查询表 t1 加锁吗？ 对mysql8.4.3的锁进行研究之前，首先要确认一下事务的隔离级别，不同的事务隔离级别，锁的表现是不一样的。
1.2、创建测试数据
创建测试表t1和t2：
create table t1(id int primary key,c1 varchar(10),c2 datetime,key idx_c1(c1));
#id 列为主键，c1列上有普通索引
create table t2 like t1;
创建存储过程并向t1表插入测试数据:（具体SQL如下）
delimiter //
DROP PROCEDURE IF EXISTS p1//
CREATE 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 ;
call p1 ;
具体执行过程如下:
root@master-db01 11:23:47 [db01]> delimiter //
root@master-db01 11:23:50 [db01]> DROP PROCEDURE IF EXISTS p1//
Query OK, 0 rows affected (0.01 sec)
root@master-db01 11:23:50 [db01]> CREATE 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;
-> //
Query OK, 0 rows affected (0.00 sec)
root@master-db01 11:23:50 [db01]> delimiter ;
root@master-db01 11:23:50 [db01]> call p1 ;
Query OK, 1 row affected (0.01 sec)
root@master-db01 11:23:50 [db01]> select * from t1;
+----+------+---------------------+
| id | c1 | c2 |
+----+------+---------------------+
| 0 | 4232 | 2025-02-07 23:23:50 |
| 2 | 5730 | 2025-02-07 23:23:50 |
| 4 | 5952 | 2025-02-07 23:23:50 |
| 6 | 2569 | 2025-02-07 23:23:50 |
| 8 | 4992 | 2025-02-07 23:23:50 |
+----+------+---------------------+
5 rows in set (0.00 sec)
二、REPEATABLE-READ隔离级别
查询当前事务隔离级别：
show variables like 'transaction_isolation';
root@master-db01 09:49:06 [db01]> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
连接1：
select ps_current_thread_id ();
root@master-db01 11:24:04 [db01]> select ps_current_thread_id ();
+-------------------------+
| ps_current_thread_id () |
+-------------------------+
| 60 |
+-------------------------+
1 row in set (0.00 sec)
root@master-db01 11:30:05 [db01]> begin;
Query OK, 0 rows affected (0.00 sec)
root@master-db01 11:31:14 [db01]> use db01
Database changed
root@master-db01 11:31:45 [db01]> insert into t2 select * from t1;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
连接2：
root@master-db01 11:33:09 [(none)]> select ps_current_thread_id ();
+-------------------------+
| ps_current_thread_id () |
+-------------------------+
| 62 |
+-------------------------+
1 row in set (0.00 sec)
root@master-db01 11:34:21 [(none)]> begin;
Query OK, 0 rows affected (0.00 sec)
root@master-db01 11:34:43 [(none)]> use db01
Database changed
root@master-db01 11:34:49 [db01]> insert into t1(id,c1)values(1,'a');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@master-db01 11:35:21 [db01]>
##向t1表中插入一条表中不存在的数据时会被阻塞
上面的ERROR 1205报错 有下面的lock_wait_timeput 超时参数决定：
root@master-db01 11:35:21 [db01]> show variables like "innodb_lock_wait_timeout";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 10 |
+--------------------------+-------+
1 row in set (0.00 sec)
连接3：
root@master-db01 11:35:21 [(none)]> select ps_current_thread_id ();
+-------------------------+
| ps_current_thread_id () |
+-------------------------+
| 63 |
+-------------------------+
1 row in set (0.00 sec)
root@master-db01 11:39:59 [(none)]> select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
| 60 | t1 | NULL | TABLE | IS | GRANTED | NULL |
| 62 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 60 | t2 | NULL | TABLE | IX | GRANTED | NULL |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | supremum pseudo-record |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 0 |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 2 |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 4 |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 6 |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 8 |
| 62 | t1 | PRIMARY | RECORD | X,GAP,INSERT_INTENTION | WAITING | 2 |
+-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
10 rows in set (0.01 sec)
当连接2锁等待超时释放锁后，连接3再次执行查看锁情况时如下:
root@master-db01 11:40:29 [(none)]> select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| 60 | t1 | NULL | TABLE | IS | GRANTED | NULL |
| 60 | t2 | NULL | TABLE | IX | GRANTED | NULL |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | supremum pseudo-record |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 0 |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 2 |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 4 |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 6 |
| 60 | t1 | PRIMARY | RECORD | S | GRANTED | 8 |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
8 rows in set (0.00 sec)
测试结果说明:
连接1的语句中select的表t1上每条记录及最大伪记录supremum pseudo-record都加了S锁，这个S锁是nextkey lock锁，当连接2试图向t1表中插入一条表中不存在的数据时也会被阻塞，连接1的S锁与连接2需要的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隔离级别
查看当前事务的隔离级别: root@master-db01 11:50:09 [db01]> show variables like 'transaction_isolation'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.00 sec)
连接一：
root@master-db01 11:50:09 [db01]> select ps_current_thread_id ();
+-------------------------+
| ps_current_thread_id () |
+-------------------------+
| 58 |
+-------------------------+
1 row in set (0.00 sec)
root@master-db01 11:50:19 [db01]> begin
-> ;
Query OK, 0 rows affected (0.00 sec)
root@master-db01 11:51:59 [db01]> insert into t2 select * from t1;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
** 连接二：**
root@master-db01 11:53:32 [db01]> select ps_current_thread_id ();
+-------------------------+
| ps_current_thread_id () |
+-------------------------+
| 59 |
+-------------------------+
1 row in set (0.00 sec)
root@master-db01 11:53:33 [db01]> begin;
Query OK, 0 rows affected (0.00 sec)
root@master-db01 11:53:40 [db01]> insert into t1(id,c1)values(1,'a');
Query OK, 1 row affected (0.00 sec)
root@master-db01 11:53:48 [db01]> select * from t1;
+----+------+---------------------+
| id | c1 | c2 |
+----+------+---------------------+
| 0 | 4232 | 2025-02-07 23:23:50 |
| 1 | a | NULL |
| 2 | 5730 | 2025-02-07 23:23:50 |
| 4 | 5952 | 2025-02-07 23:23:50 |
| 6 | 2569 | 2025-02-07 23:23:50 |
| 8 | 4992 | 2025-02-07 23:23:50 |
+----+------+---------------------+
6 rows in set (0.00 sec)
连接三：
root@master-db01 11:55:19 [(none)]> select ps_current_thread_id ();
+-------------------------+
| ps_current_thread_id () |
+-------------------------+
| 60 |
+-------------------------+
1 row in set (0.00 sec)
root@master-db01 11:55:20 [(none)]>
root@master-db01 11:55:21 [(none)]>
root@master-db01 11:55:21 [(none)]> select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
| 59 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 58 | t2 | NULL | TABLE | IX | GRANTED | NULL |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)
测试说明: 可以看出事务隔离级别设置为READ-COMMITTED后，表现截然不同。连接二 并没有被阻塞，两个会话持有的锁都只有插入表意向排他锁(IX)。
四、测试结论
INSERT...SELECT语句是否对查询表加锁跟事务隔离级别有关，REPEATABLE-READ隔离级别下加共享读锁，此共享读锁属于Nextkey lock，会影响其他事务对查询表的DML操作；READ-COMMITTED下不加锁，不影响其他事务对表进行DML操作。
