脏读、幻读、不可重复读图解

mysql事务隔离级别

MySQL提供了不同的隔离级别,包括:

  1. READ UNCOMMITTED(读取未提交):最低的隔离级别,不提供任何数据一致性保证,可能会出现不可重复读问题。

  2. READ COMMITTED(读取已提交):默认隔离级别,保证了一个事务不会读取到其他未提交事务的数据修改,但仍可能出现不可重复读问题。

  3. REPEATABLE READ(可重复读):保证在一个事务内多次读取相同数据时结果是一致的,解决了不可重复读问题。但仍可能出现幻读问题,即在一个事务内多次查询时,结果集的行数可能发生变化。

  4. SERIALIZABLE(可串行化):提供最高的隔离级别,保证了所有事务的串行执行,从而避免了不可重复读和幻读问题。但性能开销较高,不适用于所有场景。

你可以通过设置SET TRANSACTION ISOLATION LEVEL语句来更改事务的隔离级别

 

脏读

 

概念:脏读(Dirty Read)是数据库事务隔离级别中的一个问题,它发生在一个事务中读取了另一个未提交的事务中的数据。这可能会导致不一致的结果,因为读取的数据可能在后续被回滚或修改。

可能的后果:读可能导致数据的不一致性。因为读取的数据可能会在未来被回滚或修改,所以一个事务可能会基于不准确或不稳定的数据做出决策,从而引发问题。

 

在MySQL中,默认的事务隔离级别是可重复读(REPEATABLE READ),这意味着默认情况下不会发生脏读。

 

解决脏读:为了解决脏读问题,数据库管理系统通常使用锁定机制或隔离级别来控制并发访问。

 

脏读演示

1. 创建表、数据、查看隔离级别

1
2
3
4
5
6
7
8
9
10
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10, 2)
);
 
INSERT INTO accounts (id, name, balance) VALUES (1, 'Alice', 1000.00);
 
-- 查看默认隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

  

 

默认mysql默认隔离级别就是可重复读(REPEATABLE-READ)

2. 事务1

事务1:将事务隔离级别改为读未提交,然后开启事务(事务未提交)、指向update操作。在该事务内此数据以及被修改为900.00(之前是1000.00)

1
2
3
4
5
6
7
8
9
10
11
-- 设置事务隔离级别为READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
-- 开启事务,但未提交
START TRANSACTION;
 
-- 修改账户余额
UPDATE accounts SET balance = 900.00 WHERE id = 1;
 
-- 在Session A中查看账户余额
SELECT * FROM accounts WHERE id = 1;

  

事务1修改事务隔离级别只是临时修改,在此会话内生效

 

3. 事务2:发生了脏读

事务2设置事务隔离级别为读未提交,然后查询事务1修改的数据,发现读取到事务1修改未提交的数据,此时发生了脏读。

1
2
3
4
-- 设置事务隔离级别为READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 在Session B中读取账户余额
SELECT * FROM accounts WHERE id = 1;

  

 事务2修改事务隔离级别只是临时修改,在此会话内生效

3. 事务3

 

 

事务3使用了mysql默认的事务隔离级别(可重复读),此隔离级别永远不会发生脏读,因此读取的数据是1000.00

最佳实践

1. 使用合适的事务隔离级别

MySQL支持多种事务隔离级别,包括READ UNCOMMITTED(最低级别,允许脏读)、READ COMMITTED、REPEATABLE READ和SERIALIZABLE(最高级别)。

要解决脏读问题,通常应该使用至少READ COMMITTED级别的隔离级别。默认情况下,MySQL使用REPEATABLE READ级别。

1
2
-- 设置事务隔离级别为READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

  

2. 使用事务

确保你的操作包含在事务中,这样可以确保一组相关的操作要么全部成功,要么全部失败。这有助于避免脏读问题。

1
2
3
4
5
6
7
8
-- 开始一个事务
START TRANSACTION;
 
-- 执行数据库操作
-- ...
 
-- 提交事务
COMMIT;

  

3. 使用锁定

如果需要,可以使用锁定来防止其他事务读取或修改数据,直到你完成操作。

1
2
3
4
5
6
7
-- 通过FOR UPDATE锁定行
SELECT * FROM table_name WHERE condition FOR UPDATE;
 
-- 通过LOCK TABLES锁定表
LOCK TABLES table_name WRITE;
-- 执行操作
UNLOCK TABLES;

  

4. 避免长时间持有锁

长时间持有锁可能会导致性能问题和死锁。因此,在事务中只有必要的时间内持有锁,并在不需要锁时释放它们。

 

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 设置事务隔离级别为READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- 开始一个事务
START TRANSACTION;
 
-- 查询数据
SELECT * FROM orders WHERE status = 'processing';
 
-- 进行一些更新操作
UPDATE orders SET status = 'completed' WHERE order_id = 123;
 
-- 提交事务
COMMIT;

  

 不可重复读

 1. 概念

MySQL的不可重复读(Non-Repeatable Read)是数据库隔离级别中的一种情况,它描述了在一个事务内,两次读取相同数据可能会得到不同的结果。这种现象可以在多个并发事务同时操作数据库时发生,其中一个事务修改了某一行的数据,而另一个事务在事务开始和结束之间读取了同一行的数据。这个问题通常在较低的隔离级别下(如Read Committed)更容易发生,因为它允许在一个事务未提交之前其他事务可以读取已修改的数据。

因为mysql默认隔离级别是可重复读,因此此问题默认不会发生。

2. 不可重复读演示

1. 事务1

1
2
3
4
5
6
-- 设置事务隔离级别为READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 开启事务
START TRANSACTION;
 
SELECT * FROM accounts WHERE id = 1

  

balance此时的值为1000.00

 

2. 事务2

1
2
3
4
5
6
7
8
9
10
-- 设置事务隔离级别为READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 开启事务
START TRANSACTION;
 
-- 更新账户余额
UPDATE accounts SET balance = 150.00 WHERE id = 1;
 
-- 提交事务
COMMIT;

  

把数据更新为150.00

3. 事务1

事务1,再次执行 SELECT * FROM accounts WHERE id = 1 命令,发现查询的结果不是1000.00,而是事务2更新后的值150.00.这样就导致同一个事务,两次读取的数据不一致即不可重复读问题。

 

 

如果事务隔离级别是可重复读,即使事务2把数据更新并提交了事务。事务1两次读取的数据也是一样的即2次读取的都是1000.00。

 

幻读

 

 

概念

幻读指的是在同一事务中,当事务在读取某个范围的数据行时,另一个事务在该范围内插入了新的数据行,导致第一个事务看到了一些它之前不存在的数据行,就像出现了幻觉一样,因此得名“幻读”。

 

幻读演示示例

事务1

事务1:设置事务隔离级别是读已提交,并开启事务查看accouts表数据,此时表数据只有2条

1
2
3
4
5
-- 设置事务隔离级别为READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- 第1次读取,在事务2执行插入操作之前
SELECT * FROM accounts

  

 

事务2

事务2执行插入操作

1
INSERT INTO accounts (id, name, balance) VALUES (3, 'Allen3', 888888.88);

  

 

3. 事务1

继续执行查询表数据操作,发现此时的数据从2条,变成了3条即发生了幻读。

1
SELECT * FROM accounts

  

 

最佳实践

使用更高的隔离级别比如可重复度、串行化。或者锁表(不建议)

 

posted @   Allen_Hao  阅读(836)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示