事务监控与死锁

• pg_stat_statements 统计语句执行历史。
• performance_schema 记录事件与事务。
• 用于事务与性能分析。

事务历史分析依赖统计工具。PostgreSQL 的 pg_stat_statements 扩展记录每条 SQL 语句的执行次数、总耗时、平均耗时、块读取等统计，可分析慢语句与高频语句，是性能分析的核心。MySQL 的 performance_schema 提供结构化的事件监控（等待、执行、锁、事务），其中的 events_transactions_current / events_transactions_history 记录事务的执行历史，可分析事务耗时、状态、关联的语句。配合 slow query log、information_schema 可全面分析事务。通过 pg_stat_statements 可定位高频/慢 SQL，通过 performance_schema 可细粒度分析事务与事件。二者是事务与性能历史分析的关键工具。

pg_stat_statements 统计语句，performance_schema 记录事务与事件。两者互补分析事务历史与性能。

• pg_locks 显示锁等待关系。
• lock_wait_timeout 控制锁等待超时。
• 构建等待图分析死锁。

事务等待图的监控：PostgreSQL 的 pg_locks 视图显示当前所有锁及其持有者/等待者，通过关联查询可构建锁等待图（哪个事务等待哪个事务的锁），识别锁等待链与潜在死锁。MySQL 的 performance_schema 的 data_locks 与 data_lock_waits 提供锁等待信息。lock_wait_timeout（MySQL）或 lock_timeout（PostgreSQL）控制等待锁的超时时间，超时后中止等待，避免无限阻塞。监控等待图可发现锁竞争、识别死锁的环。工程上，定期查询 pg_locks / data_lock_waits，分析等待关系，设置合理锁超时，是锁监控与死锁预防的核心。理解等待图是分析锁竞争与死锁的基础。

pg_locks/data_lock_waits 构建锁等待图，lock_timeout 控等待超时。监控等待图识别死锁与锁竞争。

• 回滚率 = 回滚事务数/总事务数。
• 高回滚率提示冲突或逻辑错误。
• 监控指标与告警。

事务回滚率（rollback rate）= 回滚事务数 / 总事务数。高回滚率通常意味着：事务冲突频繁（死锁、锁等待超时、序列化失败）、应用逻辑错误（异常未捕获导致回滚）、或重试浪费。监控方法：PostgreSQL 用 pg_stat_database 的 xact_commit/xact_rollback 计算回滚率；MySQL 用 performance_schema 或 status 变量（如 Com_rollback、Handler_rollback）统计。高回滚率是数据库健康的重要信号，应关注并告警（如阈值 > 5%）。治理：分析回滚原因（死锁、锁超时、业务异常），优化事务（减少锁冲突、统一加锁顺序、处理序列化失败重试）。回滚率监控是事务健康度与并发问题的晴雨表。

回滚率=回滚/总数，高回滚率提示冲突或异常。用 pg_stat_database/performance_schema 监控并告警。

• events_transactions_current/history。
• 记录事务状态、耗时、关联语句。
• 用于事务监控分析。

MySQL performance_schema 提供事务相关表：events_transactions_current（当前事务）、events_transactions_history（历史事务）、events_transactions_summary_by_user_by_event_name（按用户/事件汇总）。这些表记录事务的状态（STARTED、ACTIVE、COMMITTED、ROLLED BACK）、耗时（TIMER_WAIT）、关联的语句（NESTING_EVENT_ID）、线程等。用于分析：事务耗时、长事务、回滚事务、事务并发。配合 data_locks、data_lock_waits（锁表）可分析锁等待与死锁。performance_schema 的事务表是 MySQL 事务监控的核心数据源，比 information_schema.innodb_trx 更细粒度的性能视角。理解这些表能有效监控与分析事务。

performance_schema 的 events_transactions_* 表记录事务状态、耗时、关联语句，是事务监控核心。

• READ COMMITTED 基本禁用 Gap Lock。
• REPEATABLE READ 用 Next-Key Lock 防幻读。
• 同 SQL 因隔离级别加锁范围不同。

InnoDB 的加锁策略随隔离级别变化。READ COMMITTED（RC）下，锁定基本只针对记录（Record Lock），禁用 Gap Lock（间隙锁）与 Next-Key Lock，因为 RC 不要求防幻读（每语句快照），锁只保护当前语句扫描的行，范围更小、并发更高。REPEATABLE READ（RR）下，为防幻读，使用 Next-Key Lock（记录锁 + 间隙锁）锁定范围，包括命中记录及其间隙，防止其他事务在范围内插入，锁范围更大、并发更低。同一条 SELECT ... FOR UPDATE 在 RC 下只锁命中行，在 RR 下锁命中行+间隙，故加锁范围不同。这解释了为何隔离级别影响锁竞争与并发。此外 RR 下唯一索引等值命中退化仅记录锁，范围查询才用 Next-Key Lock。

RC 禁 Gap Lock 锁范围小，RR 用 Next-Key Lock 锁范围大。同 SQL 因隔离级别加锁范围不同，影响并发。

• 普通索引范围查询向后加锁直到第一个不满足条件的记录及其间隙。
• 唯一索引等值命中加记录锁。
• 唯一索引等值未命中加间隙锁。

InnoDB 在 RR 下，普通索引范围查询（如 WHERE idx_col > 10 AND idx_col < 20）的加锁规则：不仅锁命中范围的记录，还向后加锁直到第一个不满足条件的记录及其间隙（即锁定范围的右边界间隙），防止其他事务插入边界记录造成幻读。对唯一索引等值命中（如 WHERE unique_col = 5）：若命中，加记录锁（唯一索引等值命中只需防本行修改，无需间隙锁，因为唯一性保证不会插入重复值）；若未命中（如 unique_col=5 不存在）：加"间隙锁"锁定该值所在间隙，防止其他事务插入该值。因此差异：普通索引范围查询用 Next-Key Lock 锁范围+间隙；唯一索引等值命中只用记录锁；唯一索引等值未命中用间隙锁。不同场景加锁范围不同。

普通索引范围向后锁边界间隙，唯一索引等值命中只锁记录、未命中锁间隙。理解加锁规则差异是 InnoDB 锁分析核心。

• deadlock 段显示两个事务的持有锁与等待锁。
• 识别回滚方（被选为牺牲者）。
• 依据锁顺序设计索引与事务顺序。

SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段展示最近一次死锁：两个事务各自 (1) 的持有锁（持有的 row lock）与 (2) 等待的锁（waiting for），以及被回滚的牺牲者（InnoDB 选择回滚 undo 量较小的事务）。解读：根据两个事务的锁顺序，找出形成环的锁（如 T1 持 A 等 B，T2 持 B 等 A），确定冲突点。改造方案：一是索引设计——让加锁范围更精确（如用唯一索引/合适索引减少锁行数），或调整索引使并发操作按相同顺序加锁；二是事务顺序——统一事务对多个资源的加锁顺序（如都先锁 A 再锁 B），避免交叉加锁；三是缩短事务持锁时间，减少死锁窗口。依据死锁段的具体锁冲突，针对性地调整索引与事务顺序。

deadlock 段显示两事务持锁/等待/回滚方。据此分析锁环，用索引与统一加锁顺序改造防死锁。

• 意向锁标记表级意图。
• 表锁与行锁共存需意向锁。
• 加表锁先查意向锁避免逐行扫描。

InnoDB 引入意向锁（IS 共享意向锁、IX 排他意向锁）是为了让表锁与行锁高效共存。当事务在行上加了锁（S/X 锁），会在表上加意向锁（IS/IX），标记"该表有事务在加行锁"。这样其他事务要加表锁时，只需检查表的意向锁与表锁的兼容性，无需逐行扫描所有行来确认是否冲突。两级判定：加表锁时，先检查表上的意向锁（IS/IX）与已有表锁是否兼容，兼容则加表锁，不兼容则等待。意向锁的兼容矩阵：IS 与 IS/IX/S 兼容，IX 与 IS/IX 兼容但与 S/X 不兼容，S 与 S/IS 兼容，X 与任何不兼容。通过意向锁快速判定表锁可行性，避免逐行扫描，实现表锁与行锁高效共存。这是 InnoDB 锁管理的核心优化。

意向锁标记表级加锁意图，加表锁时查意向锁即可判定，避免逐行扫描。让表锁与行锁高效共存。

• NOWAIT 不等待，遇到锁立即失败。
• SKIP LOCKED 跳过已锁行。
• 应用：立即失败 vs 任务队列领取。

SELECT ... FOR UPDATE NOWAIT：当要加锁的行已被其他事务锁定时，不排队等待，立即返回错误（报锁等待超时/锁冲突），适合"立即失败快速返回"的场景（如高并发下快速识别冲突，避免阻塞）。SKIP LOCKED：当要加锁的行已被锁定时，跳过这些被锁的行，只返回并锁定未锁的行，适合"任务队列领取"场景（多个 worker 并发领取任务，SKIP LOCKED 让每个 worker 拿到不同任务，避免争抢同一批任务）。两者都避免排队等待：NOWAIT 立即失败，SKIP LOCKED 跳过锁行继续处理可用行。典型应用：NOWAIT 用于需要快速失败重试的并发控制；SKIP LOCKED 用于任务分发、消息队列、批量处理。理解两者差异对并发任务设计至关重要。

NOWAIT 立即失败快速返回，SKIP LOCKED 跳过锁行继续处理。NOWAIT 适合快速失败，SKIP LOCKED 适合任务队列。

• 用开始时间判断长事务。
• 结合锁监控。
• 监控告警与治理。

发现与监控长事务的工程实践：数据库层查询——PostgreSQL 查 pg_stat_activity（xact_start 判断事务开始时间）、pg_locks（关联锁信息）；MySQL 查 information_schema.innodb_trx（trx_started 判断开始时间、trx_state、trx_rows_locked）与 performance_schema。监控指标：事务开始时间距今超过阈值（如 60s）即为长事务；结合锁等待与持锁时间判断影响。工程实践：定期轮询（如每 30s）查询长事务，写入监控系统并告警；结合锁监控（pg_locks / data_lock_waits）识别持锁长事务；对空闲长事务可用 idle_in_transaction_session_timeout 自动清理；对长事务设置告警阈值与处理流程（通知、终止）。定期监控长事务是数据库稳定运行的必备实践。

用事务开始时间与锁信息识别长事务，定期轮询监控并告警，配合超时自动清理。这是长事务治理的工程实践。

• 半一致性读对不满足 WHERE 的已锁行提前释放锁。
• 减少锁冲突。
• 与语句级复制安全（binlog）关系。

半一致性读（semi-consistent read）是 InnoDB 在 READ COMMITTED 下的优化：当 UPDATE 语句扫描到某行已被其他事务锁定（X 锁）时，先读取该行的最新已提交版本，若该行不满足 UPDATE 的 WHERE 条件，则无需加锁，可提前释放锁继续扫描，从而减少锁冲突与等待。若满足 WHERE 条件，才需要等待锁。这样避免了"扫描到已锁行就阻塞"的浪费，减少锁冲突。与语句级复制安全的关系：RC 下 InnoDB 使用半一致性读，但为保证 binlog 语句级复制安全（语句级复制要求 UPDATE 结果与主库一致），需配合合适配置（如 binlog_format=ROW 或使用可重复执行语义）。半一致性读是 RC 提升并发、减少锁冲突的关键优化，但需注意与复制安全的配合。

半一致性读对不满足 WHERE 的已锁行提前释放锁，减少锁冲突。RC 用它提升并发，需配合复制安全。

• MySQL 回滚 undo 量较小的牺牲者。
• PostgreSQL 终止当前检测到死锁的后端，报 40P01。
• 应用重试。

死锁发生时，数据库需选择牺牲者回滚以解除死锁。MySQL InnoDB：选择回滚 undo 量较小（修改较少）的事务作为牺牲者，减少回滚代价，其余事务继续。PostgreSQL：检测到死锁时，终止当前执行检测到死锁的后端事务，返回错误（SQLSTATE 40P01 deadlock_detected），该事务回滚，其余事务继续。应用重试策略：无论 MySQL 还是 PostgreSQL，死锁被回滚的事务需应用捕获错误（MySQL 的 1213、PostgreSQL 的 40P01），回滚整个事务并重试。重试需：加退避（backoff）避免反复死锁；确保重试幂等；统一加锁顺序降低死锁概率；限制重试次数。死锁是不可避免的（只要并发），应用层重试是标准应对。

MySQL 回滚 undo 较小者，PostgreSQL 终止当前后端报 40P01。应用需捕获死锁错误并重试整个事务。

• 死锁四必要条件。
• 统一加锁顺序打破循环等待。
• 缩短持锁时间减少窗口。

死锁产生需满足四个必要条件：互斥（资源只能被一个事务独占）、持有并等待（事务持有一个资源同时等待另一个）、不可剥夺（资源不能被强行剥夺）、循环等待（多个事务形成等待环）。统一锁获取顺序：所有事务按相同的顺序获取锁（如都先锁 A 再锁 B），则不会形成交叉等待环，打破"循环等待"条件，从而避免死锁。缩短事务持锁时间：减少锁持有期间的时间窗口，降低形成"循环等待"的概率（锁释放快，等待窗口小），从而降低死锁概率但无法完全消除。因此降低死锁概率的方法是打破四条件之一：统一顺序打破循环等待，缩短持锁缩短窗口。理解四条件与对策的对应关系是死锁治理的核心。

死锁四条件：互斥、持有并等待、不可剥夺、循环等待。统一顺序打破循环等待，缩短持锁减少窗口。

• PostgreSQL 用 xmax 标记元组锁，无间隙锁。
• InnoDB 基于索引加锁（记录锁/间隙锁）。
• 本质差异：元组级 vs 索引范围级。

PostgreSQL 与 InnoDB 加锁机制本质不同。PostgreSQL 通过元组的 xmax 字段标记锁：当事务 UPDATE/DELETE 某行时，在该行的 xmax 写事务 ID，表示该行被该事务锁定/修改，其他事务通过检查 xmax 判断冲突。PostgreSQL 是元组级锁（基于堆元组），无间隙锁、无锁升级（2PL 锁不升级），靠 xmax + MVCC 处理并发。InnoDB 基于索引加锁：锁绑定在索引记录上（记录锁、间隙锁、Next-Key Lock），通过索引范围实现防幻读。本质差异：PostgreSQL 锁的是元组（数据行）本身，InnoDB 锁的是索引记录及范围。因此 PostgreSQL 无间隙锁（防幻读靠快照/EvalPlanQual），InnoDB 用间隙锁防幻读。理解差异有助于理解两数据库的并发模型。

PostgreSQL 用 xmax 锁元组，无间隙锁；InnoDB 基于索引加记录/间隙锁。本质是元组级 vs 索引范围级。

• pid、state、query、xact_start。
• 判断会话状态与事务。
• 用于监控。

pg_stat_activity 是 PostgreSQL 监控会话的关键视图，关键字段：pid（进程 ID）、datname（数据库）、state（会话状态：active 执行中、idle 空闲、idle in transaction 事务空闲、fastpath function call 等）、query（当前执行的 SQL）、query_start（查询开始时间）、xact_start（事务开始时间）、backend_start（后端启动时间）、wait_event_type/wait_event（等待事件）、client_addr（客户端地址）。通过 state 判断会话是否空闲或事务中，通过 xact_start 识别长事务，通过 query 定位慢查询，通过 wait_event 分析等待原因。pg_stat_activity 是 PostgreSQL 会话与事务监控的核心视图。

pg_stat_activity 的 state 判断状态、xact_start 识别长事务、query 定位慢查询、wait_event 分析等待。是监控核心。

• innodb_lock_wait_timeout 控锁等待超时。
• innodb_deadlock_detect 主动死锁检测。
• 大并发下检测开销大，何时关闭。

innodb_lock_wait_timeout 控制等待锁的超时时间（默认 50 秒），超时后中止等待并报错（锁等待超时错误），是锁等待的兜底。innodb_deadlock_detect（默认开启）主动检测死锁：当检测到死锁环时回滚牺牲者。二者配合：死锁检测主动解除死锁，锁等待超时作为兜底（若死锁检测被关闭或超时）。innodb_deadlock_detect 在大并发下开销较大：每次加锁都需检查等待图，高并发持锁/等待多时，检测开销显著。关闭时机：当业务明确避免死锁（如统一加锁顺序、无死锁风险）且锁等待超时可接受时，可关闭 innodb_deadlock_detect 以降低开销；但关闭后需依赖 innodb_lock_wait_timeout 处理死锁（等待超时回滚）。权衡：大并发、确无死锁时关闭；否则保持开启。理解两者配合对锁性能调优重要。

innodb_lock_wait_timeout 是锁等待兜底，innodb_deadlock_detect 主动检测死锁但大并发下开销大。权衡后决定是否关闭。

• 各指标的阈值与告警分级。
• 从趋势预判锁竞争。
• 告警分级（警告/严重）。

事务监控告警设计需设定阈值与分级。长事务：> 30s 警告、> 300s 严重（或按业务调整）；锁等待：等待时间 > 阈值警告，等待数增长严重；回滚率：> 2% 警告、> 5% 严重；死锁次数：单位时间死锁数上升警告、持续上升严重。告警分级：黄色（警告，需关注）与红色（严重，需立即处理）。从趋势预判锁竞争恶化：监控锁等待时间、死锁次数、回滚率的趋势曲线，若持续上升或周期性恶化，预示锁竞争加剧（可能因业务增长、索引缺失、事务过长），提前介入（优化索引、缩减事务、统一加锁顺序）。通过趋势分析而非单一阈值，可预判并预防锁竞争恶化。

各指标设阈值并分级告警，通过趋势分析（锁等待/死锁/回滚率上升）预判锁竞争恶化并提前优化。

• 事务开始时间、持锁时间、语句耗时。
• 拆解慢事务的瓶颈。
• 与慢查询日志配合。

定位慢事务需从事务粒度拆解：一是事务开始时间与总耗时（pg_stat_activity 的 xact_start、performance_schema 的事务表），判断事务是否过长；二是持锁时间（通过锁监控看事务持锁多久），判断锁是否成为瓶颈；三是事务内各语句耗时（将事务拆为多个语句，分析每语句耗时），定位慢语句。与慢查询日志配合：慢查询日志（PostgreSQL log_min_duration_statement、MySQL slow query log）记录慢语句，可关联事务内的慢语句，确定慢事务的瓶颈语句。拆解流程：先定位慢事务（开始时间+总耗时），再分析持锁时间与语句耗时，结合慢查询日志锁定慢语句，进而优化（索引、SQL 改写、事务拆分）。事务粒度拆解是慢事务优化的系统方法。

从开始时间、持锁时间、语句耗时拆解慢事务，配合慢查询日志定位慢语句，再针对性优化。

• retry with backoff 重试。
• 事务瘦身缩短持锁。
• 统一加锁顺序。

死锁的应用层应对：一是重试机制（retry with backoff）：捕获死锁错误（1213/40P01），回滚后重试整个事务，重试间隔加退避（如 50ms、100ms、200ms 指数退避），避免并发重试再次死锁，限制重试次数。二是事务瘦身：减少事务内操作、缩短持锁时间，降低死锁窗口。三是统一加锁顺序：所有事务按相同顺序访问资源（先 A 后 B），打破循环等待。重试幂等性保证：重试前确保事务可重复执行不产生重复副作用——用唯一键约束（重复插入报错）、业务幂等键（如订单号唯一）、或先查询后更新（条件更新）。幂等保证重试多次结果一致，避免重复扣款/重复下单。综合应用层重试、事务瘦身、统一顺序与幂等，是死锁治理的完整方案。

死锁应对：retry with backoff 重试、事务瘦身、统一加锁顺序；幂等用唯一键/幂等键保证重试安全。

• 主动死锁检测：持续检测死锁环。
• 锁等待超时：超时回滚。
• 各自优点与代价。

主动死锁检测与锁等待超时是处理死锁的两种策略。主动死锁检测（如 InnoDB 的 innodb_deadlock_detect、PostgreSQL 的死锁检测）：数据库持续检测锁等待图，发现死锁环时立即回滚牺牲者，响应快、死锁解除及时，但检测有开销（每次加锁检查等待图），大并发下开销大。锁等待超时（如 innodb_lock_wait_timeout、PostgreSQL lock_timeout）：事务等待锁超过时限则超时回滚，实现简单、无检测开销，但响应慢（需等超时）、可能误杀正常等待。对比：主动检测更快解除死锁但开销大；超时更简单但响应慢。策略选择：可同时启用（默认 InnoDB 两者都开），死锁检测优先，超时兜底；在确无死锁场景可关闭检测、依赖超时。理解两者权衡是死锁治理的参数设计基础。

主动检测快但开销大，超时简单但响应慢。两者配合（检测优先、超时兜底）是最佳实践。