一文搞懂MySQL脏读,幻读和不可重复读_MsSql_

MySQL 中事务的隔离

在 MySQL 中事务的隔离级别有以下 4 种：

• 读未提交（READ UNCOMMITTED）
• 读已提交（READ COMMITTED）
• 可重复读（REPEATABLE READ）
• 序列化（SERIALIZABLE）

MySQL 默认的事务隔离级别是可重复读（REPEATABLE READ），这 4 种隔离级别的说明如下。

1.READ UNCOMMITTED

读未提交，也叫未提交读，该隔离级别的事务可以看到其他事务中未提交的数据。该隔离级别因为可以读取到其他事务中未提交的数据，而未提交的数据可能会发生回滚，因此我们把该级别读取到的数据称之为脏数据，把这个问题称之为脏读。

2.READ COMMITTED

读已提交，也叫提交读，该隔离级别的事务能读取到已经提交事务的数据，因此它不会有脏读问题。但由于在事务的执行中可以读取到其他事务提交的结果，所以在不同时间的相同 SQL 查询中，可能会得到不同的结果，这种现象叫做不可重复读。

3.REPEATABLE READ

可重复读，是 MySQL 的默认事务隔离级别，它能确保同一事务多次查询的结果一致。但也会有新的问题，比如此级别的事务正在执行时，另一个事务成功的插入了某条数据，但因为它每次查询的结果都是一样的，所以会导致查询不到这条数据，自己重复插入时又失败（因为唯一约束的原因）。明明在事务中查询不到这条信息，但自己就是插入不进去，这就叫幻读 （Phantom Read）。

4.SERIALIZABLE

序列化，事务最高隔离级别，它会强制事务排序，使之不会发生冲突，从而解决了脏读、不可重复读和幻读问题，但因为执行效率低，所以真正使用的场景并不多。​

简单总结一下，MySQL 的 4 种事务隔离级别对应脏读、不可重复读和幻读的关系如下：

只看以上概念会比较抽象，接下来，咱们一步步通过执行的结果来理解这几种隔离级别的区别。

前置知识

1.事务相关的常用命令

# 查看 MySQL 版本 select version(); # 开启事务 start transaction; # 提交事务 commit; # 回滚事务 rollback;

2.MySQL 8 之前查询事务的隔离级别

查看全局 MySQL 事务隔离级别和当前会话的事务隔离级别的 SQL 如下：

select @@global.tx_isolation,@@tx_isolation;

以上 SQL 执行结果如下图所示： 

3.MySQL 8 之后查询事务的隔离级别

select @@global.transaction_isolation,@@transaction_isolation;

4.查看连接的客户端详情

每个 MySQL 命令行窗口就是一个 MySQL 客户端，每个客户端都可以单独设置（不同的）事务隔离级别，这也是演示 MySQL 并发事务的基础。以下是查询客户端连接的 SQL 命令：

show processlist;

以上 SQL 执行结果如下： 

5.查询连接客户端的数量

可以使用以下 SQL 命令，查询连当前接 MySQL 服务器的客户端数量：

show status like 'Threads%';

以上 SQL 执行结果如下： 

6.设置客户端的事务隔离级别

通过以下 SQL 可以设置当前客户端的事务隔离级别：

set session transaction isolation level 事务隔离级别;

事务隔离级别的值有 4 个：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。

7.新建数据库和测试数据

创建测试数据库和表信息，执行 SQL 如下：

-- 创建数据库 drop database if exists testdb; create database testdb; use testdb; -- 创建表 create table userinfo( id int primary key auto_increment, name varchar(250) not null, balance decimal(10,2) not null default 0 ); -- 插入测试数据 insert into userinfo(id,name,balance) values(1,'Java',100),(2,'MySQL',200);

创建的表结构和数据如下： 

8.名称约定

接下来会使用两个窗口（两个客户端）来演示事务不同隔离级别中脏读、不可重复读和幻读的问题。其中左边的黑底绿字的客户端下文将使用“窗口 1”来指代，而右边的蓝底白字的客户端下文将用“窗口 2”来指代，

如下图所示： 

脏读

一个事务读到另外一个事务还没有提交的数据，称之为脏读。 脏读演示的执行流程如下：

1.脏读演示步骤1

设置窗口 2 的事务隔离级别为读未提交，设置命令如下：

set session transaction isolation level read uncommitted;

PS：事务隔离级别读未提交存在脏读的问题。

然后使用命令来检查当前连接窗口的事务隔离界别，如下图所示： 

 开启事务并查询用户列表信息，如下图所示： 

2.脏读演示步骤2

在窗口 1 中开启一个事务，并给 Java 账户加 50 元，但不提交事务，执行的 SQL 如下： 

3.脏读演示步骤3

在窗口 2 中再次查询用户列表，执行结果如下： 

 从上述结果可以看出，在窗口 2 中读取到了窗口 1 中事务未提交的数据，这就是脏读。

4.不可重复读

不可重复读是指一个事务先后执行同一条 SQL，但两次读取到的数据不同，就是不可重复读。

不可重复读演示的执行流程如下：

窗口 2 同一个事务中的两次查询，得到了不同的结果这就是不可重复读，具体执行步骤如下。

5.不可重复读演示步骤1

设置窗口 2 的事务隔离级别为读已提交，设置命令如下：

set session transaction isolation level read committed;

PS：读已提交可以解决脏读的问题，但存在不可重复读的问题。

使用命令来检查当前连接窗口的事务隔离界别，如下图所示： 

在窗口 2 中开启事务，并查询用户表，执行结果如下： 

 此时查询的列表中，Java 用户的余额为 100 元。

6.不可重复读演示步骤2

在窗口 1 中开启事务，并给 Java 用户添加 20 元，但不提交事务，再观察窗口 2 中有没有脏读的问题，

具体执行结果如下图所示： 

 从上述结果可以看出，当把窗口的事务隔离级别设置为读已提交，已经不存在脏读问题了。 接下来在窗口 1 中提交事务，执行结果如下图所示： 

7.

提示： 本文由神整理自网络，如有侵权请联系本站删除！
本站声明：
1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！

上一篇：SQL Server忘记sa账号密码重新添加新管理账号_MsSql_

下一篇：浅谈为什么#{}可以防止SQL注入_MsSql_