如何在获取pg_advisory_lock时，若请求数超X则直接失败？

PostgreSQL 原生的 pg_advisory_lock 没有直接提供限制等待请求数的参数，要实现当等待该锁的请求数超过指定阈值时报错的需求，需要通过自定义函数来实现，以下是几种不同场景的方案：

方案1：简单检查（存在竞态，适合低并发场景） #

这个方案先查询当前等待锁的会话数，超过阈值则直接报错，否则阻塞获取锁。缺点是查询等待数和获取锁之间存在竞态窗口，可能出现实际等待数超过阈值的情况。

CREATE OR REPLACE FUNCTION acquire_advisory_lock_with_queue_limit(p_lock_id text, p_max_waiters int)
RETURNS void AS $$
DECLARE
  v_lock_key int := hashtext(p_lock_id);
  v_waiting_count int;
BEGIN
  -- 查询当前等待该锁的会话数（未包含当前请求）
  SELECT COUNT(*)
  INTO v_waiting_count
  FROM pg_locks
  WHERE locktype = 'advisory'
    AND classid = v_lock_key
    AND objid = 0
    AND objsubid = 0
    AND granted = false;

  -- 等待数达上限则抛出异常
  IF v_waiting_count >= p_max_waiters THEN
    RAISE EXCEPTION 'Too many waiters for lock %: % waiting, max allowed is %', p_lock_id, v_waiting_count, p_max_waiters;
  END IF;

  -- 阻塞式获取锁
  PERFORM pg_advisory_lock(v_lock_key);
END;
$$ LANGUAGE plpgsql VOLATILE;

使用方式：

SELECT acquire_advisory_lock_with_queue_limit('your_lock_id', 5); -- 允许最多5个等待请求

方案2：减少竞态窗口（更可靠） #

先尝试非阻塞获取锁，失败后再检查等待数，缩短竞态窗口，降低超阈值的概率：

CREATE OR REPLACE FUNCTION acquire_advisory_lock_with_queue_limit(p_lock_id text, p_max_waiters int)
RETURNS void AS $$
DECLARE
  v_lock_key int := hashtext(p_lock_id);
  v_waiting_count int;
  v_acquired boolean;
BEGIN
  -- 先尝试非阻塞获取锁
  SELECT pg_try_advisory_lock(v_lock_key) INTO v_acquired;

  IF v_acquired THEN
    RETURN;
  END IF;

  -- 获取失败，查询当前等待数
  SELECT COUNT(*)
  INTO v_waiting_count
  FROM pg_locks
  WHERE locktype = 'advisory'
    AND classid = v_lock_key
    AND objid = 0
    AND objsubid = 0
    AND granted = false;

  -- 等待数达上限则报错
  IF v_waiting_count >= p_max_waiters THEN
    RAISE EXCEPTION 'Too many waiters for lock %: % waiting, max allowed is %', p_lock_id, v_waiting_count, p_max_waiters;
  END IF;

  -- 阻塞等待获取锁
  PERFORM pg_advisory_lock(v_lock_key);
END;
$$ LANGUAGE plpgsql VOLATILE;

方案3：严格队列限制（无竞态，适合高并发场景） #

通过维护一个独立的计数器表，原子性地控制等待队列长度，彻底避免竞态问题，但需要配套使用释放锁的函数：

1. 创建计数器表 #

CREATE TABLE IF NOT EXISTS advisory_lock_wait_counts (
  lock_key int PRIMARY KEY,
  waiting_count int NOT NULL DEFAULT 0
);

2. 获取锁函数 #

CREATE OR REPLACE FUNCTION acquire_advisory_lock_with_strict_queue_limit(p_lock_id text, p_max_waiters int)
RETURNS void AS $$
DECLARE
  v_lock_key int := hashtext(p_lock_id);
  v_current_count int;
BEGIN
  -- 原子性增加等待计数器（不存在则插入）
  LOOP
    UPDATE advisory_lock_wait_counts
    SET waiting_count = waiting_count + 1
    WHERE lock_key = v_lock_key
    RETURNING waiting_count INTO v_current_count;

    IF FOUND THEN
      EXIT;
    END IF;

    INSERT INTO advisory_lock_wait_counts (lock_key, waiting_count)
    VALUES (v_lock_key, 1)
    ON CONFLICT (lock_key) DO NOTHING
    RETURNING waiting_count INTO v_current_count;

    IF FOUND THEN
      EXIT;
    END IF;
  END LOOP;

  -- 检查是否超过上限
  IF v_current_count > p_max_waiters THEN
    -- 回滚计数器
    UPDATE advisory_lock_wait_counts
    SET waiting_count = waiting_count - 1
    WHERE lock_key = v_lock_key;

    RAISE EXCEPTION 'Too many waiters for lock %: % waiting, max allowed is %', p_lock_id, v_current_count - 1, p_max_waiters;
  END IF;

  BEGIN
    -- 阻塞获取锁
    PERFORM pg_advisory_lock(v_lock_key);
  EXCEPTION
    WHEN OTHERS THEN
      -- 获取锁失败，回滚计数器
      UPDATE advisory_lock_wait_counts
      SET waiting_count = waiting_count - 1
      WHERE lock_key = v_lock_key;
      RAISE;
  END;
END;
$$ LANGUAGE plpgsql VOLATILE;

3. 释放锁函数（必须配套使用） #

CREATE OR REPLACE FUNCTION release_advisory_lock_with_queue_count(p_lock_id text)
RETURNS void AS $$
DECLARE
  v_lock_key int := hashtext(p_lock_id);
BEGIN
  PERFORM pg_advisory_unlock(v_lock_key);

  -- 减少计数器，计数为0则删除记录
  UPDATE advisory_lock_wait_counts
  SET waiting_count = waiting_count - 1
  WHERE lock_key = v_lock_key;

  DELETE FROM advisory_lock_wait_counts
  WHERE lock_key = v_lock_key AND waiting_count = 0;
END;
$$ LANGUAGE plpgsql VOLATILE;

使用方式：

-- 获取锁（最多允许3个等待请求）
SELECT acquire_advisory_lock_with_strict_queue_limit('your_lock_id', 3);

-- 释放锁
SELECT release_advisory_lock_with_queue_count('your_lock_id');

内容的提问来源于stack exchange，提问作者Alexander Mills