INSERT...SELECT語句對查詢的表加鎖嗎
作者：JennyYu
INSERT...SELECT語句是否對查詢表加鎖跟事務隔離級別有關，REPEATABLE-READ隔離級別下加共享讀鎖，此共享讀鎖屬于Nextkey lock，會影響其他事務對查詢表的DML操作；READ-COMMITTED下不加鎖，不影響其他事務對表進行DML操作。
前言
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: 0connection2：
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: 0connection 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操作。
責任編輯：武曉燕
來源：
GreatSQL社區
