MySQL事务与隔离级别：解析脏读、不可重复读和幻读问题

今日目标

• 理解事务特性
• 理解事务隔离级别
• 理解不同事务下出现的问题脏读、不可重复读和幻读

MySQL是广泛使用的关系型数据库管理系统，它支持事务处理，确保数据操作的一致性和可靠性。本文将介绍MySQL事务的基本概念和重要性。

1. 事务简介

事务：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

我们现在就拿一个经典的银行存取钱例子来说明: 李四给王五转账500块钱，李四银行账户就要减少500，并且王五账户要增加500。这一组操作就必须在一个事务范围内，要么转账同时成功，要么转账同时失败

id	name	money
1	李四	2000
2	王五	2000

转账分为以下情况：

1.正常情况:转账成功，可以分为以下基本三步完成，完成之后李四减少500，王五增加500，转账成功：

图片

最终数据库结果：

id	name	money
1	李四	1500
2	王五	2500

2.异常情况:转账失败，耶斯分为三步完成，假设李四减少500块钱以后，王五账户金额没有发生变化，这就造成了严重的数据不一致问题。

图片

问题解决方式:通过事务完成，我们在执行业务逻辑之前开启事务，业务执行完毕后，关闭事务。如果执行过程中出错，则事务回滚，将数据恢复到事务开启之前状态。

图片

注意: 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐 式的提交事务。

如果您觉得本文不错，欢迎关注,点赞,收藏支持，您的关注是我坚持的动力！

2. 事务操作

通过sql语句，实现刚才的例子。

2.1. 数据准备

-- 创建数据库test
create database  if not exists test;
use test;

-- 删除表
drop table if exists tb_account;

create table tb_account(
                           id int primary key AUTO_INCREMENT comment 'ID',
                           name varchar(10) comment '姓名',
                           money double(10,2) comment '余额'
) comment '账户表';
insert into tb_account(name, money) VALUES ('李四',2000), ('王五',2000);

2.2. 正常转账情况

-- 正常转账情况

-- 1. 查询李四余额
select * from tb_account where name = '李四';
-- 2. 李四的余额减少500
update tb_account set money = money - 500 where name = '李四';
-- 3. 王五的余额增加500
update tb_account set money = money + 500 where name = '王五';

-- 4. 查看账户结果
select * from tb_account;

测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。

图片

2.3. 正常异常情况

-- 转账异常情况
-- 1. 查询李四余额
select * from tb_account where name = '李四';
-- 2. 李四的余额减少500
update tb_account set money = money - 500 where name = '李四';
 出错了....
-- 3. 王五的余额增加500
update tb_account set money = money + 500 where name = '王五';
-- 4. 查看账户结果
select * from tb_account;

我们把数据都恢复到2000， 然后再次一次性执行上述的SQL语句(出错了.... 这句话不符合SQL语 法,执行就会报错)，检查最终的数据情况, 发现数据在操作前后不一致了。

图片

2.3. 控制事务解决转账情况

1.开启事务

-- 1.开启事务
start transaction  或者 BEGIN;

2.提交事务

-- 2.提交事务
commit;

3.事务回滚

-- 3.事务回滚
rollback ;

转账案例

-- 开启事务
start transaction;
-- 1. 查询李四余额
select * from tb_account where name = '李四';
-- 2. 李四的余额减少1000
update tb_account set money = money - 500 where name = '李四';
-- 如果转账失败 执行rollback
-- 3. 王五的余额增加1000
update tb_account set money = money + 500 where name = '王五';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;

3. 事务四大特性

MySQL事务遵循ACID属性，即原子性、一致性、隔离性和持久性。

• 原子性（Atomicity）：事务是不可分割的最小操作单元,事务内的所有操作要么全部成功，要么全部失败。如果出现错误，所有更改都会被撤销，数据库保持一致状态。
• 一致性（Consistency）：事务执行后，数据库状态必须满足预定义的一致性状态，不会导致矛盾或不一致的数据。
• 隔离性（Isolation）：隔离性控制多个事务之间的相互影响。MySQL支持不同的隔离级别，以满足不同应用的需求。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的，即使发生系统故障也不会丢失。

4. 事务隔离级别

SQL 标准中定义了四种隔离级别，分别是：

• 读未提交（Read Uncommitted）：这是最低的隔离级别。在该级别下，一个事务可以读取另一个事务尚未提交的数据，可能导致脏读（读取到未提交数据）、不可重复读（同一查询多次执行结果不一致）和幻读（查询结果集发生变化） 等问题。这个级别的并发性最高，但数据完整性较差。
• 读已提交（Read Committed）：在该级别下，事务只能读取已提交的数据，避免了脏读，但仍可能遇到不可重复读和幻读。这是许多数据库系统的默认隔离级别，提供了更好的数据一致性。
• 可重复读（Repeatable Read）：这是MySQL的默认隔离级别。在该级别下，事务在整个事务期间看到的数据保持一致，防止了脏读和不可重复读。但仍可能发生幻读问题。
• 串行化（Serializable）：这是最高的隔离级别，也是最严格的。在该级别下，事务被强制排队执行，以避免任何并发问题，包括脏读、不可重复读和幻读。这提供了最高级别的数据一致性，但也可能降低并发性能。

5. 事务隔离级别产生并发事务问题

事务隔离级别，是为了解决多个并行事务竞争导致的数据安全问题的一种规范。具体来说，多个事务竞争可能会产生三种不同的现象。

• 脏读。
• 不可重复读
• 幻读接下来我们分别介绍一下:

5.1. 脏读（Dirty Read）

脏读:一个事务读到另外一个事务还没有提交的数据

图片

示例sql:

1.事务A 中SQL

-- 步骤一：设置事务A隔离级别 读未提交

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
use test;
-- 步骤三:开启事务A,查询ID=1的数据
start transaction;
select * from tb_account where id=1;

-- 步骤五:开启事务A,查询ID=1的数据
select * from tb_account where id=1;

commit ;

2.事务B中SQL

-- 步骤二：设置事务B隔离级别 读未提交

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
use test;
-- 步骤四:开启事务A,
start transaction;
-- 更新ID=1的数据   mnotallow=money-500
update tb_account set mnotallow=money-500  where id=1;


commit ;

示例结果：

图片

5.2. 不可重复读（Non-Repeatable Read）

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

图片

示例sql:

1.事务A 中SQL

-- 读已提交隔离级别下 不可重复读
-- 步骤一：设置事务A隔离级别 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
use test;
-- 步骤三:开启事务A,查询ID=1的数据
start transaction;
select * from tb_account where id=1;

-- 步骤六:事务A,查询ID=1的数据
select * from tb_account where id=1;


-- 步骤八:事务A,查询ID=1的数据
select * from tb_account where id=1;
commit ;

2.事务B中SQL

-- 读已提交隔离级别下 不可重复读
-- 步骤二：设置事务B隔离级别 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
use test;
-- 步骤四:开启事务B,更新ID=1的数据   mnotallow=money-500
start transaction;
update tb_account set mnotallow=money-500  where id=1;
-- 步骤五:事务B查询id=1数据
select * from tb_account where id=1;
-- 步骤七:事务B 提交事务 并查询结果
commit ;
select * from tb_account where id=1;

示例结果

图片

5.3. 幻读（Phantom Read）

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 "幻影"。

图片

示例sql:

1.事务A 中SQL

-- 可重复读隔离级别下 幻读
-- 步骤一：设置事务A隔离级别 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
use test;
-- 步骤三:开启事务A,查询ID>1的数据
start transaction;
select * from tb_account where id>1;

-- 步骤五:事务A,查询ID>1的数据
select * from tb_account where id>1;


-- 步骤七:事务A,查询ID>1的数据
select * from tb_account where id>1;
commit ;

2.事务B中SQL

-- 可重复读隔离级别下 幻读
-- 步骤二：设置事务B隔离级别 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
use test;
-- 步骤四:开启事务B,插入一条数据
start transaction;
insert into tb_account values(3,'张三',2000);
-- 步骤六:提交事务B
commit ;

示例结果

图片

总结

隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）
读已提交（Read Committed）
可重复读（Repeatable Read）
串行化（Serializable）

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

• SESSION:表示当前客户端会话窗口有效
• GLOBAL: 表示对所有客户端会话窗口有效

注意：事务隔离级别越高，数据越安全，但是性能越低。

原文地址：https://mp.weixin.qq.com/s/XaKqWUrXsDHQIyB_G5aPBw