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

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