怎样选择MySQL事务隔离级别?

数据库 MySQL
对于 Serializable 隔离级别,因为它强制事务串行执行,会在读取的每一行数据上都加锁,因此可能会导致大量的超时和锁争用的问题。生成环境也不建议使用。

我们回到一个经常会讨论的问题:MySQL事务隔离级别究竟应该怎么选择?

先说一下我自己的见解:

建议在RC和RR两个隔离级别中选一种,如果能接受幻读,需要并发高点,就可以配置成RC:

如果不能接受幻读的情况,就设置成RR隔离级别。

我们就来详细介绍一下MySQL的4种事务隔离级别。

1 通过基本定义认识事务隔离级别

四种隔离级别的基本定义(如果觉得文字不太好理解,可以结合文章后面的实验部分):

事务隔离级别

解释

Read uncommitted

(读未提交,简称:RU)

所有事务都可以看到其它未提交事务的执行结果,这也就是脏读。

Read Committed

(读已提交,简称:RC)

一个事务只能看见已经提交事务所做的改变,某个事务执行期间可能有其他事务提交,所以可能出现幻读

Repeatable Read

(可重复读,简称:RR)

这是MySQL的默认事务隔离级别,它确保同一事务相同的语句多次查询时,会看到同样的数据行。消除了脏读、不可重复读,默认也不会出现幻读

Serializable

(串行)

这是最高的隔离级别,它通过强制事务排序,使不同事务之间不可能相互冲突,从而解决幻读问题

解释一下幻读:在一个事务里面,按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足查询条件的新数据。这种情况就称为幻读。


2 通过实验认识Read uncommitted

创建测试表和写入测试数据:

use martin;

drop procedure if exists insert_t21; 
delimiter ;;
create procedure insert_t21() 
begin

drop table if exists t21;

CREATE TABLE `t21` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int NOT NULL,
`b` int NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_c` (`a`)
) ENGINE=InnoDB CHARSET=utf8mb4;
insert into t21(a,b) values (1,1),(2,2);

end;;
delimiter ;


按下图进行RU隔离级别的实验:

步骤

session1

session2

1

call insert_t21();


2

set session transaction_isolation='READ-UNCOMMITTED';

set session transaction_isolation='READ-UNCOMMITTED';

3

begin;

begin;

4

select * from t21 where a=1;


5


insert into t21(a,b) values (1,3);

6

select * from t21 where a=1;


7

commit;

commit;

上面的实验中,第 5 步中 session2 写入了一条 a、b 值分别为 1、3 的记录,在第 6 步中,session2 中的事务还没提交,但是 session1 就能看到 session2 写入的数据,出现了脏读现象。


3 通过实验认识Read Committed

按下图,进行RC隔离级别的实验:

ID

session1

session2

1

call insert_t21 ();


2

set session transaction_isolation='READ-COMMITTED';

set session transaction_isolation='READ-COMMITTED';

3

begin;

begin;

4

select * from t21 where a=1;


5


insert into t21(a,b) values (1,3);

6

select * from t21 where a=1;


7


commit;

8

select * from t21 where a=1;


9

commit;



实验结果是:

session2 写入了新数据未提交的情况下,session1 无法查看到新记录,等到 session2 提交之后,session1 才能看到第 5 步 session2 写入的数据。

但是存在一个问题就是在session1这个事务里面,按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据。也就是出现了幻读。

4 通过实验认识Repeatable Read

再来看下RR隔离级别下的实验:

ID

session1

session2

1

call insert_t21 ();


2

set session transaction_isolation='REPEATABLE-READ';

set session transaction_isolation='REPEATABLE-READ';

3

begin;

begin;

4

select * from t21 where a=1;


5


insert into t21(a,b) values (1,3);

6

select * from t21 where a=1;


7


commit;

8

select * from t21 where a=1;


9

commit;


10

select * from t21 where a=1;


实验结论:

session2 写入了新数据未提交的情况下,session1 无法查看到新记录,等到 session2 提交但是 session1 还未提交时,session1 还是不能看到新记录,需要等 session1 事务提交之后,才能查看到第 5 步 session2 写入的新数据。

也就是RR隔离级别下,在同一个事务里面,前后两条一样的语句,读取的数据是一样的。

5 通过实验认识Serializable

进行如下实验:

ID

session1

session2

1

call insert_t21 ();


2

set session transaction_isolation='SERIALIZABLE';

set session transaction_isolation='SERIALIZABLE';

3

begin;

begin;

4

select * from t21 where a=1;


5


insert into t21(a,b) values (1,3);

(等待)

6

select * from t21 where a=1;


7

commit;

session1 提交后,第 5 步中的写入操作执行成功

8


commit;

9

select * from t21 where a=1;


当 session1 中有事务查询 a=1 这行记录时,在 session2 就不能插入 a=1 的记录,进入等待。必须等 session1 提交后,session2 才能执行成功。也就是让事务串行进行。

6 Read uncommitted的例子

拿零售业务场景来讲,在事务隔离级别 RU 下:

比如顾客 A 在超市买单时,

当收银员扫完顾客 A 的支付码后,因为网络原因,一直等待着(也就是整个支付过程的事务还没结束);

这时收银员去后台数据查询,看到 A 的钱已经进入超市账户了,然后让顾客 A 离开。

过了一会,整个支付过程回滚了,才发现 A 实际是支付失败。

这样超市岂不是很亏。

这就是 RU 隔离级别可能导致脏读的情况。

7 Read Committed的例子

顾客A在超市购买了90元的东西。

收银系统查询到顾客A还剩100元,足够扣款,

A 的老婆在家网购,花掉了A账户里的这100块,

收银系统在扣除A账户90元时,就会出现报错,

顾客A肯定郁闷,不是明明钱够么?

这就是 RC 隔离级别下的幻读现象。

8 Repeatable Read的例子

顾客A在超市购买了90元的东西。

当收银系统查询到顾客A还剩100 元,足够扣款,

这期间A 的老婆在家网购,能查询到 A 的账户里还有 100 元,但是想要用 A 账户里的 100 块,却发现并不能使用这 100 元,

A最后的扣款步骤也能正常完成,最终顺利完成了整个付款过程,

这就是可重复读的现象。

9 Serializable的例子

顾客A在超市购买了90元的东西。

当收银系统查询到顾客A还剩100元,足够扣款,

此时A 的老婆在家网购,想查询 A 账户里还有多少钱,却发现无法查看到,必须要等到 A 整个付款完成,其老婆才能去查询余额,

这就是串行导致的。

10 如何选择合适的事务隔离级别

对于RU隔离级别,会导致脏读,从性能上看,也不会比其它隔离级别好太多,因此生产环境不建议使用。

对于RC隔离级别,相比RU隔离级别,不会出现脏读;但是会出现幻读,一个事务中的两次执行同样的查询,可能得到不一样的结果。

对于 RR 隔离级别,相比RC隔离级别,解决了部分幻读,我们在锁那一章也有详细讲解,但是相对于RC,锁的范围可能更大了。

对于 Serializable 隔离级别,因为它强制事务串行执行,会在读取的每一行数据上都加锁,因此可能会导致大量的超时和锁争用的问题。生成环境也不建议使用。

因此总的来说,建议在RC和RR两个隔离级别中选一种,如果能接受幻读,需要并发高点,就可以配置成RC,如果不能接受幻读的情况,就设置成RR隔离级别。

责任编辑:武晓燕 来源: MySQL数据库联盟
相关推荐

2018-12-19 16:46:38

MySQL事务隔离数据库

2021-07-26 10:28:13

MySQL事务隔离

2021-08-04 13:19:42

MySQL 事务隔离

2010-11-19 16:13:06

oracle事务隔离级

2009-06-29 17:54:47

Spring事务隔离

2021-10-19 10:10:51

MySQL事务隔离级别数据库

2022-06-10 11:51:49

MySQL事务隔离

2020-10-13 10:32:24

MySQL事务MVCC

2021-08-30 20:12:11

MySQL事务隔离

2022-06-29 11:01:05

MySQL事务隔离级别

2021-01-18 11:49:26

面试事务隔离

2022-09-13 13:49:05

数据库隔离

2020-09-21 18:44:35

MySQL

2019-10-15 10:23:13

服务器MySQL 数据

2023-02-02 07:06:10

2024-07-16 08:19:46

MySQL数据InnoDB

2017-08-09 14:34:12

MysqlJavaPython

2023-10-11 08:09:53

事务隔离级别

2022-09-19 06:16:23

事务隔离级别Spring

2023-06-01 07:37:48

级别事务调度
点赞
收藏

51CTO技术栈公众号