mysql中RR/RC隔离级别的正确用法 假设有表 ```sql 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下执行操作。 ```sql 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); | 不阻塞 | | | select * from mytable where id = 5 for update; | 阻塞 | session1查询主键，**且这一行存在**。 session2 insert新行不阻塞，因为主键中id=5的行存在，**锁退化为行锁**。并且，在另一个session执行select此行，也会阻塞。 利用主键（唯一索引）的这个特性，可以极大的优化性能。 但是，如果**查主键不存在的列**，为了防止幻读，**此时将变成间隙锁**。这对性能将是极大的损失。 | 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等级**。情况就会不同。 ```sql 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] ,接着向右遍历到第一个不满足条件的。本例中，加锁范围为(1,5] and (5,10) ## 总结 1. 单纯的select不会阻塞任何操作。 2. 善用主键（唯一索引），对主键进行`for update`查询，如果这行存在，会退化为行锁：`select * from mytable where id = 5 for update;`。 3. 对主键进行`for update`查询，如果这行不存在，将会加间隙锁。为了增加并行，<mark style="background: #2ea8e5">此时可考虑隔离级别RC</mark>。 4. 慎用主键范围查询并`for update`，**将使用间隙锁**：`select * from mytable where id < 6 for update;`此时间隙内的insert将会阻塞。 5. 慎用非唯一索引，对非唯一索引进行`for update`查询，将会使用间隙锁<mark style="background: #2ea8e5">（哪怕只查一行）</mark>：`select * from mytable where flow = 5 for update;`，此时间隙内的insert将会阻塞。 6. <mark style="background: #2ea8e5">调整隔离级别为RC</mark>，可避免间隙锁的问题，增加并发，但可能会产生幻读。 7. <mark style="background: #2ea8e5">不操作非索引字段</mark>，对非索引字段进行`for update`查询，将直接锁表。非常危险。 来自 大脸猪 写于 2023-06-05 20:37 -- 更新于2024-01-16 20:31 -- 0 条评论