生产环境的MySQL事务隔离级别方式_Mysql

本文探讨了MySQL数据库在RR（可重复读）和RC（读已提交）隔离级别下的锁机制,在RR级别下,UPDATE语句会锁定所有符合条件的行,包括不符合条件的行,以防止幻读,而在RC级别下,UPDATE语句仅锁定符合条件的行,通过半一致性读优化,可以进一步提高并发度

目录

• 生产环境的MySQL事务隔离级别
• 创建如下的表，并插入一些记录
• 此时执行SQL-A ，且未提交事务
• 半一致性读(“semi-consistent”read)
• 总结

生产环境的MySQL事务隔离级别

MySQL 数据库的默认隔离级别是 RR( 可重复读 )，但是很多大公司把隔离级别改成了 RC(读已提交)，主要原因是为了提高并发和降低死锁概率

为了解决幻读的问题 RR 相比 RC 多了间隙锁( gap lock )和临键锁( next-keylock )。而 RC 中修改数据仅用行锁，锁定的范围更小，因此相比而言 RC的并发更高。

创建如下的表，并插入一些记录

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

此时执行SQL-A ，且未提交事务

UPDATE t SET b = 5 WHERE b = 3;

在 RR 即可重复隔离级别情况下，会锁哪几条数据呢?

答案是会锁定所有符合条件的行，并且为了防止幻读，还会锁定这些行之间的间隙。

x-lock(2,3) 和 x-lock(4,3)  -- 这两行会被更新并保留X锁。
x-lock(1,2)、x-lock(3,2) 和 x-lock(5,2) -- 这些行虽然不符合更新条件，但由于间隙锁的存在，它们也会被锁定以防止其他事务插入新的行

可以看到全锁了，此时执行 SQL-B:

UPDATE t SET b = 4 WHERE b = 2;

就会被阻塞了。

因此，在RR隔离级别下，UPDATE t SET b = 5 WHERE b = 3; 会锁定所有行，包括那些不符合更新条件的行。

而执行 SQL-A 在 RC 即读已提交隔离级别下，会锁哪几条数据呢?

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); 保留 x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); 保留 x-lock
x-lock(5,2); unlock(5,2)
-- 只会锁定符合更新条件的行，即 b = 3 的行，因为RC级别不使用间隙锁。

可以看到，只锁了两条数据，此时执行 SQL-B 会怎样?

x-lock(1,2); update(1,2) to (1,4); 保留 x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); 保留 x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); 保留 x-lock

可以看到仅锁了 b=2 的数据，完美避开了 SQL-A加的锁

此时可能有同学会有疑问: SQL-B 不是应该被 (2, 3 )这行的锁给阻塞吗?

半一致性读(“semi-consistent”read)

这其实也是 InnoDB 做的一个优化。

• 在执行 update 的时候，扫描发现当前行已经被锁定了，它就会执行半一致性读的操作，得到当前数据的最新版本(上述中 SQL-A 锁定的行最新版本的已提交数据，b都为5 )，来判断是否和当前的(SQL-B)update 的 where 条件匹配
• 如果匹配则说明当前的 update也需要锁定这行
• 因此需要等待。如果不匹配说明它们之间没关联，因此不需要等待锁，这个优化提升了并发度。

所以 RC + 半一致性读 能进一步的提升 SQL 执行的并发度。

并且 RC 锁的粒度更小，意味着死锁的概率会更低，但是缺点是可能会产生幻读，这个就需要业务自已评估幻读的问题(大部分情况下都没啥影响)。

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我。

您可能感兴趣的文章:

• MySQL的事务的基本要素和事务隔离级别详解
• mysql的事务隔离级别详细解析
• mysql中InnoDB事务隔离的记录锁、间隙锁和临键锁
• mysql事务的基本要素与事务隔离级别详解
• 一文带你搞懂MySQL的事务隔离级别
• mysql事务隔离级别详解