0

I am writing an application that processes jobs. A job does 2 things that are non-transactional: PartA and PartB. Both are idempotent.

A job has three statuses:

  • Created
  • PartA_Done
  • PartB_Done

The logic looks something like this:

BEGIN;
let jobId = randomUUID
INSERT INTO jobs (id, status) VALUES (jobId, 'Created');
COMMIT;

BEGIN;
do partA in application code (make HTTP request)
UPDATE jobs SET status = 'PartA_Done' WHERE id=?
COMMIT;

BEGIN;
do partB in application code (make HTTP request)
UPDATE jobs SET status = 'PartB_Done' WHERE id=?
COMMIT;

PartA could succeed, but fail to update the jobs table. A retry cron job will retry to completion, PartB_Done.

If I were to add more than one cron job, i.e. running concurrently, there's a risk of the cron jobs doing duplicate work. In particular, both cron jobs could re-do PartA, PartB or both for the same job. I want to avoid that.

Is there any way for me to perform the COMMIT, while maintaining a FOR UPDATE lock in subsequent transactions on the new jobs row that I've added?

I thought that COMMIT AND CHAIN would work, but I'm not sure what to do.

1 Answer 1

1

You cannot keep a row lock across a commit. I have two ideas:

  1. Add an additional column processing_since of type timestamp with time zone. The column is NULL when nobody works on the job. Whenever a process starts working on the job, it sets the column to current_timestamp, and when it completes a part, it does not only change status but also set processing_since to NULL.

    A job is up for grabs if processing_since is NULL or older than a certain time interval that is longer than it can possibly take to complete a part of the job. That way, the "lock" expires after a while.

  2. Use advisory locks. These locks can live longer than a transaction and don't cause problems with autovacuum. Unfortunately you are using UUIDs, otherwise you could have taken the jobid for the advisory lock. You could add another bigint column to the table just for that purpose.

6
  • Thanks, Laurenz! I enjoy reading your blog posts. For the latter 2 BEGIN/COMMIT, I would SELECT FOR UPDATE before doing the HTTP request? Commented Nov 4, 2023 at 18:29
  • No, why would you? Are you talking about the advisory locks? Commented Nov 4, 2023 at 21:17
  • Without that, two concurrent cron jobs could process the same job. No - talking about your option 1. Commented Nov 4, 2023 at 22:20
  • I wrote "A job is up for grabs if processing_since is NULL or older than a certain time interval that is longer than it can possibly take to complete a part of the job." That will make sure that no two workers grab the same job to work on. Commented Nov 6, 2023 at 6:55
  • 1
    Ah, yes, you are right. Perhaps FOR UPDATE SKIP LOCKED would be a good choice to avoid lock waits. Commented Nov 6, 2023 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.