CODEWITHSUNDEEP
postgresqlconcurrencytransactionsisolation-level
vapor_cafe
vapor_cafe

Reputation: 21

Atomically update flag in Postgres?

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.

Upvotes: 2

Views: 246

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246053

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.

Upvotes: 1

Related Questions