事务隔离级别深度解析：PostgreSQL vs MySQL

事务隔离级别是数据库并发控制的核心概念，直接影响系统的数据一致性与并发性能。SQL 标准（SQL-92）定义了四种隔离级别，但 PostgreSQL 和 MySQL（InnoDB）在实现细节上存在显著差异，理解这些差异是写出正确、高效 SQL 的关键。

---

一、并发问题：为什么需要隔离级别？

在多事务并发执行时，可能出现以下四类问题：

1. 脏读（Dirty Read）

事务 A 读取了事务 B 尚未提交的数据。若 B 回滚，A 读到的就是”幻觉数据”。

-- 事务 B（未提交）
UPDATE accounts SET balance = 9999 WHERE id = 1;

-- 事务 A（脏读）
SELECT balance FROM accounts WHERE id = 1;  -- 读到 9999（B 未提交）

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

事务 A 两次读取同一行，期间事务 B 修改并提交了该行，导致两次结果不同。

-- 事务 A 第一次读
SELECT balance FROM accounts WHERE id = 1;  -- 1000

-- 事务 B 修改并提交
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- 事务 A 第二次读（同一事务内）
SELECT balance FROM accounts WHERE id = 1;  -- 500（结果变了！）

3. 幻读（Phantom Read）

事务 A 两次执行范围查询，期间事务 B 插入了符合条件的新行，导致两次结果行数不同。

-- 事务 A 第一次查
SELECT * FROM orders WHERE amount > 1000;  -- 返回 5 行

-- 事务 B 插入并提交
INSERT INTO orders (amount) VALUES (2000);
COMMIT;

-- 事务 A 第二次查（同一事务内）
SELECT * FROM orders WHERE amount > 1000;  -- 返回 6 行（多了一行"幻行"）

4. 序列化异常（Serialization Anomaly）

多个事务并发执行的结果，无法等价于这些事务按某种顺序串行执行的结果。这是最高级别的并发问题，只有 SERIALIZABLE 能完全防止。

---

二、SQL 标准定义的四种隔离级别

隔离级别	脏读	不可重复读	幻读	序列化异常
READ UNCOMMITTED	✅ 可能	✅ 可能	✅ 可能	✅ 可能
READ COMMITTED	❌ 防止	✅ 可能	✅ 可能	✅ 可能
REPEATABLE READ	❌ 防止	❌ 防止	✅ 可能	✅ 可能
SERIALIZABLE	❌ 防止	❌ 防止	❌ 防止	❌ 防止

✅ = 该问题可能发生    ❌ = 该问题被防止

---

三、PostgreSQL 的隔离级别实现

PostgreSQL 基于 MVCC（多版本并发控制） 实现隔离，每个事务看到的是数据的一个一致性快照，读操作不阻塞写操作，写操作不阻塞读操作。

默认隔离级别：READ COMMITTED

-- 查看当前隔离级别
SHOW transaction_isolation;      -- 会话级
SHOW default_transaction_isolation;  -- 数据库默认

PostgreSQL 的四个级别行为如下：

隔离级别	脏读	不可重复读	幻读	序列化异常
READ UNCOMMITTED	❌ 防止¹	✅ 可能	✅ 可能	✅ 可能
READ COMMITTED（默认）	❌ 防止	✅ 可能	✅ 可能	✅ 可能
REPEATABLE READ	❌ 防止	❌ 防止	❌ 防止²	✅ 可能
SERIALIZABLE	❌ 防止	❌ 防止	❌ 防止	❌ 防止

¹ PostgreSQL 不真正支持 READ UNCOMMITTED，设置后实际按 READ COMMITTED 处理。
² PostgreSQL 的 REPEATABLE READ 额外防止了幻读，这比 SQL 标准要求更严格。

各级别详解

READ COMMITTED

每条 SQL 语句看到的是语句开始时已提交的数据快照。

BEGIN;  -- 默认 READ COMMITTED
SELECT balance FROM accounts WHERE id = 1;  -- 读到 1000

-- 此时另一事务提交了修改，balance 变为 800

SELECT balance FROM accounts WHERE id = 1;  -- 读到 800（不可重复读！）
COMMIT;

REPEATABLE READ

整个事务看到的是事务开始时的数据快照，且 PostgreSQL 实现中额外防止了幻读。

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;  -- 读到 1000

-- 另一事务修改 balance 为 800 并提交

SELECT balance FROM accounts WHERE id = 1;  -- 仍然读到 1000（快照固定）

-- 幻读也被防止
SELECT * FROM orders WHERE amount > 500;  -- 结果行数不会因其他事务插入而改变
COMMIT;

⚠️ 注意：在 REPEATABLE READ 下，若尝试更新另一事务已修改的行，PostgreSQL 会检测到写-写冲突并报错：

ERROR: could not serialize access due to concurrent update

SERIALIZABLE

基于 SSI（Serializable Snapshot Isolation） 算法，检测序列化异常并在检测到危险模式时中止事务，抛出错误码 40001。

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- 执行业务逻辑...

COMMIT;
-- 若存在序列化冲突，COMMIT 时会报：
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- SQLSTATE: 40001

应用层必须实现重试逻辑：

function withSerializable(PDO $pdo, callable $fn): mixed
{
    for ($attempt = 0; $attempt < 3; $attempt++) {
        try {
            $pdo->exec('BEGIN ISOLATION LEVEL SERIALIZABLE');
            $result = $fn($pdo);
            $pdo->exec('COMMIT');
            return $result;
        } catch (PDOException $e) {
            $pdo->exec('ROLLBACK');
            if ($e->getCode() !== '40001' || $attempt === 2) {
                throw $e;
            }
            usleep((2 ** $attempt) * 50_000);  // 指数退避：50ms, 100ms, 200ms
        }
    }
}

---

四、MySQL（InnoDB）的隔离级别实现

MySQL InnoDB 同样使用 MVCC，但在锁机制上有所不同，尤其在 REPEATABLE READ 级别下依赖间隙锁（Gap Lock） 防止幻读。

默认隔离级别：REPEATABLE READ

-- 查看当前隔离级别
SELECT @@transaction_isolation;           -- MySQL 8.0+
SELECT @@tx_isolation;                    -- MySQL 5.7 及以下
SHOW VARIABLES LIKE 'transaction_isolation';

MySQL 四个级别行为如下：

隔离级别	脏读	不可重复读	幻读	备注
READ UNCOMMITTED	✅ 可能	✅ 可能	✅ 可能	几乎不使用
READ COMMITTED	❌ 防止	✅ 可能	✅ 可能	常用于高并发写入
REPEATABLE READ（默认）	❌ 防止	❌ 防止	⚠️ 部分防止³	InnoDB 默认
SERIALIZABLE	❌ 防止	❌ 防止	❌ 防止	所有 SELECT 加共享锁

³ MySQL REPEATABLE READ 下，普通 SELECT（快照读）不会产生幻读，但当前读（SELECT ... FOR UPDATE / SELECT ... LOCK IN SHARE MODE）需要间隙锁才能防止幻读。

MySQL 的锁机制补充

MySQL 在 REPEATABLE READ 下通过三种锁防止幻读：

-- 1. 记录锁（Record Lock）：锁定具体行
SELECT * FROM orders WHERE id = 1 FOR UPDATE;

-- 2. 间隙锁（Gap Lock）：锁定索引间隙，防止插入
-- 若 id 存在 1, 5, 10，查询 id BETWEEN 3 AND 7
-- 会锁定 (1,5] 和 (5,10] 的间隙，防止其他事务插入 id=3,4,6,7 的行
SELECT * FROM orders WHERE id BETWEEN 3 AND 7 FOR UPDATE;

-- 3. 临键锁（Next-Key Lock）= 记录锁 + 间隙锁
-- 这是 InnoDB 默认的行锁策略

⚠️ 间隙锁在高并发写入场景容易造成死锁，可通过将隔离级别改为 READ COMMITTED 来禁用间隙锁：

-- 会话级别修改（适合高并发写入场景）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

---

五、PostgreSQL vs MySQL 核心差异对比

差异总览

对比维度	PostgreSQL	MySQL (InnoDB)
默认隔离级别	READ COMMITTED	REPEATABLE READ
REPEATABLE READ 防幻读	✅ 是（MVCC 快照）	⚠️ 部分（需加锁）
READ UNCOMMITTED	实际按 READ COMMITTED 处理	真正允许脏读
SERIALIZABLE 实现	SSI（乐观，可能失败重试）	悲观锁（SELECT 加共享锁）
幻读防止机制	MVCC 快照	间隙锁（Gap Lock）
写-写冲突处理	后写者等待或失败	后写者等待（行锁）
锁粒度	行级 + 表级	行级（记录锁/间隙锁/临键锁）

关键差异详解

差异一：REPEATABLE READ 对幻读的处理

这是最容易踩坑的差异点。

-- ===== PostgreSQL REPEATABLE READ =====
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders WHERE amount > 500;  -- 返回 10

-- 另一事务 INSERT 了 amount=600 的行并提交

SELECT COUNT(*) FROM orders WHERE amount > 500;  -- 仍返回 10（快照隔离，幻读被防止）
COMMIT;

-- ===== MySQL REPEATABLE READ =====
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE amount > 500;  -- 返回 10（快照读，无幻读）

-- 另一事务 INSERT 了 amount=600 的行并提交

SELECT COUNT(*) FROM orders WHERE amount > 500;  -- 仍返回 10（快照读，无幻读）

-- 但！使用当前读：
SELECT COUNT(*) FROM orders WHERE amount > 500 FOR UPDATE;  -- 返回 11！（幻读出现）
COMMIT;

结论：MySQL 的 REPEATABLE READ 在当前读场景下不能防幻读，PostgreSQL 的 REPEATABLE READ 在所有场景都防幻读。

差异二：SERIALIZABLE 实现方式

-- PostgreSQL SERIALIZABLE：乐观策略，事务可以并发执行
-- 若检测到序列化冲突，在提交时报错（SQLSTATE 40001），需重试

-- MySQL SERIALIZABLE：悲观策略，所有 SELECT 自动转为加共享锁
-- SELECT * FROM t WHERE id = 1
-- 等价于：SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE
-- 大量共享锁会严重降低并发性能

差异三：写-写冲突处理

-- 场景：事务 A 和事务 B 同时要修改同一行

-- PostgreSQL REPEATABLE READ
-- 先到的事务正常执行，后到的事务：
-- 若等待前者提交后发现该行已被修改，则报错：
-- ERROR: could not serialize access due to concurrent update
-- 应用层需重试

-- MySQL REPEATABLE READ（行锁机制）
-- 先到的事务加行锁，后到的事务等待锁释放（最长等 innodb_lock_wait_timeout 秒）
-- 默认等待 50 秒，超时则报错：ERROR 1205: Lock wait timeout exceeded

---

六、实战选择指南

PostgreSQL 隔离级别选择

-- ✅ 场景 1：普通查询、报表统计（无需高一致性）
-- 使用默认 READ COMMITTED，无需设置
SELECT SUM(amount) FROM orders WHERE DATE(created_at) = CURRENT_DATE;

-- ✅ 场景 2：先查后写、账户余额操作（防不可重复读）
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM wallets WHERE user_id = 42;
UPDATE wallets SET balance = balance - 100 WHERE user_id = 42 AND balance >= 100;
COMMIT;

-- ✅ 场景 3：跨表一致性操作、转账（需完全隔离）
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE wallets SET balance = balance - 100 WHERE user_id = 1;
UPDATE wallets SET balance = balance + 100 WHERE user_id = 2;
COMMIT;
-- 记得应用层捕获 SQLSTATE 40001 并重试！

MySQL 隔离级别选择

-- ✅ 场景 1：高并发写入、日志记录（避免间隙锁死锁）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO access_logs (user_id, action, created_at) VALUES (?, ?, NOW());

-- ✅ 场景 2：通用 OLTP 业务（默认 REPEATABLE READ + 显式加锁）
START TRANSACTION;
SELECT balance FROM wallets WHERE user_id = 42 FOR UPDATE;  -- 加行锁
UPDATE wallets SET balance = balance - 100 WHERE user_id = 42 AND balance >= 100;
COMMIT;

-- ✅ 场景 3：严格防幻读（使用 FOR UPDATE 触发间隙锁）
START TRANSACTION;
SELECT * FROM orders WHERE amount > 500 FOR UPDATE;  -- 加间隙锁防幻读
-- 执行后续逻辑...
COMMIT;

---

七、常见误区与注意事项

误区一：认为提高隔离级别总是更安全

更高的隔离级别会增加锁竞争（MySQL）或事务失败重试率（PostgreSQL），在高并发系统中可能反而导致性能骤降。应按实际需求选择最低满足要求的隔离级别。

误区二：PostgreSQL 和 MySQL 的 REPEATABLE READ 等价

这是最常见的迁移陷阱！ 如上文所述，MySQL 的 REPEATABLE READ 在当前读下不防幻读，PostgreSQL 则完全防幻读。从 MySQL 迁移到 PostgreSQL 时需要重新评估隔离级别需求。

误区三：忽略序列化失败的重试

PostgreSQL SERIALIZABLE 隔离级别必须配合应用层重试逻辑，否则序列化冲突会直接返回错误给用户，导致业务失败。

误区四：在 MySQL 高并发写入时使用 REPEATABLE READ

MySQL REPEATABLE READ 下的间隙锁在并发 INSERT 时极易产生死锁，对于日志表、消息队列等高写入场景，推荐降级为 READ COMMITTED。

快速排查命令

-- ===== PostgreSQL =====
-- 查看当前隔离级别
SHOW transaction_isolation;

-- 查看所有正在等待锁的事务
SELECT pid, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- 查看锁详情
SELECT * FROM pg_locks WHERE NOT granted;

-- ===== MySQL =====
-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 查看正在等待的事务
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state = 'LOCK WAIT';

-- 查看锁等待详情（MySQL 8.0+）
SELECT * FROM performance_schema.data_lock_waits;

---

八、总结

	PostgreSQL	MySQL
默认级别	READ COMMITTED	REPEATABLE READ
推荐日常使用	READ COMMITTED	REPEATABLE READ
高并发写入	READ COMMITTED	READ COMMITTED
账务/余额操作	REPEATABLE READ	REPEATABLE READ + FOR UPDATE
严格金融场景	SERIALIZABLE（需重试）	SERIALIZABLE（性能差）
迁移注意点	RR 防幻读更彻底	当前读需额外加锁防幻读

💡 一句话总结：PostgreSQL 的 MVCC 快照在 REPEATABLE READ 就能防幻读，而 MySQL 需要 FOR UPDATE 间隙锁配合；PostgreSQL 的 SERIALIZABLE 是乐观的（可能失败重试），MySQL 是悲观的（加共享锁）。理解这两点，就能避免 90% 的隔离级别踩坑。

---

参考资料：PostgreSQL 官方文档 - 事务隔离 | MySQL 官方文档 - InnoDB 事务模型