PostgreSQL最优数据锁定方案：自定义跨事务互斥锁如何优化轮询？

现有方案的核心问题 #

你的当前实现存在两个严重缺陷：

1. 空转忙等：WHILE循环中没有等待逻辑，抢锁期间会占满PostgreSQL工作进程的CPU资源，并发稍高就会把数据库CPU打满。
2. 存在竞态窗口：先SELECT判断locked状态、再执行UPDATE的操作不是原子的，高并发下会出现多个请求同时通过locked=false的检查，同时拿到锁的问题，完全达不到互斥效果。

优化后的轮询实现方案 #

第一步：表结构调整（可选但强烈建议） #

给guild_options表加两个字段，避免客户端异常崩溃导致的永久死锁，同时提升锁操作安全性：

ALTER TABLE guild_options 
ADD COLUMN IF NOT EXISTS locked_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS lock_owner UUID; -- 可选，用来校验解锁请求确实是锁持有者

第二步：重写get_config函数，实现原子加锁+低消耗轮询 #

把锁检查和加锁逻辑合并为单条原子UPDATE，消除竞态窗口，同时加等待逻辑避免空耗CPU：

CREATE OR REPLACE FUNCTION get_config (guildid_in int8, key_in varchar, lock_timeout_sec INT DEFAULT 30) RETURNS jsonb AS $$
DECLARE 
    d JSONB;
    updated_rows INT;
BEGIN
    LOOP
        -- 原子操作：只有未锁定、或者锁已超时的行才会被更新，返回1表示成功拿到锁
        UPDATE guild_options 
        SET locked = true, locked_at = NOW()
        WHERE guildid = guildid_in 
          AND key = key_in
          AND (locked = false OR locked_at < NOW() - (lock_timeout_sec || 's')::INTERVAL);
        
        GET DIAGNOSTICS updated_rows = ROW_COUNT;
        IF updated_rows = 1 THEN
            -- 拿到锁后读取数据返回
            SELECT data INTO d FROM guild_options WHERE guildid = guildid_in AND key = key_in;
            RETURN d;
        END IF;
        -- 没拿到锁，等待50ms再重试，可根据业务场景调整等待时长
        PERFORM pg_sleep(0.05);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

第三步：优化set_config函数，增加解锁校验 #

CREATE OR REPLACE FUNCTION set_config (guildid_in int8, key_in varchar, data_in jsonb) RETURNS void AS $$
BEGIN
    UPDATE guild_options 
    SET locked = false, locked_at = NULL, data = data_in 
    WHERE guildid = guildid_in 
      AND key = key_in
      AND locked = true; -- 只有已锁定的行才能解锁，避免误操作
END;
$$ LANGUAGE plpgsql;

额外优化建议 #

• 如果你的锁持有时间通常很短，可以把pg_sleep的等待时间调小到0.01s降低延迟；如果锁持有时间较长，可以调大等待时间减少数据库压力。
• 如果需要更高的安全性，可以使用lock_owner字段，调用get_config的时候生成一个UUID返回给客户端，解锁的时候需要传入这个UUID校验，避免其他客户端误解锁。
• 这个方案完全适配你跨事务、跨会话、使用连接池的场景，效率比原空转方案高几个数量级，也规避了PostgreSQL原生 advisory 锁会话绑定的限制。

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