mysql中RR/RC隔离级别的正确用法
假设有表
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下执行操作。
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传给服务端，服务端会验证逻辑，伪代码如下：
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
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等级。情况就会不同。
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)
总结
- 单纯的select不会阻塞任何操作。
- 善用主键（唯一索引），对主键进行
for update查询，如果这行存在，会退化为行锁：select * from mytable where id = 5 for update;。 - 对主键进行
for update查询，如果这行不存在，将会加间隙锁。为了增加并行，<mark style="background: #ff6666">此时可考虑隔离级别RC</mark>。 - 慎用主键范围查询并
for update，将使用间隙锁：select * from mytable where id < 6 for update;此时间隙内的insert将会阻塞。 - 慎用非唯一索引，对非唯一索引进行
for update查询，将会使用间隙锁<mark style="background: #ff6666">（哪怕只查一行）</mark>：select * from mytable where flow = 5 for update;，此时间隙内的insert将会阻塞。 - <mark style="background: #ff6666">调整隔离级别为RC</mark>，可避免间隙锁的问题，增加并发，但可能会产生幻读。
- <mark style="background: #ff6666">不操作非索引字段</mark>，对非索引字段进行
for update查询，将直接锁表。非常危险。