PostgreSQL中lock_timeout的替代方案：阻塞其他会话时终止自身会话

PostgreSQL没有像lock_timeout那样直接实现“自身会话阻塞其他会话时自动终止”的内置参数，但可以通过以下几种方案实现需求：

1. 结合系统视图与定时任务监控并终止阻塞会话 #

通过查询pg_locks和pg_stat_activity系统视图，识别出持有锁且导致其他会话等待的进程，再调用pg_terminate_backend()终止这些会话。

核心查询逻辑 #

先找出所有持有锁且存在等待者的会话：

SELECT pid
FROM pg_locks l1
WHERE EXISTS (
    SELECT 1 FROM pg_locks l2
    WHERE l2.locktype = l1.locktype
      AND l2.database = l1.database
      AND l2.relation = l1.relation
      AND l2.pid != l1.pid
      AND l2.granted = false
      AND l1.granted = true
);

如果需要过滤掉短时间持有锁的正常情况，可结合事务启动时间判断，比如只终止持有锁超过5分钟的会话：

SELECT pid
FROM pg_locks l1
JOIN pg_stat_activity sa ON l1.pid = sa.pid
WHERE EXISTS (
    SELECT 1 FROM pg_locks l2
    WHERE l2.locktype = l1.locktype
      AND l2.database = l1.database
      AND l2.relation = l1.relation
      AND l2.pid != l1.pid
      AND l2.granted = false
      AND l1.granted = true
)
AND sa.xact_start < NOW() - INTERVAL '5 minutes';

自动化执行 #

将上述逻辑封装成函数，使用pg_cron（PostgreSQL的定时任务扩展）定期执行，比如每分钟检查一次：

-- 先确保pg_cron已安装
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 创建终止阻塞会话的函数
CREATE OR REPLACE FUNCTION terminate_blocking_sessions()
RETURNS void AS $$
DECLARE
    blocking_pid integer;
BEGIN
    FOR blocking_pid IN
        SELECT pid
        FROM pg_locks l1
        JOIN pg_stat_activity sa ON l1.pid = sa.pid
        WHERE EXISTS (
            SELECT 1 FROM pg_locks l2
            WHERE l2.locktype = l1.locktype
              AND l2.database = l1.database
              AND l2.relation = l1.relation
              AND l2.pid != l1.pid
              AND l2.granted = false
              AND l1.granted = true
        )
        AND sa.xact_start < NOW() - INTERVAL '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(blocking_pid);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 设定定时任务：每分钟执行一次
SELECT cron.schedule('terminate-blocking-sessions', '* * * * *', 'SELECT terminate_blocking_sessions();');

2. 利用事务超时参数限制锁持有时间 #

如果你的会话常在事务中闲置并持有锁，可以设置idle_in_transaction_session_timeout参数，当会话在事务中 idle 超过指定时长时自动终止，从而释放锁：

-- 全局设置（需重启生效）
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
-- 或当前会话临时设置
SET idle_in_transaction_session_timeout = '5min';

注意：该方案仅针对事务中闲置的会话，若会话一直在执行操作但持有锁阻塞他人，则无法触发。

3. 应用层自定义监控逻辑 #

在应用代码中嵌入监控逻辑，定期检查当前会话是否阻塞了其他会话，若满足条件则主动调用pg_terminate_backend()终止自身（需确保会话有对应的权限）。

权限说明 #

执行pg_terminate_backend()需要超级用户权限，或会话所属用户被授予pg_signal_backend角色：

GRANT pg_signal_backend TO your_username;

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