如何在PostgreSQL中判断任务行是否被Worker锁定

我正在构建一个Worker系统，闲置硬件成本高昂，因此机器需要尽快获取新任务。任务时长差异极大：部分仅需数秒，部分需数分钟，还有些可能耗时半小时。因此很难选择固定的租期超时时间：

• 短租期可能导致有效长时任务被重复处理
• 长租期则可能在Worker崩溃后导致硬件闲置

我的思路是在Worker处理任务期间保持数据库事务开启：

1. Worker启动一个事务。
2. 使用SELECT ... FOR UPDATE SKIP LOCKED获取任务。
3. 其他Worker跳过已锁定的行，处理其他任务。
4. 仅在任务完成或失败后，Worker才提交事务。

这避免了双事务方案的问题：即第一个事务将任务标记为running，后续事务再将其更新为finished或failed。若Worker在两个事务之间崩溃，任务可能会一直处于running状态。

目前剩余的问题是前端展示：在列出任务时，我希望显示某任务是否正被活跃Worker锁定，例如返回is_running或is_locked字段，且不依赖持久化的running状态或租期超时。

请问是否有简洁的PostgreSQL模式可用于判断特定行是否正被其他事务锁定？

PostgreSQL提供了系统视图pg_locks，可以直接查询行级锁的状态，结合任务表就能判断特定任务是否被锁定。以下是几种简洁的实现方式：

1. 直接关联查询获取锁定状态 #

在查询任务列表时，通过关联pg_locks和任务表，判断每行是否存在对应的行级锁：

SELECT
    t.*,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM pg_locks l
            JOIN pg_class c ON l.relation = c.oid
            WHERE
                c.relname = 'tasks' -- 替换为你的任务表名
                AND l.locktype = 'tuple'
                AND l.objid = t.ctid -- 关联任务行的物理标识符
                AND l.mode = 'RowExclusiveLock' -- FOR UPDATE 会添加此锁类型
                AND l.pid != pg_backend_pid() -- 排除当前事务自身加的锁
        ) THEN TRUE
        ELSE FALSE
    END AS is_locked
FROM tasks t;

关键说明：

• ctid是PostgreSQL中行的物理位置标识符，可精准匹配被锁定的行
• RowExclusiveLock是SELECT ... FOR UPDATE语句默认添加的锁类型
• pg_backend_pid()获取当前会话的进程ID，避免误判自身持有的锁

2. 创建可重用的视图 #

如果需要频繁查询锁定状态，可以创建一个包含该字段的视图，简化前端调用：

CREATE VIEW tasks_with_lock_status AS
SELECT
    t.*,
    EXISTS (
        SELECT 1
        FROM pg_locks l
        JOIN pg_class c ON l.relation = c.oid
        WHERE
            c.relname = 'tasks'
            AND l.locktype = 'tuple'
            AND l.objid = t.ctid
            AND l.mode = 'RowExclusiveLock'
            AND l.pid != pg_backend_pid()
    ) AS is_locked
FROM tasks t;

之后前端直接查询该视图即可：

SELECT * FROM tasks_with_lock_status;

注意事项 #

• pg_locks是系统视图，需要确保查询用户拥有pg_monitor角色或超级用户权限，才能查看所有锁信息
• 如果任务表使用分区，需要调整pg_class的关联逻辑，覆盖所有分区表
• ctid会在VACUUM FULL等操作后变化，但Worker持有锁期间行不会被移动，因此查询结果是可靠的

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