0

I wonder whether this scenario is safe race condition wise.

I mean is there is any risk that 2 or more queries run in the same time will override themself:

UPDATE table_name
SET process_id = 'foobar'
WHERE process_id IS NULL
AND (...);

Desired scenario is that only one transaction can ever make this update.

Do I need to use some explicit locking mechanism here, or postgres does that for me?

Robert Trzebiński
  • 1,327
  • 8
  • 16
  • Here is what I found - Mostly what happens when you try to UPDATE is that Postgres will acquire a lock on the row that you want to change. If you have two update statements running at the same time on the same row, then the second must wait for the first to process. – Tsvetoslav Tsvetkov Nov 09 '22 at 07:54
  • 1
    The second transaction will only see the 'foobar' value and that's not a null. That means the second transaction will not update this record. – Frank Heikens Nov 09 '22 at 08:08

1 Answers1

0

You can do explicit lock on table before performing update. Also if you have one function or procedure that does this update, you can use perform pg_advisory_lock($key1, $key2) and the second call of function will wait until the first process releases the lock.

But anyway if you are updating the same rows from multiple connections, locks will occur, which shouldn't be a problem unless it comes out as a deadlock.

Martin Kegel
  • 9
  • 2