INSERT...SELECT語(yǔ)句對(duì)查詢的表加鎖嗎
前言
insert into t2 select * from t1; 這條語(yǔ)句會(huì)對(duì)查詢表 t1 加鎖嗎？不要輕易下結(jié)論。對(duì)GreatSQL的鎖進(jìn)行研究之前，首先要確認(rèn)一下事務(wù)的隔離級(jí)別，不同的事務(wù)隔離級(jí)別，鎖的表現(xiàn)是不一樣的。
實(shí)驗(yàn)
創(chuàng)建測(cè)試表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列上有普通索引創(chuàng)建存儲(chǔ)過(guò)程，向t1表插入測(cè)試數(shù)據(jù)
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隔離級(jí)別
查詢當(dāng)前事務(wù)隔離級(jí)別：
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的語(yǔ)句中select的表t1上每條記錄及最大偽記錄supremum pseudo-record都加了S鎖，這個(gè)S鎖是nextkey lock鎖，當(dāng)connection2試圖向t1表中插入一條表中不存在的數(shù)據(jù)時(shí)也會(huì)被阻塞，connect1的S鎖與connect2需要的 X,GAP,INSERT_INTENTION鎖不兼容。
在 REPEATABLE-READ 隔離級(jí)別下，INSERT ... SELECT 操作并未采用MVCC來(lái)保證事務(wù)一致性和隔離性，而是使用了鎖機(jī)制。
加鎖的目的是確保事務(wù)在讀取數(shù)據(jù)時(shí)能夠看到一個(gè)一致的數(shù)據(jù)快照。如果在執(zhí)行 INSERT ... SELECT 時(shí)不加鎖，那么可能會(huì)出現(xiàn)以下情況：
- 不可重復(fù)讀：如果在 INSERT ... SELECT 執(zhí)行期間，另一個(gè)事務(wù)修改了被查詢的數(shù)據(jù)，那么 INSERT ... SELECT 可能會(huì)讀取到不同的數(shù)據(jù)，導(dǎo)致插入的數(shù)據(jù)不一致。
- 幻讀：在某些情況下，另一個(gè)事務(wù)可能會(huì)在 INSERT ... SELECT 執(zhí)行期間插入新的行，導(dǎo)致插入操作插入到不應(yīng)該插入的行。
通過(guò)加鎖，InnoDB 能夠確保 INSERT ... SELECT 語(yǔ)句在執(zhí)行期間讀取到的數(shù)據(jù)是一致的，并且不會(huì)被其他事務(wù)修改，從而維護(hù)了事務(wù)的隔離性和一致性。盡管 MVCC 可以在大多數(shù)情況下提供高效的數(shù)據(jù)讀取和寫入，但它并不能完全替代鎖機(jī)制。在 INSERT ... SELECT 這樣的操作中，使用 MVCC 可能無(wú)法提供足夠的保證。
READ-COMMITTED隔離級(jí)別
查詢當(dāng)前事務(wù)隔離級(jí)別：
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)可以看出事務(wù)隔離級(jí)別設(shè)置為READ-COMMITTED后，表現(xiàn)截然不同。connection2并沒(méi)有被阻塞，兩個(gè)會(huì)話持有的鎖都只有插入表意向排他鎖(IX)。
結(jié)論
INSERT...SELECT語(yǔ)句是否對(duì)查詢表加鎖跟事務(wù)隔離級(jí)別有關(guān)，REPEATABLE-READ隔離級(jí)別下加共享讀鎖，此共享讀鎖屬于Nextkey lock，會(huì)影響其他事務(wù)對(duì)查詢表的DML操作；READ-COMMITTED下不加鎖，不影響其他事務(wù)對(duì)表進(jìn)行DML操作。
