postgresql - SELECT FOR UPDATE 是否需要在 CTE 中进行更新？

Question

在 PostgreSQL 9.6 中，此 CTE 中的以下 FOR UPDATE 子句是否必要？

CREATE OR REPLACE FUNCTION next_job()
    RETURNS json
    LANGUAGE 'sql'

AS $BODY$

    WITH thejob AS (
        SELECT   jobs.*, company.*
        FROM     (
            select * from jobs 
            WHERE NOT EXISTS (SELECT * from jobs AS j2 where jobs.platform = j2.platform and jobs.project = j2.project AND start > now() - interval '1 hour')
            order by priority, account_priority, job_id 
            limit 1) jobs
        LEFT OUTER JOIN company
            ON jobs.company_id = company.id
        , enabled
        WHERE enabled.status IS TRUE
        FOR UPDATE of jobs
    )
    UPDATE jobs
    SET    start = now() 
    FROM   thejob
    WHERE  jobs.job_id = thejob.job_id
    RETURNING json_build_object('job_id', jobs.job_id, 'platform', jobs.platform, 'project', jobs.project, 'firstSeen', thejob.first_seen);

$BODY$;

锁定的目的是确保一次只有一个工作人员完成一项工作(这似乎按预期工作)，但我在调用此函数时偶尔会看到死锁，想知道我的显式锁定是否是可能会导致问题。

WHERE NOT EXISTS 是为了确保同一个项目不会启动两次，除非它在 ​​1 小时后超时。

Answer 1

因为只涉及一行，所以 CTE 似乎是不必要的。这仅在您想要更新多行并按特定顺序锁定它们以避免死锁时才有意义。

由于查询只会锁定单个 jobs 行，因此它本身不会与任何事物发生死锁。

在同一个事务中大多数有一些其他数据修改语句与您显示的语句一起造成死锁。

请记住，锁一直持有到事务结束!