mysql中RR/RC隔离级别的正确用法

假设有表

代码语言：javascript复制

CREATE TABLE IF NOT EXISTS `mytable`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `flow` INT NOT NULL,
   `name` VARCHAR(100) NOT NULL,
   `age` int NOT NULL,
   key(flow),
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入三行原数据
insert into mytable values(1,1,"hello1", 10); 
insert into mytable values(5,5,"hello2", 15); 
insert into mytable values(10,10,"hello3", 20); 

设置隔离级别为RR，如非特指，默认在RR下执行操作。

代码语言：javascript复制

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
如果只影响这一个session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

主键操作

insert场景

session1	session2
select * from mytable where id = 5 for update;
	insert into mytable values(3,3,"hello3", 15);	不阻塞

session1查询主键，且这一行存在。

session2 insert新行不阻塞，因为主键中id=5的行存在，锁退化为行锁。

利用主键（唯一索引）的这个特性，可以极大的优化性能。

但是，如果查主键不存在的列，为了防止幻读，此时将变成间隙锁。这对性能将是极大的损失。

session1	session2
select * from mytable where id = 3 for update;		加(1,5)间隙锁
	insert into mytable values(3,3,"hello3", 15);	阻塞
	insert into mytable values(2,2,"hello3", 15);	阻塞
	insert into mytable values(6,6,"hello6", 15);	不阻塞，因为在间隙外

失败案例分析

为了实现幂等，某服务将id作为唯一key，客户端会根据时间生成一个最新的ID传给服务端，服务端会验证逻辑，伪代码如下：

代码语言：javascript复制

START TRANSACTION;
select * from flow_table where id = id1 for update;
if ret != null {
    // 说明这个ID已被别人使用过，已存在记录
    rollback;
    return;
}
insert into flow_table values(id1,...); // 这一行每次都会卡住，不能并发执行
if err{
    rollback;
    return;
}
dosth();
commit;

事实上，因为传入的id1为最新时间生成，大概率不存在且大于最大的ID，但仍然会加间隙锁。锁定(id1, 无穷大)的区间。

此时另外的session执行的insert语句，传入id2，如果id2>id1，将会阻塞。整个过程变成串行。

解决方案：将默认隔离级别改为RC

代码语言：javascript复制

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

此时执行场景将如下：

session1	session2	备注
select * from flow_table where id = id1 for update;
	select * from flow_table where id = id1 for update;
insert into flow_table values(id1,...);		成功，不阻塞
	insert into flow_table values(id1,...);	阻塞，等待，如果id不为id1，则直接成功
commit
	报错Duplicate entry 'id1' for key 'mytable.PRIMARY'

update场景

session1	session2	备注
select * from mytable where id = 5 for update;
	update mytable set age=age 1 where id = 10;	不阻塞
	update mytable set age=age 1 where id = 5;	阻塞
select * from mytable;		select原值缓存

唯一ID加锁退化为行锁(id=5)。所以update id=10不阻塞，但update id=5会阻塞。

如果select 主键，且这行不存在，则update不会受影响。

session1	session2	备注
select * from mytable where id = 3 for update;		不存在
	update mytable set age=age 1 where id = 5;	不阻塞
	update mytable set age=age 1 where id = 3;	不阻塞，影响0行

查主键范围

session1	session2	session3
select * from mytable where id<6 for update;
	insert into mytable values(9,9,"hello9", 15);	阻塞
	insert into mytable values(11,11,"hello3", 15);	不阻塞

在RR等级中，因为避免了幻读，此时会使用间隙锁。

首先扫描索引加上next-key lock (1,5] ,接着向右遍历到第一个不满足条件的，因此加锁范围为（5,10）。这是为了防止幻读。

因为9在间隙内，所以会阻塞，而11在间隙外。

如果改为RC等级。情况就会不同。

代码语言：javascript复制

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

session1	session2
select * from mytable where id>=0 and id<6 for update;
	insert into mytable values(9,9,"hello9", 15);	不阻塞
	commit;
select * from mytable where id>=0 and id<6;		查出session2已提交的数据，幻读发生。

在RC等级中，session1将看到session2提交的数据，也就是幻读。

但session2 insert的时候将不会阻塞。

非唯一索引操作

select ... for update

session1	session2
select * from mytable where flow = 5 for update;
	insert into mytable values(3,3,"hello3", 15);	阻塞
	RC等级：insert into mytable values(3,3,"hello3", 15);	不阻塞，但提交后幻读

session2阻塞。

因为flow是非唯一索引，首先扫描索引加上next-key lock (1,5] ,接着向右遍历到第一个不满足条件的，因此加锁范围为（5,10）。此时锁是间隙锁。

对于非唯一索引，加锁的范围会变大。注意！！！

> 单纯的select（不使用for update）不会阻塞任何其它session的insert/update语句。

如果将隔离级别改为RC，与主键类似，insert将不会阻塞，但session2提交后，session1数据将幻读。

update语句会加间隙锁

update和select ... for update类似

session1	session2
update mytable set age=age 1 where flow = 5;
	select * from mytable where flow = 5;	不阻塞，因为是单纯的select
	update mytable set age=age 1 where flow = 1;	不阻塞，存在，但未命中间隙
	update mytable set age=age 1 where flow = 5;	阻塞，命中锁
	update mytable set age=age 1 where flow = 6;	不阻塞，因为flow=6不存在
	insert into mytable values(6,6,"hello6", 15);	阻塞，命中间隙
	update mytable set age=age 1 where flow = 10;	不阻塞，存在，但未命中间隙

update会加间隙锁。首先扫描索引加上next-key lock (1,5] ,接着向右遍历到第一个不满足条件的。本例中，加锁范围为(0,5] and (5,10)

总结

1. 单纯的select不会阻塞任何操作。
2. 善用主键（唯一索引），对主键进行for update查询，如果这行存在，会退化为行锁：select * from mytable where id = 5 for update;。
3. 对主键进行for update查询，如果这行不存在，将会加间隙锁。为了增加并行，<mark style="background: #ff6666">此时可考虑隔离级别RC</mark>。
4. 慎用主键范围查询并for update，将使用间隙锁：select * from mytable where id < 6 for update;此时间隙内的insert将会阻塞。
5. 慎用非唯一索引，对非唯一索引进行for update查询，将会使用间隙锁<mark style="background: #ff6666">（哪怕只查一行）</mark>：select * from mytable where flow = 5 for update;，此时间隙内的insert将会阻塞。
6. <mark style="background: #ff6666">调整隔离级别为RC</mark>，可避免间隙锁的问题，增加并发，但可能会产生幻读。
7. <mark style="background: #ff6666">不操作非索引字段</mark>，对非索引字段进行for update查询，将直接锁表。非常危险。

insert mysql 遍历 数据 索引

0 人点赞