MVCC与数据库事务深层对比：PostgreSQL vs MySQL InnoDB

一、引言

数据库事务是后端开发的基石。但"事务"远不止 ACID 四个字母——PostgreSQL 的快照隔离（SSI）和 MySQL InnoDB 的 MVCC 实现完全不同，导致相同 SQL 在不同数据库中可能产生不同结果。

本文将深入对比两大主流数据库的事务实现：从 MVCC 的行版本链到底层存储结构，从锁机制（行锁/间隙锁/Next-Key Lock）到分布式事务 2PC，再到实战死锁排查。读完你会理解：为什么 PG 不会发生"不可重复读"而 MySQL 默认会？为什么 MySQL 的 SELECT ... FOR UPDATE 可能锁住不存在的行？

二、MVCC 核心原理

2.1 为什么需要MVCC

假设两个事务同时进行：

时刻1: T1 开始读 users 表 (balance = 100)
时刻2: T2 更新 balance = 200
时刻3: T1 再次读 users 表

没有 MVCC：T1 读到 200 → 不可重复读
有了 MVCC：T1 始终读到 100（它开始时的一致快照）

MVCC 的核心理念：读不阻塞写，写不阻塞读。通过维护数据的多个版本，每个事务看到数据库在自己开始时刻的快照。

2.2 PostgreSQL MVCC

PG 使用元组版本链 + XID 可见性判断。每行数据实际存储为多个"元组版本"（tuple version）：

-- PG 每行隐式字段（通过 pageinspect 扩展查看）
CREATE EXTENSION pageinspect;

SELECT lp, t_xmin, t_xmax, t_ctid, t_data 
FROM heap_page_items(get_raw_page('users', 0));

关键系统列：

列	含义
t_xmin	创建此版本的事务ID
t_xmax	删除此版本的事务ID（0=未删除）
t_cmin/cmax	事务内的命令ID
t_ctid	指向自身(当前版本)或新版本的指针
t_infomask	位掩码（提交状态、HINT位等）

可见性判断规则（简化版）：

def is_visible(tuple, snapshot):
    """判断元组对当前快照是否可见"""
    # 规则1: 如果创建者就是当前事务 → 可见
    if tuple.t_xmin == snapshot.current_xid:
        return True
    
    # 规则2: 如果创建者已提交且在当前快照之前 → 可见
    if tuple.t_xmin in snapshot.committed_before:
        # 但需要检查是否被删除
        if tuple.t_xmax == 0 or tuple.t_xmax not in snapshot.committed_before:
            return True
    
    # 规则3: 如果创建者已回滚 → 不可见
    # 规则4: 如果创建者正在进行中 → 不可见
    return False

UPDATE 的实际操作：

-- UPDATE users SET balance = 200 WHERE id = 1;

-- PG 实际操作:
-- 1. 找到旧元组 (id=1, balance=100, xmin=100, xmax=0)
-- 2. 标记旧元组为删除 (xmax = 当前事务ID)
-- 3. 插入新元组   (id=1, balance=200, xmin=当前事务ID, xmax=0)
-- 4. 更新旧元组的 t_ctid 指向新元组

-- 结果: 一行有两个版本
-- 旧: (xmin=100, xmax=105, ctid=(0,2))  ← 对事务105之前的快照可见
-- 新: (xmin=105, xmax=0, ctid=(0,2))    ← 对事务105之后的快照可见

VACUUM 的作用：

PG 的 UPDATE 不原地修改，而是插入新版本 → 产生"死元组"。VACUUM 清理这些死元组并回收空间：

-- 查看表膨胀情况
SELECT schemaname, relname, 
       n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup + 1), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 手动 VACUUM
VACUUM ANALYZE users;           -- 回收空间+更新统计信息
VACUUM FULL users;              -- 完全重写表（独占锁！生产慎用）

-- PG 13+ 支持 autovacuum 更激进（默认已够用）
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.01);

2.3 MySQL InnoDB MVCC

InnoDB 使用 Undo Log + ReadView 方式：

-- InnoDB 行的隐藏列
-- DB_TRX_ID:  6字节，最后修改此行的事务ID
-- DB_ROLL_PTR: 7字节，指向 Undo Log 中上一个版本
-- DB_ROW_ID:   6字节，行ID（当没有主键时）

-- 查看 InnoDB 事务状态
SHOW ENGINE INNODB STATUS\G
-- 关注 TRANSACTIONS 部分:
-- MySQL thread id 42, OS thread handle 1402..., query id 123 localhost root
-- 当前活跃事务列表 (ACTIVE 时间)

ReadView 可见性判断：

class ReadView:
    def __init__(self):
        self.m_ids = []          # 创建快照时活跃的事务ID列表
        self.min_trx_id = 0      # 活跃事务中的最小ID
        self.max_trx_id = 0      # 下一个将要分配的事务ID
        self.creator_trx_id = 0  # 创建此ReadView的事务ID
    
    def is_visible(self, trx_id):
        """判断 trx_id 修改的行对当前ReadView是否可见"""
        # 规则1: 如果是创建者自己修改的 → 可见
        if trx_id == self.creator_trx_id:
            return True
        
        # 规则2: 如果修改者 < 最小活跃事务ID → 已提交，可见
        if trx_id < self.min_trx_id:
            return True
        
        # 规则3: 如果修改者 >= 下一个事务ID → 在快照之后，不可见
        if trx_id >= self.max_trx_id:
            return False
        
        # 规则4: 如果在活跃事务列表中 → 未提交，不可见
        if trx_id in self.m_ids:
            return False
        
        # 其他情况 → 已提交，可见
        return True

版本链遍历示例：

-- UPDATE users SET balance = 200 WHERE id = 1;

-- Undo Log 链:
-- 当前行: (balance=200, TRX_ID=105, ROLL_PTR →)
-- Undo记录1: (balance=100, TRX_ID=100, ROLL_PTR →)
-- Undo记录2: (balance=50,  TRX_ID=95,  ROLL_PTR → NULL)

-- 事务108创建ReadView时：
-- 活跃事务 = [106, 107, 108], min=106, max=109
-- 遍历: 105<106 → 已提交 → 取 TRX_ID=105 的版本(balance=200)
-- 如果活跃事务=[104,105,106], min=104, max=107:
--   遍历: 105在活跃中 → 不可见 → 沿ROLL_PTR找 → 100<104 → 取 balance=100

三、事务隔离级别对比

3.1 各隔离级别现象

-- ========== 测试环境准备 ==========
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance INT NOT NULL DEFAULT 0
);
INSERT INTO accounts VALUES (1, 100), (2, 200);

-- ========== 脏读测试 ==========
-- T1: BEGIN; UPDATE accounts SET balance = 150 WHERE id = 1;
-- T2: SELECT balance FROM accounts WHERE id = 1;  
--     PG任何级别: 100 (绝不发生脏读)
--     MySQL READ UNCOMMITTED: 150 (唯一可能脏读的级别)
-- T1: ROLLBACK;

-- ========== 不可重复读测试 ==========
-- T1: BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- T1: SELECT balance FROM accounts WHERE id = 1;  -- 100
-- T2: UPDATE accounts SET balance = 200 WHERE id = 1; COMMIT;
-- T1: SELECT balance FROM accounts WHERE id = 1;
--     PG READ COMMITTED: 200 (不可重复读发生了!)
--     PG REPEATABLE READ: 100 (快照隔离)
--     MySQL REPEATABLE READ: 100 (默认级别,快照隔离)

-- ========== 幻读测试 ==========
-- T1: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- T1: SELECT count(*) FROM accounts WHERE balance > 100;  -- 1
-- T2: INSERT INTO accounts VALUES (3, 300); COMMIT;
-- T1: SELECT count(*) FROM accounts WHERE balance > 100;
--     PG REPEATABLE READ: 1 (无幻读，SI级别杜绝)
--     MySQL REPEATABLE READ: 1 (无幻读，Next-Key Lock阻止)
--     PG READ COMMITTED: 2 (幻读)

3.2 关键差异总结

特性	PostgreSQL	MySQL InnoDB
默认隔离级别	READ COMMITTED	REPEATABLE READ
MVCC实现	元组版本链+XID	Undo Log+ReadView
不可重复读(RC)	会发生	会发生
不可重复读(RR)	杜绝(快照隔离)	杜绝(快照读取)
幻读(RR)	杜绝(SI)	杜绝(Next-Key Lock)
序列化异常	可检测(SSI)	需显式SERIALIZABLE
垃圾回收	VACUUM	Purge线程(后台)
回滚段膨胀	需要VACUUM	Undo表空间管理

四、MySQL InnoDB 三锁详解

4.1 行锁 (Record Lock)

-- 锁住索引记录本身
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 在 id=1 的聚簇索引记录上加 X 锁

-- 查看锁等待
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

4.2 间隙锁 (Gap Lock)

-- 表数据: id=1,5,10
-- 间隙: (-∞,1), (1,5), (5,10), (10,+∞)

-- 锁定 (5,10) 间隙
SELECT * FROM accounts WHERE id = 7 FOR UPDATE;
-- 其他事务无法在 id=5 和 id=10 之间插入

-- 间隙锁防止幻读的核心机制:
-- 当前读范围即使没有匹配数据，也锁定该间隙

4.3 Next-Key Lock (行锁+间隙锁)

-- Next-Key Lock = Record Lock + Gap Lock
-- 锁住 (5,10] 区间: 间隙(5,10) + 记录10

SELECT * FROM accounts WHERE id <= 8 FOR UPDATE;
-- 在 RR 级别下:
-- 锁定范围 (-∞,1], (1,5], (5,10]
-- 即锁住了所有 id<=10 的记录及其间隙

-- 生产案例: 为什么看似无害的查询锁住全表?
-- 表 account(id主键, name无索引) 
-- SELECT * FROM account WHERE name='Alice' FOR UPDATE;
-- name无索引 → 全表扫描 → 所有行的Next-Key Lock → 全表锁定!
-- 解决方法: 给name加索引

4.4 实战：死锁排查

-- ========== 制造死锁 ==========
-- Session 1:                           Session 2:
-- BEGIN;                               BEGIN;
-- UPDATE a SET v=1 WHERE id=1;         UPDATE a SET v=2 WHERE id=2;
-- UPDATE a SET v=1 WHERE id=2;  ← 等待  --
--                                      UPDATE a SET v=2 WHERE id=1; → DEADLOCK!

-- ========== 排查步骤 ==========
-- 1. 查看最新死锁
SHOW ENGINE INNODB STATUS\G
-- 滚动到 LATEST DETECTED DEADLOCK 部分
-- 关键信息:
--   *** (1) TRANSACTION: 第一个事务的SQL
--   *** (1) HOLDS THE LOCK(S): 持有锁
--   *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 等待锁
--   *** (2) TRANSACTION: 第二个事务
--   *** WE ROLL BACK TRANSACTION (2): 回滚了哪个

-- 2. 实时锁等待监控
SELECT 
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

-- 3. 杀死阻塞事务（生产慎用）
-- KILL ;

-- ========== 预防措施 ==========
-- 1. 保持事务短小精悍
-- 2. 按固定顺序访问资源（如按id升序）
-- 3. 使用乐观锁（version字段）替代悲观锁
-- 4. 降低隔离级别到RC（如业务允许）
-- 5. 添加合适的索引，避免全表扫描锁升级

五、分布式事务 2PC/3PC

5.1 两阶段提交(2PC)

-- PostgreSQL 中的两阶段提交
-- 协调者: 分布式事务管理器

-- 阶段1: PREPARE（准备阶段）
-- 所有参与节点执行SQL但不提交
PREPARE TRANSACTION 'tx_001';
-- PG会将事务状态持久化到 pg_twophase 目录
-- 即使崩溃重启也能恢复

-- 阶段2: COMMIT（提交阶段）
COMMIT PREPARED 'tx_001';
-- 或回滚: ROLLBACK PREPARED 'tx_001';

-- 查看待处理的2PC事务
SELECT * FROM pg_prepared_xacts;

5.2 2PC的可用性问题

问题: 协调者崩溃后，参与者不知该提交还是回滚 → 锁一直持有

                     协调者
                   /    |    \
          参与者1    参与者2    参与者3
          
          时间线:
          T1: 协调者发送 PREPARE → 所有参与者回复 YES
          T2: 协调者写入 commit log → ★ 此时崩溃
          T3: 参与者1收到 COMMIT → 提交
          T4: ★ 参与者2和3永远收不到 COMMIT → 阻塞
          
          解决: 
          - 协调者重启后从log恢复，重发COMMIT
          - 参与者超时后主动询问协调者
          - 3PC引入预提交阶段(try-commit)

六、实战调优

6.1 PG事务调优

-- 1. 避免长事务（阻止VACUUM清理死元组）
SELECT pid, now()-xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY duration DESC;
-- 设置 idle_in_transaction_session_timeout
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';

-- 2. 监控膨胀
SELECT schemaname, relname, 
       pg_size_pretty(pg_total_relation_size(relid)) AS size,
       n_dead_tup, 
       round(n_dead_tup*100.0/(n_live_tup+1),1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000;

-- 3. 调整 autovacuum
ALTER TABLE large_table SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 1%死元组就触发
    autovacuum_analyze_scale_factor = 0.005
);

6.2 MySQL InnoDB调优

-- 1. 监控锁等待
SELECT * FROM sys.innodb_lock_waits;
-- 锁等待超过阈值告警
SET GLOBAL innodb_lock_wait_timeout = 10;  -- 秒

-- 2. Undo表空间管理
SELECT @@innodb_undo_tablespaces;  -- 独立Undo表空间数
SELECT @@innodb_max_undo_log_size; -- Undo日志最大大小
-- 大事务导致Undo膨胀 → 可能堵塞Purge线程

-- 3. 死锁检测
SET GLOBAL innodb_deadlock_detect = ON;  -- 默认开启
-- 高并发场景可考虑关闭（用锁超时代替）

七、总结

问题	PG 答案	MySQL InnoDB 答案
MVCC行版本在哪	堆表中(标记xmax)	Undo Log中(ROLL_PTR链)
旧版本清理	VACUUM	Purge线程
不可重复读	RC发生/RR杜绝	RC发生/RR杜绝
幻读(RR级别)	快照隔离杜绝	Next-Key Lock杜绝
默认隔离级别	READ COMMITTED	REPEATABLE READ
序列化异常	SSI自动检测	需SERIALIZABLE

理解 MVCC 和锁机制，才能真正写出高性能、无死锁的事务代码。