RR与RC模式下的间隙锁导致的区别

隔离级别

MySQL默认隔离级别为 REPEATABLE READ
常用的隔离级别为如下俩个：

  • REPEATABLE READ 可重复读
  • READ COMMITTED 已提交读

间隙锁（REPEATABLE READ）

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks

  • 间隙锁定是对索引记录之间的间隙的锁定，或者是对第一个或最后一个索引记录之前的间隙的锁定。
  • 对于唯一索引来说，没有间隙锁（不包括搜索条件含多列唯一索引情况）。
  • 间隙锁在 REPEATABLE READ 以上模式下才存在

实例展示

测试数据

环境：MySQL 8.0.18

【session1】
create database locktest;
drop table yqlock1;
create table yqlock1 (id int auto_increment primary key , a int ,b varchar(30),key idx_a(a));
insert into yqlock1(a,b) values (3,5),(5,12),(9,8),(7,1),(8,5),(15,20);

root@yq [locktest]> select * from yqlock1 order by a;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    3 | 5    |
|  2 |    5 | 12   |
|  4 |    7 | 1    |
|  5 |    8 | 5    |
|  3 |    9 | 8    |
|  6 |   15 | 20   |
+----+------+------+
6 rows in set (0.00 sec)

RR隔离级别

条件字段有索引

# 查看隔离级别
root@yq [locktest]> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

begin;
update yqlock1 set b = '隔壁老王' where a = '5'; # a列存在索引

# 8.0 可以查看data_locks表查看锁定的数据行
# 可以看到有一个间隙锁 7, 4
root@yq [locktest]> select ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| INNODB |               2287449 |    155509 |       15 | locktest      | yqlock1     | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| INNODB |               2287449 |    155509 |       15 | locktest      | yqlock1     | idx_a      | RECORD    | X             | GRANTED     | 5, 2      |
| INNODB |               2287449 |    155509 |       15 | locktest      | yqlock1     | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
| INNODB |               2287449 |    155509 |       15 | locktest      | yqlock1     | idx_a      | RECORD    | X,GAP         | GRANTED     | 7, 4      |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)

# 【session2】新开一个窗口，执行insert
root@yq [locktest]> begin;
Query OK, 0 rows affected (0.00 sec)

root@yq [locktest]> insert into yqlock1 select 7,'5','aaa';  # 处于等待状态
root@yq [locktest]> insert into yqlock1 select 7,'6','aaa';  # 处于等待状态
root@yq [locktest]> insert into yqlock1 select 7,'7','aaa';  # 成功插入
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

在测试下更新的数据是不存在的记录

【session1】
root@yq [locktest]> begin;
Query OK, 0 rows affected (0.00 sec)

root@yq [locktest]> update yqlock1 set b = '隔壁老王' where a = '12';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

root@yq [locktest]> select ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |               2287472 |    155663 |       25 | locktest      | yqlock1     | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |               2287472 |    155663 |       25 | locktest      | yqlock1     | idx_a      | RECORD    | X,GAP     | GRANTED     | 15, 6     |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

【session2】
root@yq [locktest]> begin;
Query OK, 0 rows affected (0.00 sec)
root@yq [locktest]> insert into yqlock1 select 7,'9','aaa';   ## 等待
root@yq [locktest]> insert into yqlock1 select 8,'10','aaa';  ## 等待
root@yq [locktest]> insert into yqlock1 select 9,'12','aaa';  ## 等待
root@yq [locktest]> insert into yqlock1 select 10,'15','aaa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

由上示例可得知：

  • RR模式下存在间隙锁（GAP），会锁住两索引间的间隙。
  • 条件有索引情况下，实际锁住的数据是左闭右开(5,7],其实这是 next-key lock(行锁+GAP)

条件字段无索引

【session1】
root@yq [locktest]> select * from yqlock1 order by b;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  4 |    7 | 1    |
|  2 |    5 | 12   |
|  6 |   15 | 20   |
|  1 |    3 | 5    |
|  5 |    8 | 5    |
|  3 |    9 | 8    |
+----+------+------+
6 rows in set (0.00 sec)

begin;
root@yq [locktest]> update yqlock1 set a = 123 where b = '5';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

root@yq [locktest]> select ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| INNODB |               2287516 |    155889 |       18 | locktest      | yqlock1     | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB |               2287516 |    155889 |       18 | locktest      | yqlock1     | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB |               2287516 |    155889 |       18 | locktest      | yqlock1     | PRIMARY    | RECORD    | X         | GRANTED     | 1                      |
| INNODB |               2287516 |    155889 |       18 | locktest      | yqlock1     | PRIMARY    | RECORD    | X         | GRANTED     | 3                      |
| INNODB |               2287516 |    155889 |       18 | locktest      | yqlock1     | PRIMARY    | RECORD    | X         | GRANTED     | 4                      |
| INNODB |               2287516 |    155889 |       18 | locktest      | yqlock1     | PRIMARY    | RECORD    | X         | GRANTED     | 5                      |
| INNODB |               2287516 |    155889 |       18 | locktest      | yqlock1     | PRIMARY    | RECORD    | X         | GRANTED     | 6                      |
| INNODB |               2287516 |    155889 |       18 | locktest      | yqlock1     | PRIMARY    | RECORD    | X         | GRANTED     | 2                      |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
  • 条件无索引情况下，锁全记录，并存在 supremum pseudo-record，也就是后续的也不能写入（锁全表）

RC隔离级别

条件字段有索引

【session1】
root@yq [locktest]> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

root@yq [locktest]> set session transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

root@yq [locktest]> show variables like '%iso%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

root@yq [locktest]> begin;
Query OK, 0 rows affected (0.00 sec)

root@yq [locktest]> update yqlock1 set b = '隔壁老王' where a = '5';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@yq [locktest]> select ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| INNODB |               2287524 |    156236 |        8 | locktest      | yqlock1     | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| INNODB |               2287524 |    156236 |        8 | locktest      | yqlock1     | idx_a      | RECORD    | X,REC_NOT_GAP | GRANTED     | 5, 2      |
| INNODB |               2287524 |    156236 |        8 | locktest      | yqlock1     | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)

【session2】
root@yq [locktest]> begin;
Query OK, 0 rows affected (0.00 sec)

root@yq [locktest]> insert into yqlock1 select 7,'9','aaa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@yq [locktest]> insert into yqlock1 select 8,'10','aaa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@yq [locktest]> insert into yqlock1 select 9,'12','aaa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@yq [locktest]> insert into yqlock1 select 10,'15','aaa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
  • 仅锁住了相应的条件行

更新的数据是不存在的记录

【session1】
root@yq [locktest]> begin;
Query OK, 0 rows affected (0.00 sec)

root@yq [locktest]> update yqlock1 set b = '隔壁老王' where a = '12';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

root@yq [locktest]> select ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |               2287531 |    156236 |       13 | locktest      | yqlock1     | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

【session2】
root@yq [locktest]> begin;
Query OK, 0 rows affected (0.00 sec)

root@yq [locktest]> insert into yqlock1 select 10,'12','aaa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
  • 仅有一个IX锁，不影响数据写入，即使写入的值是上述更新的值

条件字段无索引

root@yq [locktest]> begin;
Query OK, 0 rows affected (0.00 sec)

root@yq [locktest]> update yqlock1 set a = 123 where b = '5';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

root@yq [locktest]> select ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| INNODB |               2287537 |    156236 |       18 | locktest      | yqlock1     | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| INNODB |               2287537 |    156236 |       18 | locktest      | yqlock1     | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
| INNODB |               2287537 |    156236 |       18 | locktest      | yqlock1     | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 5         |
+--------+-----------------------+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)
  • 可以看出仅对条件符合的行加锁
  • 其实在RC模式下，刚开始是锁了全记录的，MySQL评估WHERE条件后，将释放不匹配行的记录锁。
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。