postgresql - Lock for SELECT so another process doesn't get old data

Question

I have a table that could have two threads reading data from it. If the data is in a certain state (let's say state 1) then the process will do something (not relevant to this question) and then update the state to 2.

It seems to me that there could be a case where thread 1 and thread 2 both perform a select within microseconds of one another and both see that the row is in state 1, and then both do the same thing and 2 updates occur after locks have been released.

Question is: Is there a way to prevent the second thread from being able to modify this data in Postgres - AKA it is forced to do another SELECT after the lock from the first one is released for its update so it knows to bail in order to prevent dupes?

I looked into row locking, but it says you cannot prevent select statements which sounds like it won't work for my condition here. Is my only option to use advisory locks?

Answer 1

Your question, referencing an unknown source:

I looked into row locking, but it says you cannot prevent select statements which sounds like it won't work for my condition here. Is my only option to use advisory locks?

The official documentation on the matter:

Row-level locks do not affect data querying; they block only writers and lockers to the same row.

Concurrent attempts will not just select but try to take out the same row-level lock with SELECT ... FOR UPDATE - which causes them to wait for any previous transaction holding a lock on the same row to either commit or roll back. Just what you wanted.

However, many use cases are better solved with advisory locks - in versions before 9.5. You can still lock rows being processed with FOR UPDATE additionally to be safe. But if the next transaction just wants to process "the next free row" it's often much more efficient not to wait for the same row, which is almost certainly unavailable after the lock is released, but skip to the "next free" immediately.

In Postgres 9.5+ consider FOR UPDATE SKIP LOCKED for this. Like @Craig commented, this can largely replace advisory locks.

Related question stumbling over the same performance hog:

• Function taking forever to run for large number of records

Explanation and code example for advisory locks or FOR UPDATE SKIP LOCKED in Postgres 9.5+:

• Postgres UPDATE ... LIMIT 1

To lock many rows at once:

• How to mark certain nr of rows in table on concurrent access