mysql8 Record Locks ,Gap Locks, Next-Key Locks 实验1
mysql8 Record Locks ,Gap Locks, Next-Key Locks 实验2 唯一索引和非唯一索引,无索引情况
5. RC 隔离级别下加锁行为
由于隔离级别的实现是靠加锁来实现的 所以在 考察完 RR 隔离级别下加锁的情形后 我们需要分析另一个常用的隔离级别RC
首先需要把数据库的全局隔离级别设置为RC
set global TRANSACTION ISOLATION level read COMMITTED;
show global variables like 'transaction_isolation';
-- 恢复为RR
-- set global TRANSACTION ISOLATION level repeatable read;
5.1 主键索引等值查询
5.1.1 存在值等值查询
我们启动一个新的连接 执行以下sql
# connection1
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.id=13 for update;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 13 | 13 | 13 | 13 |
+-----------+----+----+----+----+
1 row in set (0.04 sec)
-- 查询锁信息
SELECT
row_number() OVER() 行号
,t.THREAD_ID '锁线程(THREAD_ID)'
,t.OBJECT_NAME '锁的表(OBJECT_NAME)'
,t.INDEX_NAME '锁的索引(INDEX_NAME)'
,t.LOCK_TYPE '锁粒度(LOCK_TYPE)'
,t.LOCK_MODE '锁类型(LOCK_MODE)'
,t.LOCK_STATUS '锁状态(LOCK_STATUS)'
,t.LOCK_DATA '锁的数据(LOCK_DATA)'
FROM performance_schema.data_locks t
查询结果为:
5.1.2 不存在值的等值查询
哪锁定【不存在记录】会怎么样呢?
START TRANSACTION;
-- 15这条记录不存在
SELECT * FROM locktest t WHERE t.id=15 FOR update;
Query OK, 0 rows affected (0.00 sec) --无记录返回
此时的锁信息为,发现没有锁存在
而 RR 隔离级别下 会有 id=20 上加了一个 X排他锁还有一个 GAP 锁,而这个GAP 就是 (13,20) 中间的间隙
这说明在高的隔离级别RR下 会比低的隔离级别 RC 多出很多锁,在竞争激烈的时候会明显影响性能。
5.1 主键索引范围查询
5.1.1 单范围查询
对于以下范围查询,也是封锁的范围
START TRANSACTION;
SELECT * FROM locktest t WHERE t.id>11 FOR UPDATE;
RC 级别下加锁信息如下
RR 隔离级别加锁信息如下:
在RC 级别下 用另外一个连接下插入以下记录验证以下
START TRANSACTION;
-- 12 RC可以插入,由于没有了间隙锁,RR 下不可以插入,RR 下有GAP
INSERT INTO locktest (id,f1,f2,f3) VALUES (12,12,12,12);
START TRANSACTION;
-- 21 RC可以插入
INSERT INTO locktest (id,f1,f2,f3) VALUES (21,21,21,21);
-- 9 RC可以插入
START TRANSACTION;
INSERT INTO locktest (id,f1,f2,f3) VALUES (9,9,9,9);
可以看到 RC 下 明显比RR 下少加一些 GAP 锁,这样在插入和修改的时候可以增加并发度,缺点是存在幻读。
5.1.2 双范围查询
查询sql如下
START TRANSACTION;
SELECT * FROM locktest t WHERE t.id>11 AND t.id<20 FOR UPDATE;Query OK, 0 rows affected (0.00 sec)
+----+----+----+----+
| id | f1 | f2 | f3 |
+----+----+----+----+
| 13 | 13 | 13 | 13 |
+----+----+----+----+
1 row in set (0.00 sec)
RC 下锁信息如下
而RR 下锁信息如下
5.1.2 不存在的双范围查询
START TRANSACTION;
-- 记录 12 和21 都不存在
SELECT * FROM locktest t WHERE t.id>=12 AND t.id<=21 FOR UPDATE;
+----+----+----+----+
| id | f1 | f2 | f3 |
+----+----+----+----+
| 13 | 13 | 13 | 13 |
| 20 | 20 | 20 | 20 |
+----+----+----+----+
2 rows in set (0.00 sec)
RC 下锁信息如下,只有在主键索性上有2个 RECORD 锁,并且明确不存在GAP lock
而RR 下锁信息如下,只看 THREAD_ID=45
5.3 二级唯一索引等值查询
这次我们对 f1 字段操作,这是一个二级 唯一索引字段
5.3.1 锁定存在的值
-- 查询存在的值 11
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f1=11 FOR UPDATE ;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 11 | 11 | 11 | 11 |
+-----------+----+----+----+----+
1 row in set (0.00 sec)
RC查询锁的信息,发现有3个锁,如下图所示:
RR 下信息如下,这中情况下和RC 是一致的
【行号1】 是一个表级的意向锁
【行号2】 是一个加在 二级唯一索引 ui_f1上的 record 锁,锁类型为 X(排他),REC_NO__GAP 说明没有对间隙加锁
值得注意的是 LOCK_DATA为【11,11】 ,我们知道二级索引的record 组成为(key,主键),所以第一个11 是二级索引字段f1 的值,而第二个11 是主键的值
【行号3】 是一个主键索引上的 排他record 锁
5.3.2 锁定不存在的值
-- 查询存在的值 12
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f1=12 FOR UPDATE;
Empty set (0.00 sec)
RC下查询加锁情况:
而RR 下锁信息如下
对比 RR 比RC 多出一个 next key lock (11,13]
5.4 二级唯一索引范围查询
验证完唯一索引的等值查询加锁情况,我们开始验证范围查询
5.4.1 单范围查询
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f1>11 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 13 | 13 | 13 | 13 |
| 47 | 20 | 20 | 20 | 20 |
+-----------+----+----+----+----+
2 rows in set (0.00 sec)
RC加锁信息如下:
RR 加锁信息如下
RC 比RR 任然少 GAP 锁
5.4.2 双范围查询
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f1>11 AND t.f1<20 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 13 | 13 | 13 | 13 |
+-----------+----+----+----+----+
1 row in set (0.00 sec)
RC加锁信息如下图:
RR 加锁信息如下图
5.4.3 不存在的双范围查询
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID , t.* FROM locktest t WHERE t.f1>=12 AND t.f1<=21 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 13 | 13 | 13 | 13 |
| 47 | 20 | 20 | 20 | 20 |
+-----------+----+----+----+----+
2 rows in set (0.00 sec)
RC加锁信息如下:
RR 加锁信息如下:
5.5 非唯一索引等值查询
这次我们对 f2 字段操作,这个一个二级非唯一索引字段
5.5.1 锁定存在的值
-- 查询存在的值 11
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f2=11 FOR UPDATE ;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 11 | 11 | 11 | 11 |
+-----------+----+----+----+----+
1 row in set (0.00 sec)
RC锁信息:
RR查询锁的信息,发现有4个锁,如下图所示:
5.5.2 锁定不存在的值
-- 查询存在的值 12
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f2=12 FOR UPDATE;
Empty set (0.00 sec)
RC 加锁情况:
RR查询加锁情况:
5.6 非唯一索引范围查询
验证完 非唯一索引的等值查询加锁情况,我们开始验证范围查询
5.6.1 单范围查询
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f2>11 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 13 | 13 | 13 | 13 |
| 47 | 20 | 20 | 20 | 20 |
+-----------+----+----+----+----+
2 rows in set (0.00 sec)
RC 加锁信息如下:
RR加锁信息如下:
5.6.2 双范围查询
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f2>11 AND t.f2<20 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 13 | 13 | 13 | 13 |
+-----------+----+----+----+----+
1 row in set (0.00 sec)
RC 加锁信息:
RR加锁信息如下图:
5.6.3 不存在的双范围查询
START TRANSACTION;
SELECT * FROM locktest t WHERE t.f2>=12 AND t.f2<=21 FOR UPDATE;
+----+----+----+----+
| id | f1 | f2 | f3 |
+----+----+----+----+
| 13 | 13 | 13 | 13 |
| 20 | 20 | 20 | 20 |
+----+----+----+----+
2 rows in set (0.00 sec)
RC加锁信息如下:
RR加锁信息如下:
5.7 无索引等值查询
这次我们对 f3 字段操作,这个一个 无索引字段
5.7.1 锁定存在的值
-- 查询存在的值 11
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f3=11 FOR UPDATE ;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 11 | 11 | 11 | 11 |
+-----------+----+----+----+----+
1 row in set (0.00 sec)
RC 加锁记录,只有一条锁信息:
RR查询锁的信息,发现数据库中的数据都添加了锁,如下图所示:
5.7.2 锁定不存在的值
-- 查询存在的值 12
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f3=12 FOR UPDATE;
Empty set (0.00 sec)
RC加锁信息:
RR查询加锁情况:
5.8 无索引范围查询
5.8.1 单范围查询
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f3>11 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 13 | 13 | 13 | 13 |
| 47 | 20 | 20 | 20 | 20 |
+-----------+----+----+----+----+
2 rows in set (0.00 sec)
RC 加锁信息如下:
5.8.2 双范围查询
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f3>11 AND t.f3<20 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 13 | 13 | 13 | 13 |
+-----------+----+----+----+----+
1 row in set (0.00 sec)
RC 加锁信息:
5.8.3 不存在的双范围查询
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f3>=12 AND t.f3<=21 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
| 47 | 13 | 13 | 13 | 13 |
| 47 | 20 | 20 | 20 | 20 |
+-----------+----+----+----+----+
2 rows in set (0.00 sec)
RC加锁信息如下:
6. 总结
RR 对事务所涉及的数据加read lock,write lock和gap lock,且一直持有至事务结束。
RC 对事务涉及的数据加的write lock 会一直持续到事务结束,但加的read在查询操作完成后就马上会释放。
RC缺乏贯穿整个事务周期的read lock,无法禁止读取过的数据发生变化,所以无法防止幻读.
综合加锁情况和一般应用场景我们一般会在线上使用RC 的隔离级别。