Atomically update flag in Postgres?

Question

I am implementing a lightweight jobs system in my highly-concurrent application. For simplicity, I will be using Postgres to manage the state of all jobs in the system.

I have a table where processes can mark specific jobs as "running" via a boolean flag is_running. If multiple processes attempt to run the same job, only one of the processes should "win".

I want to have an atomic statement that the application processes will call in an attempt to exclusively mark the job as running.

UPDATE jobs SET is_running = true WHERE is_running = false AND id = 1

If there a is a single row with (id=1, is_running=false), and multiple processes attempt to execute the above statement, is it possible for more than one process to actually set is_running=true?

I only want one of the processes to see an updated row count of 1 - all other processes should see an updated row count of 0.

Laurenz Albe · Accepted Answer · 2022-05-30 06:03:04Z

1

Your approach is safe and free from race conditions, because PostgreSQL will reevaluate the WHERE condition after it had to wait for a lock caused by a concurrent modification. It will then see the changed value and skip the row.

answered May 30, 2022 at 6:03

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Atomically update flag in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related