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 表没有 MVCCT1 读到 200 →不可重复读有了 MVCCT1 始终读到 100它开始时的一致快照MVCC 的核心理念读不阻塞写写不阻塞读。通过维护数据的多个版本每个事务看到数据库在自己开始时刻的快照。2.2 PostgreSQL MVCCPG 使用元组版本链 XID 可见性判断。每行数据实际存储为多个元组版本tuple version-- PG 每行隐式字段通过 pageinspect 扩展查看CREATEEXTENSION pageinspect;SELECTlp,t_xmin,t_xmax,t_ctid,t_dataFROMheap_page_items(get_raw_page(users,0));关键系统列列含义t_xmin创建此版本的事务IDt_xmax删除此版本的事务ID0未删除t_cmin/cmax事务内的命令IDt_ctid指向自身(当前版本)或新版本的指针t_infomask位掩码提交状态、HINT位等可见性判断规则简化版defis_visible(tuple,snapshot):判断元组对当前快照是否可见# 规则1: 如果创建者就是当前事务 → 可见iftuple.t_xminsnapshot.current_xid:returnTrue# 规则2: 如果创建者已提交且在当前快照之前 → 可见iftuple.t_xmininsnapshot.committed_before:# 但需要检查是否被删除iftuple.t_xmax0ortuple.t_xmaxnotinsnapshot.committed_before:returnTrue# 规则3: 如果创建者已回滚 → 不可见# 规则4: 如果创建者正在进行中 → 不可见returnFalseUPDATE 的实际操作-- UPDATE users SET balance 200 WHERE id 1;-- PG 实际操作:-- 1. 找到旧元组 (id1, balance100, xmin100, xmax0)-- 2. 标记旧元组为删除 (xmax 当前事务ID)-- 3. 插入新元组 (id1, balance200, xmin当前事务ID, xmax0)-- 4. 更新旧元组的 t_ctid 指向新元组-- 结果: 一行有两个版本-- 旧: (xmin100, xmax105, ctid(0,2)) ← 对事务105之前的快照可见-- 新: (xmin105, xmax0, ctid(0,2)) ← 对事务105之后的快照可见VACUUM 的作用PG 的 UPDATE 不原地修改而是插入新版本 → 产生死元组。VACUUM 清理这些死元组并回收空间-- 查看表膨胀情况SELECTschemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup*100.0/(n_live_tupn_dead_tup1),2)ASdead_ratioFROMpg_stat_user_tablesWHEREn_dead_tup1000ORDERBYn_dead_tupDESC;-- 手动 VACUUMVACUUMANALYZEusers;-- 回收空间更新统计信息VACUUMFULLusers;-- 完全重写表独占锁生产慎用-- PG 13 支持 autovacuum 更激进默认已够用ALTERTABLEusersSET(autovacuum_vacuum_scale_factor0.01);2.3 MySQL InnoDB MVCCInnoDB 使用Undo Log ReadView方式-- InnoDB 行的隐藏列-- DB_TRX_ID: 6字节最后修改此行的事务ID-- DB_ROLL_PTR: 7字节指向 Undo Log 中上一个版本-- DB_ROW_ID: 6字节行ID当没有主键时-- 查看 InnoDB 事务状态SHOWENGINEINNODBSTATUS\G-- 关注 TRANSACTIONS 部分:-- MySQL thread id 42, OS thread handle 1402..., query id 123 localhost root-- 当前活跃事务列表 (ACTIVE 时间)ReadView 可见性判断classReadView:def__init__(self):self.m_ids[]# 创建快照时活跃的事务ID列表self.min_trx_id0# 活跃事务中的最小IDself.max_trx_id0# 下一个将要分配的事务IDself.creator_trx_id0# 创建此ReadView的事务IDdefis_visible(self,trx_id):判断 trx_id 修改的行对当前ReadView是否可见# 规则1: 如果是创建者自己修改的 → 可见iftrx_idself.creator_trx_id:returnTrue# 规则2: 如果修改者 最小活跃事务ID → 已提交可见iftrx_idself.min_trx_id:returnTrue# 规则3: 如果修改者 下一个事务ID → 在快照之后不可见iftrx_idself.max_trx_id:returnFalse# 规则4: 如果在活跃事务列表中 → 未提交不可见iftrx_idinself.m_ids:returnFalse# 其他情况 → 已提交可见returnTrue版本链遍历示例-- UPDATE users SET balance 200 WHERE id 1; -- Undo Log 链: -- 当前行: (balance200, TRX_ID105, ROLL_PTR →) -- Undo记录1: (balance100, TRX_ID100, ROLL_PTR →) -- Undo记录2: (balance50, TRX_ID95, ROLL_PTR → NULL) -- 事务108创建ReadView时 -- 活跃事务 [106, 107, 108], min106, max109 -- 遍历: 105106 → 已提交 → 取 TRX_ID105 的版本(balance200) -- 如果活跃事务[104,105,106], min104, max107: -- 遍历: 105在活跃中 → 不可见 → 沿ROLL_PTR找 → 100104 → 取 balance100三、事务隔离级别对比3.1 各隔离级别现象-- 测试环境准备 CREATETABLEaccounts(idSERIALPRIMARYKEY,balanceINTNOTNULLDEFAULT0);INSERTINTOaccountsVALUES(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 关键差异总结特性PostgreSQLMySQL InnoDB默认隔离级别READ COMMITTEDREPEATABLE READMVCC实现元组版本链XIDUndo LogReadView不可重复读(RC)会发生会发生不可重复读(RR)杜绝(快照隔离)杜绝(快照读取)幻读(RR)杜绝(SI)杜绝(Next-Key Lock)序列化异常可检测(SSI)需显式SERIALIZABLE垃圾回收VACUUMPurge线程(后台)回滚段膨胀需要VACUUMUndo表空间管理四、MySQL InnoDB 三锁详解4.1 行锁 (Record Lock)-- 锁住索引记录本身SELECT*FROMaccountsWHEREid1FORUPDATE;-- 在 id1 的聚簇索引记录上加 X 锁-- 查看锁等待SELECT*FROMperformance_schema.data_locks;SELECT*FROMperformance_schema.data_lock_waits;4.2 间隙锁 (Gap Lock)-- 表数据: id1,5,10-- 间隙: (-∞,1), (1,5), (5,10), (10,∞)-- 锁定 (5,10) 间隙SELECT*FROMaccountsWHEREid7FORUPDATE;-- 其他事务无法在 id5 和 id10 之间插入-- 间隙锁防止幻读的核心机制:-- 当前读范围即使没有匹配数据也锁定该间隙4.3 Next-Key Lock (行锁间隙锁)-- Next-Key Lock Record Lock Gap Lock-- 锁住 (5,10] 区间: 间隙(5,10) 记录10SELECT*FROMaccountsWHEREid8FORUPDATE;-- 在 RR 级别下:-- 锁定范围 (-∞,1], (1,5], (5,10]-- 即锁住了所有 id10 的记录及其间隙-- 生产案例: 为什么看似无害的查询锁住全表?-- 表 account(id主键, name无索引)-- SELECT * FROM account WHERE nameAlice FOR UPDATE;-- name无索引 → 全表扫描 → 所有行的Next-Key Lock → 全表锁定!-- 解决方法: 给name加索引4.4 实战死锁排查-- 制造死锁 -- Session 1: Session 2:-- BEGIN; BEGIN;-- UPDATE a SET v1 WHERE id1; UPDATE a SET v2 WHERE id2;-- UPDATE a SET v1 WHERE id2; ← 等待 ---- UPDATE a SET v2 WHERE id1; → DEADLOCK!-- 排查步骤 -- 1. 查看最新死锁SHOWENGINEINNODBSTATUS\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. 实时锁等待监控SELECTr.trx_idASwaiting_trx,r.trx_mysql_thread_idASwaiting_thread,r.trx_queryASwaiting_query,b.trx_idASblocking_trx,b.trx_mysql_thread_idASblocking_thread,b.trx_queryASblocking_query,TIMESTAMPDIFF(SECOND,r.trx_wait_started,NOW())ASwait_secondsFROMinformation_schema.innodb_lock_waits wJOINinformation_schema.innodb_trx rONw.requesting_trx_idr.trx_idJOINinformation_schema.innodb_trx bONw.blocking_trx_idb.trx_id;-- 3. 杀死阻塞事务生产慎用-- KILL ;-- 预防措施 -- 1. 保持事务短小精悍-- 2. 按固定顺序访问资源如按id升序-- 3. 使用乐观锁version字段替代悲观锁-- 4. 降低隔离级别到RC如业务允许-- 5. 添加合适的索引避免全表扫描锁升级五、分布式事务 2PC/3PC5.1 两阶段提交(2PC)-- PostgreSQL 中的两阶段提交-- 协调者: 分布式事务管理器-- 阶段1: PREPARE准备阶段-- 所有参与节点执行SQL但不提交PREPARETRANSACTIONtx_001;-- PG会将事务状态持久化到 pg_twophase 目录-- 即使崩溃重启也能恢复-- 阶段2: COMMIT提交阶段COMMITPREPAREDtx_001;-- 或回滚: ROLLBACK PREPARED tx_001;-- 查看待处理的2PC事务SELECT*FROMpg_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清理死元组SELECTpid,now()-xact_startASduration,queryFROMpg_stat_activityWHEREstateidle in transactionORDERBYdurationDESC;-- 设置 idle_in_transaction_session_timeoutALTERSYSTEMSETidle_in_transaction_session_timeout5min;-- 2. 监控膨胀SELECTschemaname,relname,pg_size_pretty(pg_total_relation_size(relid))ASsize,n_dead_tup,round(n_dead_tup*100.0/(n_live_tup1),1)ASdead_pctFROMpg_stat_user_tablesWHEREn_dead_tup1000;-- 3. 调整 autovacuumALTERTABLElarge_tableSET(autovacuum_vacuum_scale_factor0.01,-- 1%死元组就触发autovacuum_analyze_scale_factor0.005);6.2 MySQL InnoDB调优-- 1. 监控锁等待SELECT*FROMsys.innodb_lock_waits;-- 锁等待超过阈值告警SETGLOBALinnodb_lock_wait_timeout10;-- 秒-- 2. Undo表空间管理SELECTinnodb_undo_tablespaces;-- 独立Undo表空间数SELECTinnodb_max_undo_log_size;-- Undo日志最大大小-- 大事务导致Undo膨胀 → 可能堵塞Purge线程-- 3. 死锁检测SETGLOBALinnodb_deadlock_detectON;-- 默认开启-- 高并发场景可考虑关闭用锁超时代替七、总结问题PG 答案MySQL InnoDB 答案MVCC行版本在哪堆表中(标记xmax)Undo Log中(ROLL_PTR链)旧版本清理VACUUMPurge线程不可重复读RC发生/RR杜绝RC发生/RR杜绝幻读(RR级别)快照隔离杜绝Next-Key Lock杜绝默认隔离级别READ COMMITTEDREPEATABLE READ序列化异常SSI自动检测需SERIALIZABLE理解 MVCC 和锁机制才能真正写出高性能、无死锁的事务代码。
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 表没有 MVCCT1 读到 200 →不可重复读有了 MVCCT1 始终读到 100它开始时的一致快照MVCC 的核心理念读不阻塞写写不阻塞读。通过维护数据的多个版本每个事务看到数据库在自己开始时刻的快照。2.2 PostgreSQL MVCCPG 使用元组版本链 XID 可见性判断。每行数据实际存储为多个元组版本tuple version-- PG 每行隐式字段通过 pageinspect 扩展查看CREATEEXTENSION pageinspect;SELECTlp,t_xmin,t_xmax,t_ctid,t_dataFROMheap_page_items(get_raw_page(users,0));关键系统列列含义t_xmin创建此版本的事务IDt_xmax删除此版本的事务ID0未删除t_cmin/cmax事务内的命令IDt_ctid指向自身(当前版本)或新版本的指针t_infomask位掩码提交状态、HINT位等可见性判断规则简化版defis_visible(tuple,snapshot):判断元组对当前快照是否可见# 规则1: 如果创建者就是当前事务 → 可见iftuple.t_xminsnapshot.current_xid:returnTrue# 规则2: 如果创建者已提交且在当前快照之前 → 可见iftuple.t_xmininsnapshot.committed_before:# 但需要检查是否被删除iftuple.t_xmax0ortuple.t_xmaxnotinsnapshot.committed_before:returnTrue# 规则3: 如果创建者已回滚 → 不可见# 规则4: 如果创建者正在进行中 → 不可见returnFalseUPDATE 的实际操作-- UPDATE users SET balance 200 WHERE id 1;-- PG 实际操作:-- 1. 找到旧元组 (id1, balance100, xmin100, xmax0)-- 2. 标记旧元组为删除 (xmax 当前事务ID)-- 3. 插入新元组 (id1, balance200, xmin当前事务ID, xmax0)-- 4. 更新旧元组的 t_ctid 指向新元组-- 结果: 一行有两个版本-- 旧: (xmin100, xmax105, ctid(0,2)) ← 对事务105之前的快照可见-- 新: (xmin105, xmax0, ctid(0,2)) ← 对事务105之后的快照可见VACUUM 的作用PG 的 UPDATE 不原地修改而是插入新版本 → 产生死元组。VACUUM 清理这些死元组并回收空间-- 查看表膨胀情况SELECTschemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup*100.0/(n_live_tupn_dead_tup1),2)ASdead_ratioFROMpg_stat_user_tablesWHEREn_dead_tup1000ORDERBYn_dead_tupDESC;-- 手动 VACUUMVACUUMANALYZEusers;-- 回收空间更新统计信息VACUUMFULLusers;-- 完全重写表独占锁生产慎用-- PG 13 支持 autovacuum 更激进默认已够用ALTERTABLEusersSET(autovacuum_vacuum_scale_factor0.01);2.3 MySQL InnoDB MVCCInnoDB 使用Undo Log ReadView方式-- InnoDB 行的隐藏列-- DB_TRX_ID: 6字节最后修改此行的事务ID-- DB_ROLL_PTR: 7字节指向 Undo Log 中上一个版本-- DB_ROW_ID: 6字节行ID当没有主键时-- 查看 InnoDB 事务状态SHOWENGINEINNODBSTATUS\G-- 关注 TRANSACTIONS 部分:-- MySQL thread id 42, OS thread handle 1402..., query id 123 localhost root-- 当前活跃事务列表 (ACTIVE 时间)ReadView 可见性判断classReadView:def__init__(self):self.m_ids[]# 创建快照时活跃的事务ID列表self.min_trx_id0# 活跃事务中的最小IDself.max_trx_id0# 下一个将要分配的事务IDself.creator_trx_id0# 创建此ReadView的事务IDdefis_visible(self,trx_id):判断 trx_id 修改的行对当前ReadView是否可见# 规则1: 如果是创建者自己修改的 → 可见iftrx_idself.creator_trx_id:returnTrue# 规则2: 如果修改者 最小活跃事务ID → 已提交可见iftrx_idself.min_trx_id:returnTrue# 规则3: 如果修改者 下一个事务ID → 在快照之后不可见iftrx_idself.max_trx_id:returnFalse# 规则4: 如果在活跃事务列表中 → 未提交不可见iftrx_idinself.m_ids:returnFalse# 其他情况 → 已提交可见returnTrue版本链遍历示例-- UPDATE users SET balance 200 WHERE id 1; -- Undo Log 链: -- 当前行: (balance200, TRX_ID105, ROLL_PTR →) -- Undo记录1: (balance100, TRX_ID100, ROLL_PTR →) -- Undo记录2: (balance50, TRX_ID95, ROLL_PTR → NULL) -- 事务108创建ReadView时 -- 活跃事务 [106, 107, 108], min106, max109 -- 遍历: 105106 → 已提交 → 取 TRX_ID105 的版本(balance200) -- 如果活跃事务[104,105,106], min104, max107: -- 遍历: 105在活跃中 → 不可见 → 沿ROLL_PTR找 → 100104 → 取 balance100三、事务隔离级别对比3.1 各隔离级别现象-- 测试环境准备 CREATETABLEaccounts(idSERIALPRIMARYKEY,balanceINTNOTNULLDEFAULT0);INSERTINTOaccountsVALUES(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 关键差异总结特性PostgreSQLMySQL InnoDB默认隔离级别READ COMMITTEDREPEATABLE READMVCC实现元组版本链XIDUndo LogReadView不可重复读(RC)会发生会发生不可重复读(RR)杜绝(快照隔离)杜绝(快照读取)幻读(RR)杜绝(SI)杜绝(Next-Key Lock)序列化异常可检测(SSI)需显式SERIALIZABLE垃圾回收VACUUMPurge线程(后台)回滚段膨胀需要VACUUMUndo表空间管理四、MySQL InnoDB 三锁详解4.1 行锁 (Record Lock)-- 锁住索引记录本身SELECT*FROMaccountsWHEREid1FORUPDATE;-- 在 id1 的聚簇索引记录上加 X 锁-- 查看锁等待SELECT*FROMperformance_schema.data_locks;SELECT*FROMperformance_schema.data_lock_waits;4.2 间隙锁 (Gap Lock)-- 表数据: id1,5,10-- 间隙: (-∞,1), (1,5), (5,10), (10,∞)-- 锁定 (5,10) 间隙SELECT*FROMaccountsWHEREid7FORUPDATE;-- 其他事务无法在 id5 和 id10 之间插入-- 间隙锁防止幻读的核心机制:-- 当前读范围即使没有匹配数据也锁定该间隙4.3 Next-Key Lock (行锁间隙锁)-- Next-Key Lock Record Lock Gap Lock-- 锁住 (5,10] 区间: 间隙(5,10) 记录10SELECT*FROMaccountsWHEREid8FORUPDATE;-- 在 RR 级别下:-- 锁定范围 (-∞,1], (1,5], (5,10]-- 即锁住了所有 id10 的记录及其间隙-- 生产案例: 为什么看似无害的查询锁住全表?-- 表 account(id主键, name无索引)-- SELECT * FROM account WHERE nameAlice FOR UPDATE;-- name无索引 → 全表扫描 → 所有行的Next-Key Lock → 全表锁定!-- 解决方法: 给name加索引4.4 实战死锁排查-- 制造死锁 -- Session 1: Session 2:-- BEGIN; BEGIN;-- UPDATE a SET v1 WHERE id1; UPDATE a SET v2 WHERE id2;-- UPDATE a SET v1 WHERE id2; ← 等待 ---- UPDATE a SET v2 WHERE id1; → DEADLOCK!-- 排查步骤 -- 1. 查看最新死锁SHOWENGINEINNODBSTATUS\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. 实时锁等待监控SELECTr.trx_idASwaiting_trx,r.trx_mysql_thread_idASwaiting_thread,r.trx_queryASwaiting_query,b.trx_idASblocking_trx,b.trx_mysql_thread_idASblocking_thread,b.trx_queryASblocking_query,TIMESTAMPDIFF(SECOND,r.trx_wait_started,NOW())ASwait_secondsFROMinformation_schema.innodb_lock_waits wJOINinformation_schema.innodb_trx rONw.requesting_trx_idr.trx_idJOINinformation_schema.innodb_trx bONw.blocking_trx_idb.trx_id;-- 3. 杀死阻塞事务生产慎用-- KILL ;-- 预防措施 -- 1. 保持事务短小精悍-- 2. 按固定顺序访问资源如按id升序-- 3. 使用乐观锁version字段替代悲观锁-- 4. 降低隔离级别到RC如业务允许-- 5. 添加合适的索引避免全表扫描锁升级五、分布式事务 2PC/3PC5.1 两阶段提交(2PC)-- PostgreSQL 中的两阶段提交-- 协调者: 分布式事务管理器-- 阶段1: PREPARE准备阶段-- 所有参与节点执行SQL但不提交PREPARETRANSACTIONtx_001;-- PG会将事务状态持久化到 pg_twophase 目录-- 即使崩溃重启也能恢复-- 阶段2: COMMIT提交阶段COMMITPREPAREDtx_001;-- 或回滚: ROLLBACK PREPARED tx_001;-- 查看待处理的2PC事务SELECT*FROMpg_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清理死元组SELECTpid,now()-xact_startASduration,queryFROMpg_stat_activityWHEREstateidle in transactionORDERBYdurationDESC;-- 设置 idle_in_transaction_session_timeoutALTERSYSTEMSETidle_in_transaction_session_timeout5min;-- 2. 监控膨胀SELECTschemaname,relname,pg_size_pretty(pg_total_relation_size(relid))ASsize,n_dead_tup,round(n_dead_tup*100.0/(n_live_tup1),1)ASdead_pctFROMpg_stat_user_tablesWHEREn_dead_tup1000;-- 3. 调整 autovacuumALTERTABLElarge_tableSET(autovacuum_vacuum_scale_factor0.01,-- 1%死元组就触发autovacuum_analyze_scale_factor0.005);6.2 MySQL InnoDB调优-- 1. 监控锁等待SELECT*FROMsys.innodb_lock_waits;-- 锁等待超过阈值告警SETGLOBALinnodb_lock_wait_timeout10;-- 秒-- 2. Undo表空间管理SELECTinnodb_undo_tablespaces;-- 独立Undo表空间数SELECTinnodb_max_undo_log_size;-- Undo日志最大大小-- 大事务导致Undo膨胀 → 可能堵塞Purge线程-- 3. 死锁检测SETGLOBALinnodb_deadlock_detectON;-- 默认开启-- 高并发场景可考虑关闭用锁超时代替七、总结问题PG 答案MySQL InnoDB 答案MVCC行版本在哪堆表中(标记xmax)Undo Log中(ROLL_PTR链)旧版本清理VACUUMPurge线程不可重复读RC发生/RR杜绝RC发生/RR杜绝幻读(RR级别)快照隔离杜绝Next-Key Lock杜绝默认隔离级别READ COMMITTEDREPEATABLE READ序列化异常SSI自动检测需SERIALIZABLE理解 MVCC 和锁机制才能真正写出高性能、无死锁的事务代码。
)
