如何查询PostgreSQL advisory lock时长？及设置自动释放超时

嘿，这个问题问到点子上了——advisory lock在定时任务这类场景里，确实很容易遇到超时没释放的情况，我来给你详细拆解下：

首先，要获取advisory lock的持有时间，得结合系统视图pg_locks（记录所有锁信息）和pg_stat_activity（记录会话活动）。这俩视图关联后，就能算出锁已经持有的时长。

这里要区分会话级advisory lock和事务级advisory lock：

• 会话级锁：调用pg_advisory_lock()等函数获取，会一直持有直到会话结束或主动解锁；
• 事务级锁：调用pg_advisory_xact_lock()等函数获取，事务结束自动释放。

对应的查询语句如下：

SELECT
  l.pid,
  -- 区分会话级/事务级advisory锁
  CASE 
    WHEN l.mode LIKE '%Session%' THEN 'Session-level'
    ELSE 'Transaction-level'
  END AS lock_level,
  -- 锁的类型标识（advisory锁的键值）
  l.objid AS advisory_lock_key,
  -- 计算持有时长
  NOW() - CASE
    WHEN l.mode LIKE '%Session%' THEN s.query_start  -- 会话级锁用获取锁的查询时间
    ELSE s.xact_start                               -- 事务级锁用事务开始时间
  END AS lock_hold_duration,
  s.usename,
  s.application_name,
  s.query
FROM pg_locks l
JOIN pg_stat_activity s ON l.pid = s.pid
WHERE l.locktype = 'advisory'
  AND l.granted = true;

这个查询会列出所有已授予的advisory锁，包括它们的持有时长、所属会话/事务信息，方便你定位超时的锁。

如果要定期清理超过指定时长未释放的advisory锁，核心思路是找到对应会话的PID，然后终止该会话（会话级锁会随会话结束释放，事务级锁会随事务终止释放）。

步骤1：定位超时锁的PID #

先筛选出持有时间超过阈值（比如30分钟）的锁：

WITH timeout_advisory_locks AS (
  SELECT l.pid
  FROM pg_locks l
  JOIN pg_stat_activity s ON l.pid = s.pid
  WHERE l.locktype = 'advisory'
    AND l.granted = true
    AND NOW() - CASE
      WHEN l.mode LIKE '%Session%' THEN s.query_start
      ELSE s.xact_start
    END > INTERVAL '30 minutes'
)
SELECT pid FROM timeout_advisory_locks;

步骤2：终止会话释放锁 #

确认这些锁确实是过期后，执行以下语句终止对应会话：

WITH timeout_advisory_locks AS (
  SELECT l.pid
  FROM pg_locks l
  JOIN pg_stat_activity s ON l.pid = s.pid
  WHERE l.locktype = 'advisory'
    AND l.granted = true
    AND NOW() - CASE
      WHEN l.mode LIKE '%Session%' THEN s.query_start
      ELSE s.xact_start
    END > INTERVAL '30 minutes'
)
SELECT pg_terminate_backend(pid) AS lock_released
FROM timeout_advisory_locks;

⚠️ 注意：执行pg_terminate_backend()需要超级用户权限，或者当前用户是会话的所有者且拥有pg_signal_backend权限。另外，终止会话会中断该会话的所有正在执行的操作，一定要确保这些锁对应的任务确实已经停滞，避免误杀正常任务。

你可以把这个逻辑封装成定时任务（比如用PostgreSQL的pg_cron扩展，或者外部cron），定期自动清理超时锁。

遗憾的是，PostgreSQL本身没有内置的advisory lock自动超时机制——不管是会话级还是事务级的advisory锁，都不会自动超时释放。不过你可以通过以下几种方式实现类似效果：

• 事务级锁：利用事务超时
  如果是事务级advisory锁，可以在事务内设置语句超时或事务超时，超时后事务会自动回滚，锁也会随之释放：

  BEGIN;
  -- 设置事务内语句超时为5分钟
  SET LOCAL statement_timeout = '5min';
  -- 获取事务级advisory锁
  SELECT pg_advisory_xact_lock(12345);
  -- 执行你的任务
  COMMIT;
  

  要是想限制整个事务的空闲时长，可以用idle_in_transaction_session_timeout，不过它只针对事务空闲的场景。

• 会话级锁：应用层实现超时
  在应用代码里，获取锁后启动一个定时器，当超过指定时间后，主动调用pg_advisory_unlock()或pg_advisory_unlock_all()释放锁。比如用Python的threading.Timer或者Java的ScheduledExecutorService来实现。

• 后台定时清理任务
  就是前面提到的定期查询超时锁并终止会话的方式，这是最通用的方案，不管是会话级还是事务级锁都适用。

内容的提问来源于stack exchange，提问作者Eliot Sykes