翼度科技»论坛 编程开发 mysql 查看内容

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

7

主题

7

帖子

21

积分

新手上路

Rank: 1

积分
21
前言:

insert into t2 select * from t1; 这条语句会对查询表 t1 加锁吗?不要轻易下结论。对GreatSQL的锁进行研究之前,首先要确认一下事务的隔离级别,不同的事务隔离级别,锁的表现是不一样的。
实验:

创建测试表t1,t2
  1. greatsql> create table t1(id int primary key ,c1 varchar(10),c2 datetime,key idx_c1(c1));
  2. greatsql> create table t2 like t1;
  3. # id 列为主键,c1列上有普通索引
复制代码
创建存储过程,向t1表插入测试数据
  1. greatsql> delimiter //
  2. CREATE or replace PROCEDURE p1()
  3. BEGIN
  4. DECLARE p1 int default 0;
  5. while p1<5 do
  6. insert into t1(id,c1,c2) values(p1*2,round(rand()*10000),now());
  7. SET p1 = p1 + 1;
  8. end while;
  9. END;
  10. //
  11. delimiter ;
  12. greatsql> call p1;
  13. greatsql> select * from t1;
  14. +----+------+---------------------+
  15. | id | c1   | c2                  |
  16. +----+------+---------------------+
  17. |  0 | 2660 | 2024-02-21 15:45:00 |
  18. |  2 | 4627 | 2024-02-21 15:45:00 |
  19. |  4 | 5158 | 2024-02-21 15:45:00 |
  20. |  6 | 1907 | 2024-02-21 15:45:00 |
  21. |  8 | 4061 | 2024-02-21 15:45:00 |
  22. +----+------+---------------------+
  23. 5 rows in set (0.01 sec)
复制代码
REPEATABLE-READ隔离级别:

查询当前事务隔离级别:
  1. greatsql> show variables like 'transaction_isolation';
  2. +-----------------------+-----------------+
  3. | Variable_name         | Value           |
  4. +-----------------------+-----------------+
  5. | transaction_isolation | REPEATABLE-READ |
  6. +-----------------------+-----------------+
  7. 1 row in set (0.00 sec)
复制代码
connection 1:
  1. greatsql> select ps_current_thread_id();
  2. +------------------------+
  3. | ps_current_thread_id() |
  4. +------------------------+
  5. |                     92 |
  6. +------------------------+
  7. 1 row in set (0.00 sec)
  8. greatsql> begin;
  9. Query OK, 0 rows affected (0.00 sec)
  10. greatsql> insert into t2 select * from t1;
  11. Query OK, 5 rows affected (0.00 sec)
  12. Records: 5  Duplicates: 0  Warnings: 0
复制代码
connection2:
  1. greatsql> select ps_current_thread_id();
  2. +------------------------+
  3. | ps_current_thread_id() |
  4. +------------------------+
  5. |                     93 |
  6. +------------------------+
  7. 1 row in set (0.00 sec)
  8. greatsql> begin;
  9. Query OK, 0 rows affected (0.01 sec)
  10. greatsql> insert into t1(id,c1) values(1,'a');
复制代码
connection3:
  1. greatsql> select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
  2. +-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
  3. | THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE              | LOCK_STATUS | LOCK_DATA              |
  4. +-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
  5. |        93 | t1          | NULL       | TABLE     | IX                     | GRANTED     | NULL                   |
  6. |        93 | t1          | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 2                      |
  7. |        92 | t2          | NULL       | TABLE     | IX                     | GRANTED     | NULL                   |
  8. |        92 | t1          | NULL       | TABLE     | IS                     | GRANTED     | NULL                   |
  9. |        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | supremum pseudo-record |
  10. |        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 0                      |
  11. |        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 2                      |
  12. |        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 4                      |
  13. |        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 6                      |
  14. |        92 | t1          | PRIMARY    | RECORD    | S                      | GRANTED     | 8                      |
  15. +-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
  16. 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隔离级别

查询当前事务隔离级别:
  1. greatsql> show variables like 'transaction_isolation';
  2. +-----------------------+----------------+
  3. | Variable_name         | Value          |
  4. +-----------------------+----------------+
  5. | transaction_isolation | READ-COMMITTED |
  6. +-----------------------+----------------+
  7. 1 row in set (0.00 sec)
复制代码
connection 1
  1. greatsql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. greatsql> insert into t2 select * from t1;
  4. Query OK, 5 rows affected (0.01 sec)
  5. Records: 5  Duplicates: 0  Warnings: 0
复制代码
connection 2
  1. greatsql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. greatsql> insert into t1(id,c1) values(1,'a');
  4. Query OK, 1 row affected (0.00 sec)
复制代码
connection3
  1. greatsql> select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
  2. +-----------+-------------+------------+-----------+-----------+-------------+-----------+
  3. | THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
  4. +-----------+-------------+------------+-----------+-----------+-------------+-----------+
  5. |       104 | t1          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
  6. |       103 | t2          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
  7. +-----------+-------------+------------+-----------+-----------+-------------+-----------+
  8. 2 rows in set (0.00 sec)
复制代码
可以看出事务隔离级别设置为READ-COMMITTED后,表现截然不同。connection2并没有被阻塞,两个会话持有的锁都只有插入表意向排他锁(IX)。
结论:

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

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18055878
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

举报 回复 使用道具