Thread: Transaction lock granting order
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? Is there some "lock queue order" that is respected? Is there any chance Z can proceed before Y, say if it acquires a less aggressive lock?
Put differently: when txn Y starts, it has to wait. Is this wait time at most the length of the longest txn older than Y?
Thanks,
Josh
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
Thanks a bunch Tom, appreciate the quick response.
On Mon, Dec 5, 2016 at 12:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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