Re: [GENERAL] Transaction lock granting order

[Joshua Ma]

Thanks a bunch Tom, appreciate the quick response.

On Mon, Dec 5, 2016 at 12:33 PM, Tom Lane  wrote:

> Joshua Ma  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
>

Re: [GENERAL] Transaction lock granting order

[Tom Lane]

Joshua Ma  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Transaction lock granting order

[Joshua Ma]

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