Re: Transaction lock granting order

Joshua Ma <josh@benchling.com> writes:
> Can someone point me to documentation on (or confirm) this detail on
> Postgres locking?

> - Transaction X starts and acquires a lock on a table T
> - Transaction Y starts and attempts to acquire a conflicting lock on T - it
> is now blocked
> - Transaction Z starts and also attempts to acquire a conflicting lock on T
> - it is now blocked

> Is txn Y guaranteed to be the first txn to proceed once X finishes?

In isolation, arrival order is respected, but there are cases where it
would not be.  In particular, lock queues can get reordered to fix
"soft deadlock" situations where the only alternative to letting Z go
ahead of Y is to raise a deadlock error.  This would require there being
other locks in the system besides the ones you mention, of course.
(And it may well require more than three transactions --- I don't remember
at the moment what are the user-visible cases where this happens.)

You can find probably more than you want to know about deadlock handling
in src/backend/storage/lmgr/README.

                        regards, tom lane