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

数据库 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: 表示对所有客户端会话窗口有效

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

责任编辑:武晓燕 来源: springboot葵花宝典
相关推荐

2024-04-19 08:18:47

MySQLSQL隔离

2022-04-27 07:32:02

脏读幻读不可重复读

2022-06-29 11:01:05

MySQL事务隔离级别

2024-04-24 08:26:35

事务数据InnoDB

2023-02-02 07:06:10

2019-03-21 09:06:00

数据库复读幻读

2022-01-03 07:18:05

脏读幻读 MySQL

2024-05-13 11:46:33

MySQL数据库

2023-10-26 00:41:46

脏读数据幻读

2018-01-03 09:02:13

不可重复读幻读MySQL

2024-07-16 08:19:46

MySQL数据InnoDB

2021-08-02 09:01:05

MySQL 多版本并发数据库

2023-12-26 08:08:02

Spring事务MySQL

2019-12-24 14:50:01

MySQL可重复读数据库

2024-03-11 00:00:00

mysqlInnoDB幻读

2021-09-07 10:33:42

MySQL事务隔离性

2019-05-28 13:50:27

MySQL幻读数据库

2021-04-20 19:21:50

脏读MySQL幻读

2022-09-21 09:00:10

MySQL幻读隔离级别

2021-07-26 10:28:13

MySQL事务隔离
点赞
收藏

51CTO技术栈公众号