咨询锁与锁超时与行锁升级

• innodb_lock_wait_timeout 的作用
• 锁等待超时
• 超时后的行为

innodb_lock_wait_timeout 用于控制 InnoDB 事务等待行锁的超时时间（默认 50 秒）。当一个事务等待其他事务持有的行锁并超过该时间时，会抛出"Lock wait timeout exceeded"错误，回滚当前等待的语句（但不会回滚整个事务）。它的作用是避免事务无限期等待锁而导致系统卡死，让锁等待有一个明确的上限。实际调优中，若并发锁冲突频繁，可适当调小该值使冲突快速暴露，或调大以满足耐心等待的场景。它与死锁检测（deadlock detection）是两种不同的机制，前者是超时，后者是主动检测并回滚。

innodb_lock_wait_timeout 是锁等待的"安全阀"，防止无期限等待。理解它与死锁检测、整个事务回滚（需 innodb_rollback_on_timeout）的区别，是配置数据库锁行为的关键。

• pg_advisory_lock 阻塞式获取
• pg_try_advisory_lock 非阻塞式获取
• 使用场景

PostgreSQL 提供 advisory lock 函数：pg_advisory_lock(key) 会阻塞式获取锁，直到拿到锁才返回；pg_try_advisory_lock(key) 是非阻塞式，若锁已被占用则立即返回 false，不等待。还有 pg_advisory_unlock(key) 释放锁。key 可以是 int 或 bigint。阻塞式适合"必须拿到锁才继续"的场景，非阻塞式适合"抢不到就放弃/走其他逻辑"的场景（如定时任务多实例只让一个执行）。此外还有事务级版本 pg_advisory_xact_lock、pg_try_advisory_xact_lock，随事务提交/回滚自动释放。

阻塞式与非阻塞式是 advisory lock 的核心分叉。掌握 pg_advisory_lock/pg_try_advisory_lock 及事务级变体，能灵活处理应用层的分布式协调需求。

• 死锁检测
• PostgreSQL 周期检测 + 回滚一方
• InnoDB 等待图检测 + 回滚代价小者

PostgreSQL 通过后台周期性地检测等待图中的循环来发现死锁，检测间隔由 deadlock_timeout 控制（默认 1 秒），发现死锁后回滚其中一个事务（通常是代价较小的一方），并抛出死锁错误。MySQL InnoDB 使用等待图（wait-for graph）算法检测死锁，当检测到循环等待时回滚代价较小的事务（基于 undo 大小等），并返回死锁错误。两者都会自动检测并终止死锁，但回滚对象的选择策略略有差异。死锁检测不需要人工干预，但可通过统一加锁顺序等方式减少死锁发生。

死锁检测是数据库自动处理死锁的机制。理解两者"检测算法 + 回滚一方"的共同点，以及检测周期/代价评估的差异，有助于分析死锁日志与优化并发。

• PostgreSQL 锁等待诊断
• MySQL 锁等待诊断
• 定位阻塞源

PostgreSQL 中，pg_locks 显示所有锁的信息（锁类型、锁模式、pid、relation 等），pg_stat_activity 显示会话状态与当前执行语句，两者 join 可以定位哪个会话持锁、哪个会话在等待。MySQL 中，INFORMATION_SCHEMA.INNODB_LOCK_WAITS（以及 performance_schema.data_lock_waits）显示锁等待关系，结合 INNODB_TRX 可定位阻塞事务。诊断锁等待的流程通常是：先看哪些语句处于等待状态，再通过锁视图找到持锁会话，最后决定等待或终止持锁会话。

锁等待诊断需要"会话视图 + 锁视图"结合。PostgreSQL 用 pg_locks + pg_stat_activity，MySQL 用 INNODB_LOCK_WAITS + INNODB_TRX，是定位锁阻塞问题的标准手段。

• pg_locks 的字段
• 分析锁等待与死锁
• 结合 MVCC 分析长事务

pg_locks 是 PostgreSQL 的锁视图，包含 locktype（relation、tuple、transactionid、advisory 等）、模式（AccessShareLock、ExclusiveLock 等）、pid、relation、mode、granted 等字段。在 MVCC 与锁范畴内，pg_locks 可用于：查看事务持有的行锁与表锁、分析锁等待（granted=false 表示在等待）、定位死锁相关会话、结合 pg_stat_activity 找出长事务（长事务既持锁又影响 MVCC 清理边界）。通过 pg_locks 可以判断一个事务是否阻塞了其他操作，以及锁的类型与来源。

pg_locks 是 PostgreSQL 锁状态的可视化入口。理解其字段与用法，能快速定位锁等待、死锁及长事务问题，是 MVCC 与锁调优的基础工具。

• 锁升级的概念
• InnoDB 不升级锁
• 与 Oracle 的差异

锁升级（Lock Escalation）是指数据库在单个事务持有大量行锁时，自动将其升级为表锁以减少锁开销。Oracle 和 SQL Server 支持锁升级，但 MySQL InnoDB 不支持自动锁升级：它始终按行粒度加锁，即使一个事务锁定了大量行，也不会合并成表锁。原因是 InnoDB 的行锁存储在内存锁表中，通过 hash 索引管理，开销相对可控；且不升级锁能避免"锁范围突然扩大导致并发骤降"的问题。InnoDB 通过锁内存的管理（如自适应哈希、锁分离）来应对大量行锁，而不是依赖升级。

不升级锁是 InnoDB 的并发设计选择：保持行锁粒度以维持高并发，靠内存管理应对大量锁。理解这一差异能解释为何 InnoDB 不会因锁升级而突然阻塞整个表。

• PostgreSQL 行锁粒度
• 仅锁被影响的行
• 无锁升级

PostgreSQL 的行锁作用于单行（元组），UPDATE/DELETE 会锁定实际被修改的行，SELECT ... FOR UPDATE 会锁定返回的行。PostgreSQL 的行锁粒度精确到行，且不会自动升级为表锁——即使一个事务锁定了大量行，也不会升级成表级锁。这与 InnoDB 类似。PostgreSQL 的行锁信息存储在元组的 xmax 字段及各行的锁信息中，通过事务状态判断。不升级锁维持了高并发，但大量行锁会占用较多内存与锁冲突开销。

PostgreSQL 坚持"行锁不升级"的粒度，保证并发粒度细小。理解这一点有助于解释为何长事务锁定大量行时虽不升级表锁，但会带来锁内存与死锁风险。

• 锁升级的概念
• 大量行锁升级为表锁
• 支持与不支持的数据库

行锁升级（Lock Escalation）是指数据库在检测到单个事务持有超过阈值数量的行锁时，自动将这些行锁合并升级为一个表锁，以减少锁的维护开销和内存占用。常见于 SQL Server 和 Oracle 等数据库。升级后锁粒度从行变为表，虽然减少了锁开销，但会显著扩大锁冲突范围，降低并发度。InnoDB 和 PostgreSQL 不支持锁升级，而是选择维持行锁粒度以保并发。锁升级的权衡在于"锁开销 vs 并发度"。

锁升级牺牲并发换取锁开销。支持锁升级的数据库（SQL Server/Oracle）在极端场景下会因表锁造成并发骤降；不支持升级的（InnoDB/PG）则靠内存管理维持高并发。理解这一权衡是并发设计的关键。

• deadlock_timeout 与 lock_timeout 的区别
• 死锁检测周期
• 检测开销

PostgreSQL 中，deadlock_timeout 控制死锁检测的周期（默认 1 秒），即后端的锁等待过程会周期性检查等待图以发现死锁；lock_timeout 控制单个锁等待的最大时长，超时后该语句放弃并报错。死锁检测采用周期触发而非每次锁等待都立即检测，是因为死锁检测需要扫描等待图，开销较大，若每次锁等待都立即检测会带来显著 CPU 开销。deadlock_timeout 默认 1 秒，兼顾了"及时检测"与"开销可控"。若间隔过小，等待图扫描频繁，会消耗大量 CPU 并影响高并发下的性能。

deadlock_timeout 是"检测周期"，lock_timeout 是"等待上限"，两者分工明确。死锁检测是周期性开销，需平衡检测及时性与开销，这就是为什么默认不设 0 或极小值。

• advisory lock 的典型用法
• 定时任务互斥、库存预占、分布式调度
• 与唯一索引、行锁的取舍

advisory lock 的典型用法：定时任务单实例执行（多个应用实例抢一把 advisory lock，只有抢到者执行，避免重复执行）；库存预占（抢锁后再操作库存，避免并发超卖）；分布式调度（在多个节点间协调任务执行）。相比唯一索引、行锁方案，advisory lock 的优势是灵活、不依赖具体数据表、可在应用层直接调用；缺点是锁不由业务数据约束，若代码忘记释放或实例崩溃可能长时间占用（需用会话级锁或设置超时）。而唯一索引/行锁方案直接把并发控制绑定到数据，更适合"数据本身作为竞争资源"的场景。

advisory lock 适合"应用层协调"，数据行锁/唯一索引适合"数据层约束"。选择取决于竞争资源是否映射到具体数据：映射到数据用行锁/唯一索引，纯应用协调用 advisory lock。

• locktype 各类型含义
• 结合 pg_stat_activity 定位
• 安全终止会话

pg_locks 的 locktype 字段表示锁类型：relation（表级锁）、tuple（元组/行锁）、transactionid（事务锁，用于事务等待）、advisory（咨询锁）、page（页锁）、virtualxid（虚拟事务 ID 锁）等。解读时，transactionid 锁常与事务等待相关，relation 锁是表级锁，advisory 是咨询锁。结合 pg_stat_activity 的 pid、state、query 字段，可以找到持有锁的会话及其执行的语句。安全终止时，通常先 pg_cancel_backend(pid) 取消正在执行的查询，若仍无法释放锁再 pg_terminate_backend(pid) 终止会话，避免直接 kill 造成数据不一致。

locktype 是锁类型的分类，读 pg_locks 需结合 pg_stat_activity 才能定位到具体会话。终止会话要"先取消、后终止"，按安全层级操作，避免破坏事务。

• innodb_lock_wait_timeout 与 lock_wait_timeout 区别
• 超时后事务状态
• 恢复方法

innodb_lock_wait_timeout 控制 InnoDB 行锁等待的超时时间（默认 50 秒），lock_wait_timeout 控制元数据锁（MDL）等待的超时时间（默认 31536000 秒，即一年）。当行锁等待超时，等待的语句抛出"Lock wait timeout exceeded"错误，该语句被回滚，但事务的其他语句仍可继续（除非设置了 innodb_rollback_on_timeout=ON 则整个事务回滚）；当 MDL 等待超时，同样抛出锁等待超时错误（等待期间进程状态表现为 Waiting for table metadata lock）。恢复方式是：终止阻塞方事务（kill 持锁会话）或等阻塞方提交，然后重试被回滚的语句或事务。

两个超时参数分别针对行锁与元数据锁，易混淆。理解超时后的"语句回滚 vs 事务回滚"语义，以及恢复需先解除阻塞，是排查锁超时问题的关键。

• NOWAIT 与 SKIP LOCKED
• MySQL 与 PostgreSQL 的差异
• 任务队列领取

NOWAIT 表示如果要加锁的行已被其他事务锁定，立即报错而不等待；SKIP LOCKED 表示跳过当前已被锁定的行，只返回未锁定的行。两者 PostgreSQL 与 MySQL 8.0 都支持。差异主要体现在：SKIP LOCKED 适合任务队列并发领取——多个工作者同时 SELECT ... FOR UPDATE SKIP LOCKED 领取任务，每个任务只被一个工作者锁到，不会重复领取，也不会因等待已锁任务而阻塞。相比之下，普通 FOR UPDATE 会让工作者互相等待，NOWAIT 会直接报错，都不适合高并发任务领取。SKIP LOCKED 让每个工作者立即拿到不同的任务，实现高效并发消费。

SKIP LOCKED 是"跳过已锁行"的并发领取语义，天然适合任务队列、消息消费等场景，避免重复处理和锁等待。NOWAIT 则适合"抢不到就报错"的场景。

• session 级 advisory lock
• transaction 级 advisory lock
• 释放时机差异

Session 级 advisory lock（pg_advisory_lock）在会话级持有，需显式调用 pg_advisory_unlock 释放，或在会话结束时自动释放；Transaction 级 advisory lock（pg_advisory_xact_lock）在事务级持有，随事务提交或回滚自动释放，无需也不能显式释放。差异在于释放时机：session 级由应用控制、"跨事务"持有，适合需要跨多个事务保持锁的场景；transaction 级自动随事务结束释放，适合"锁的持有期正好等于一个事务"的场景，且更安全（不会因忘记释放而泄漏）。

选择 session 级还是 transaction 级，取决于锁的持有周期。若锁必须在多个事务间保持，用 session 级；若锁只需在一个事务内有效，用 transaction 级更安全、自动释放。

• lock_timeout 锁等待超时
• statement_timeout 语句超时
• 语义差异

lock_timeout 控制单个锁等待的超时时间，即一个语句在等待锁时超过该时间则放弃并报错；statement_timeout 控制整个语句的执行超时，即语句执行总时长超过该时间则被终止。lock_timeout 是"等锁"的时限，statement_timeout 是"执行"的时限。两者可同时设置：语句执行中若等待锁超时，触发 lock_timeout 报错；若语句整体执行超时，触发 statement_timeout 终止。lock_timeout 只作用于锁等待，statement_timeout 作用于整个语句，两者衡量的是不同阶段。

"等锁"与"执行"是两个不同阶段，lock_timeout 与 statement_timeout 分别对其设限。理解差异有助于区分"锁等待超时"与"语句运行超时"两种错误。

• 行锁内存表示
• 为什么不升级锁
• SQL Server 的锁升级

行锁在内存中由锁结构（lock struct）表示，每个锁结构包含锁类型、事务 ID、锁模式、锁定的记录（记录指针或 key）等信息，占用内存随时间增长。InnoDB 与 PostgreSQL 宁愿维护大量行锁而不做锁升级，是因为：行锁粒度小、并发高，升级为表锁会突然扩大冲突范围、降低并发，且 InnoDB 通过内存锁表与 hash 管理、PostgreSQL 用元组 xmax 标记，锁开销相对可控。SQL Server 则会在行锁数量超过阈值（锁升级阈值）时升级为表锁，以减少锁内存开销，但牺牲并发度。这是"锁开销 vs 并发度"的权衡，InnoDB 与 PG 倾向并发，SQL Server 倾向控制开销。

锁升级的取舍是"内存开销 vs 并发度"。InnoDB/PG 用精细的锁管理维持高并发，SQL Server 用升级控制锁内存。生产环境需关注行锁数量与内存占用，避免锁风暴。

• advisory lock 键设计
• 两个 int 参数的命名空间
• 避免业务模块冲突

PostgreSQL 提供 pg_advisory_lock(key)（bigint 单键）和 pg_advisory_lock(key1, key2)（两个 int 键）两种形式。两个 int 形式的第一个 int 可作为"命名空间"或"业务模块 ID"，第二个 int 作为"具体资源 ID"，通过这种两级编码，不同业务模块使用不同的第一个 int，就不会互相冲突。例如模块 A 用 pg_advisory_lock(1, 100)，模块 B 用 pg_advisory_lock(2, 100)，虽然资源 ID 都是 100，但命名空间不同，锁互不干扰。bigint 单键则通过把"模块号 + 资源号"编码进一个 bigint 实现同样的隔离。

命名空间编码是 advisory lock 键设计的关键，避免不同业务模块因键相同而互相误锁。用"模块前缀 + 资源号"的编码方式，能实现清晰的锁隔离。

• 会话级课后释放
• 事务级自动释放
• 连接池复用避免锁残留

会话级 advisory lock 在会话结束时自动释放，但若连接池复用会话（同一连接被多个逻辑事务使用），会话级锁可能被下一个请求沿用，造成锁残留。事务级 advisory lock（pg_advisory_xact_lock）随事务提交或回滚自动释放，天然避免了连接池复用导致的锁残留。因此在使用连接池时，应优先使用事务级 advisory lock，或在每次逻辑请求结束时显式释放会话级锁。若必须用会话级锁，需确保在事务/请求结束后调用 pg_advisory_unlock 释放，或在连接归还连接池前释放。

连接池复用会话使"会话级锁"的生命周期难以对齐逻辑请求，容易残留。事务级锁自动释放 + 显式释放会话级锁，是规避锁残留的两种手段。

• GET_LOCK/RELEASE_LOCK
• 与会话绑定
• 会话断开自动释放的场景

MySQL 的 GET_LOCK()/RELEASE_LOCK() 与 PostgreSQL 的 advisory lock 都是"应用级咨询锁"，不绑定具体数据，用于协调。异同：GET_LOCK 按名称加锁，调用方需在会话中管理，RELEASE_LOCK 释放，会话断开时自动释放；advisory lock 用整数键，有 session 级与 xact 级之分。两者都与会话绑定，会话断开自动释放。这一特性适合"锁的生命周期与会话生命周期一致"的场景，如防止同一连接重复执行某操作、基于连接做互斥。但自动释放也意味着锁在连接意外断开时会丢失，因此不适合"锁必须持久到业务完成"的场景。

会话级咨询锁"断开即释放"的特性，既带来便利（无需手动清理），也带来风险（意外断开会丢锁）。适合临时性、短生命周期的互斥，不适合强持久需求。