咨询锁（Advisory Lock）

概念概述

咨询锁（Advisory Lock）（Advisory Locks）指的是：咨询锁是由应用自定义键的锁，由数据库负责存取与释放，而键的含义保留在应用代码中，用于协调无自然行可锁的外部工作。

从更严格的定义看，An advisory lock is a lock whose key is chosen by the application rather than tied to a table row, so the database stores and releases it while the meaning of the key stays in application code. 该概念在工程实践中的价值，在于它能帮助团队用统一的语言描述一个具体的质量属性或设计约束，从而在评审与协作中减少歧义。

为什么需要它

It is used for distributed singletons, migration guards, and serialising external work that has no natural row to lock. 它与 Cost-Based Query Optimization、Database Connection Multiplexing、Autovacuum and Dead Tuple Reclamation 等概念经常一起出现，用于解决相近的工程问题。理解它的适用场景，有助于在多种备选方案之间做出有依据的取舍，而不是凭直觉选择技术方案。

常见误解

• advisory locks automatically expire when a process crashes
• they grant the same guarantees as row-level locks

需要避免的反模式

• holding a session-level advisory lock through a pooled connection
• using an advisory lock key that collides across unrelated features

实践要点

1. Session-level advisory locks persist until explicitly released or the session ends, whereas transaction-level locks release automatically at commit or rollback.
2. With a connection pool the session may outlive the request, so a session lock can be left held and block every later caller that needs the same key.
3. Choose keyspace conventions carefully, because two features that pick the same numeric key will silently serialise each other with no visible relation.

参考来源

https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS

代码示例

-- PostgreSQL 会话级咨询锁：try 锁拿不到立即返回假
SELECT pg_try_advisory_lock(4271);

-- 若返回 true，此会话独占编号 4271 的锁
-- 其他实例的尝试会得到 false：
SELECT pg_try_advisory_lock(4271);  -- false：已被占用

-- 临界区：只有一个实例在执行对账
BEGIN;
SELECT pg_advisory_xact_lock(4271);  -- 事务级：提交或回滚自动释放

UPDATE reconciliation_runs
  SET started_at = now(), status = 'running'
  WHERE id = 'nightly-2026-10-21';

-- ... 对账主体 ...

UPDATE reconciliation_runs
  SET finished_at = now(), status = 'done'
  WHERE id = 'nightly-2026-10-21';
COMMIT;  -- 锁随事务结束自动归还

-- 会话级锁的显式释放路径
SELECT pg_advisory_unlock(4271);

-- 双键形式：类目 ID 加实体 ID 组合编号
SELECT pg_try_advisory_lock(4271, 42);

-- 锁状态观测
SELECT pg_locks.locktype, pg_locks.objid, pg_locks.granted
FROM pg_locks
WHERE locktype = 'advisory';

interface VersionedRow {
  readonly key: string;
  readonly value: string;
  readonly startTs: number;
  readonly endTs: number;
}

export class MvccStore {
  private versions: VersionedRow[] = [];
  private clock = 0;

  beginTransaction(): number {
    return ++this.clock;
  }

  write(key: string, value: string, ts: number): void {
    for (const row of this.versions) {
      if (row.key === key && row.endTs === Number.POSITIVE_INFINITY) row.endTs = ts;
    }
    this.versions.push({ key, value, startTs: ts, endTs: Number.POSITIVE_INFINITY });
  }

  read(key: string, snapshotTs: number): string | undefined {
    let found: VersionedRow | undefined;
    for (const row of this.versions) {
      if (row.key !== key) continue;
      if (row.startTs <= snapshotTs && snapshotTs < row.endTs) {
        if (!found || row.startTs > found.startTs) found = row;
      }
    }
    return found?.value;
  }
}