Wsrtjtyk

In PostgreSQL 9.6, is the below FOR UPDATE clause in this CTE necessary?

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$;

The intent of the locking is to ensure that a job is taken by only one worker at a time (which seems to work as expected), but I'm seeing occasional deadlocks when calling this function and wonder if my explicit locking is potentially causing problems.

The WHERE NOT EXISTS is to ensure the same project doesn't get started twice, unless it has timed out after 1 hour.

Because there is only a single row involved, the CTE seems unnecessary. This would only make sense if you want to update several rows and lock them in a certain order to avoid deadlocks.

Since the query will lock only a single jobs row, it cannot deadlock with anything by itself.

There most be some other data modifying statements in the same transaction that together with the statement you show create a deadlock.

Remember that locks are held until the end of a transaction!