事务隔离级别

Nyarlathotep0113
0 阅读6分钟

在mysql中，如果有大量用户同时访问修改同一条数据，将会产生数据一致性的问题，本文来探讨mysql是如何解决这个问题的。

场景预设

CREATE DATABASE IF NOT EXISTS `isolation_lab`;
USE `isolation_lab`;

-- 创建银行账户表
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `version` INT DEFAULT 0
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO `account` (`name`, `balance`) VALUES
('张三', 1000.00),
('李四', 500.00),
('王五', 2000.00);

-- 创建交易记录表
DROP TABLE IF EXISTS `transaction_log`;
CREATE TABLE `transaction_log` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `from_account` VARCHAR(50),
    `to_account` VARCHAR(50),
    `amount` DECIMAL(10,2),
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

并发事务的三大问题

脏读

脏读就是读取到了其他事务尚未提交的数据，下面来创建一个脏读的情况 脏数据是指未提交的、不稳定的临时数据。

-- 实验：制造脏读场景
-- 需要两个独立的MySQL客户端会话（会话A和会话B）

-- ---------- 会话A ----------
-- 步骤1：设置隔离级别为READ UNCOMMITTED（允许脏读）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;

-- 步骤2：修改张三的余额（但不提交！）
UPDATE account SET balance = balance - 200 WHERE name = '张三';

-- ---------- 会话B ----------
-- 步骤3：同样设置为READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;

-- 步骤4：读取张三的余额（可以看到未提交的修改！）
SELECT balance FROM account WHERE name = '张三';
-- 结果：800.00（但会话A可能随时回滚！）

-- ---------- 会话A ----------
-- 步骤5：模拟异常，回滚事务
ROLLBACK;

-- ---------- 会话B ----------
-- 步骤6：再次读取
SELECT balance FROM account WHERE name = '张三';
-- 结果：1000.00（刚才读到的800是"脏数据"）

这种情况会导致会话B很可能根据读取到的800来做出一些决策，但是会话A又回滚数据了，导致会话B的后续决策(针对800)是对于实际的1000这个数据做的。

或者会话A插入一条新的数据，但未提交，会话B根据这条新数据做了操作，会话A回滚，导致业务逻辑完全错误。

不可重读

不可重读就是同一事务中多次读取同一数据，结果不一致

-- 重置数据
UPDATE account SET balance = 1000.00 WHERE name = '张三';

-- ---------- 会话A ----------
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- 第一次读取
SELECT balance FROM account WHERE name = '张三';
-- 结果：1000.00

-- ---------- 会话B ----------
-- 修改并提交
START TRANSACTION;
UPDATE account SET balance = 800.00 WHERE name = '张三';
COMMIT;

-- ---------- 会话A ----------
-- 第二次读取（在同一事务中！）
SELECT balance FROM account WHERE name = '张三';
-- 结果：800.00（与第一次读取不一致！）

-- 这对于需要一致性视图的业务是致命的
-- 比如：计算两次读取间的差值
COMMIT;

有人可能会很疑惑，同一事务中多次读取同一数据，结果不一致，是不可重读，那脏读不一样是在一个事务中可能读到不同的数据吗？

其本质区别是：

  1. 脏读是读取了事务未提交的数据，不可重读是读取了其他事务已经提交的数据 2.脏读的数据是临时的，可能被回滚，从未真正生效，不可重读的数据是真实的

幻读

幻读是同一事务中多次查询，返回的行数不一致

-- 实验：制造幻读场景
-- ---------- 会话A ----------
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- MySQL默认级别
START TRANSACTION;

-- 第一次查询：余额大于600的账户
SELECT COUNT(*) FROM account WHERE balance > 600;
-- 结果：2（张三1000，王五2000）

-- ---------- 会话B ----------
-- 插入新记录并提交
START TRANSACTION;
INSERT INTO account (name, balance) VALUES ('赵六', 700.00);
COMMIT;

-- ---------- 会话A ----------
-- 第二次查询
SELECT COUNT(*) FROM account WHERE balance > 600;
-- 结果：2（在REPEATABLE READ下看不到新插入的赵六）

-- 但尝试更新这些记录...
UPDATE account SET version = version + 1 WHERE balance > 600;
SELECT ROW_COUNT();  -- 影响了3行！包括赵六！

-- 再次查询
SELECT COUNT(*) FROM account WHERE balance > 600;
-- 结果：3！出现了"幻影行"
COMMIT;

这个结果非常的神奇，那么到底发生了什么，会产生这种情况？

因为在可重复读这个事务隔离级别中，具有快照读和当前读两种机制：

  1. 第一次使用select进行查询时，使用的是快照读，它读取的是事务开始的时候数据的快照，因此看不见之前由其他事务提交的新增事务。
  2. 在使用update语句进行更新时，因为要确保更新的数据是基于最新的、已经提交的状态，因此数据库必须进行当前读，因此update语句能够感知到其他事务新提交的那条记录。
  3. 并且，当数据库发现新记录是符合update中的where语句，就会对其也进行更新，还会将这条记录创建版本的trx_id（事务ID）设置为当前更新事务的ID。
  4. 第二次使用select进行查询时，依旧使用的是快照读，快照读的规则是：只读取在事务开始时就已经提交的数据版本，或者由本事务自身创建的数据版本，因此现在可以读取到新记录。

四种隔离级别

  1. 读未提交（READ UNCOMMITTED） - 性能最好，一致性最差
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 特点：
-- √ 不加锁读取
-- × 可能出现脏读、不可重复读、幻读
-- 使用场景：对数据一致性要求极低，如实时性要求极高的监控系统
  1. 读已提交（READ COMMITTED） - Oracle/PostgreSQL默认
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 特点：
-- √ 避免脏读
-- × 可能出现不可重复读、幻读
-- 实现原理：每次读取都生成新的ReadView
-- 使用场景：大多数OLTP系统
  1. 可重复读（REPEATABLE READ） - MySQL默认
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 特点：
-- √ 避免脏读、不可重复读
-- × 可能出现幻读（但InnoDB通过间隙锁基本解决）
-- 实现原理：事务开始时生成ReadView，整个事务期间使用同一视图
-- 使用场景：需要事务内数据一致性的场景
  1. 串行化（SERIALIZABLE） - 一致性最好，性能最差
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 特点：
-- √ 避免所有并发问题
-- × 完全串行执行，性能极低
-- 实现原理：所有读操作加共享锁，写操作加排他锁
-- 用场景：对一致性要求极高，如金融核心系统

隔离级别实际影响

READ UNCOMMITTED

-- 重置数据
UPDATE account SET balance = 1000.00 WHERE name = '张三';
UPDATE account SET balance = 500.00 WHERE name = '李四';
UPDATE account SET balance = 2000.00 WHERE name = '王五';
DELETE FROM account WHERE name = '赵六';

-- 会话A（批处理）：
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- 读取所有账户余额
SELECT SUM(balance) AS total_before FROM account;  -- 假设看到3500

-- 会话B（用户转账）：
START TRANSACTION;
UPDATE account SET balance = balance - 1000 WHERE name = '张三';
-- 还没提交！

- 会话A：继续批处理
UPDATE account SET balance = balance * 1.01;  -- 给所有人加1%利息
-- 问题：基于脏数据计算利息！
COMMIT;

-- 会话B：
ROLLBACK;  -- 转账失败，但利息已错误计算！

REPEATABLE READ

-- 重置数据
UPDATE account SET balance = 1000.00 WHERE name = '张三';
UPDATE account SET balance = 500.00 WHERE name = '李四';
UPDATE account SET balance = 2000.00 WHERE name = '王五';


-- 会话A（批处理）：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT SUM(balance) AS total_before FROM account;  -- 看到3500

-- 会话B（用户转账）：
START TRANSACTION;
UPDATE account SET balance = balance - 1000 WHERE name = '张三';
COMMIT;  -- 提交成功


-- 会话A：继续处理
SELECT SUM(balance) AS total_now FROM account;  
-- 仍然看到3500！保证了事务内一致性
UPDATE account SET balance = balance * 1.01;  -- 基于事务开始时的一致性视图
COMMIT;  -- 提交后，其他事务看到更新
avatar
文章
阅读
粉丝