Re: SELECT FOR UPDATE on rows that don't exist - Mailing list pgsql-novice

You've informed the system you are going to be updating rows on the table but as yet have not given it specific rows to protect.

The index will not allow duplicates to be inserted and the first one to try forces all other potential insertions to wait until the first one commits;

"Short-term share/exclusive page-level locks are used for read/write access. Locks are released immediately after each index row is fetched or inserted. These index types provide the highest concurrency without deadlock conditions."

But, once the insertion happens the second transaction sees the potentially conflicting record and so continues waiting to see whether it commits with the conflict in place (at which point it returns the duplicate key error) or not (in which case one of them then gets to proceed with their insertion).  This manifests as a lock on the transactionid of the first session as is most easily seen in in pg_stat_activity wait_event/wait_event_type fields.

I picked up the last point through my own experimentation, if there is documentation someone else will hopefully point it out.

For a job queue typically you'd fire off an update to claim the work - ensuring only one session gets the assignment - and another to indicate completion.  The "critical section" is usually so long as to be harmful to the database to be performed while holding a transaction open specifically for locking purposes.  I have to imagine there are better tools, especially in-memory ones, designed to handle distributed process coordination. That said, the goodness or badness of doing it in the database heavily depends on knowing more than just "distributed mutex".  The critical section details, not just existence, factors into such a subjective evaluation.  The fact you have a functioning database does make things appealing.

David J.