Обсуждение: pg_advisory_lock() and row deadlocks

On Fri, Apr 20, 2012 at 10:27 AM, Eliot Gable
<egable+pgsql-general@gmail.com> wrote:
> Is it possible to prevent row deadlocks by using pg_advisory_lock()? For
> example:
>
> Transaction 1 grabs pg_advisory_lock(1)
> Transaction 1 runs a statement that updates multiple rows on Table A
> Transaction 1 releases pg_advisory_lock(1)
> Transaction 1 continues processing other stuff
> Transaction 1 grabs pg_advisory_lock(2)
> Transaction 1 runs a statement that updates multiple rows on Table B
> Transaction 1 releases pg_advisory_lock(2)
> Transaction 1 continues processing other stuff
>
> At the same time...
>
> Transaction 2 grabs pg_advisory_lock(2)
> Transaction 2 runs a statement that deletes multiple rows on Table B
> Transaction 2 releases pg_advisory_lock(2)
> Transaction 2 continues processing other stuff
> Transaction 2 grabs pg_advisory_lock(1)
> Transaction 2 runs a statement that deletes multiple rows on Table A
> Transaction 2 releases pg_advisory_lock(1)
> Transaction 2 continues processing other stuff
>
> If these two transactions run simultaneously, is there any way that they can
> have a row deadlock given the way the pg_advisory_lock() calls are made?
>
> My underlying problem is trying to break row deadlocks due to cascading
> deletes on foreign keys in one transaction colliding with updates to rows in
> another transaction.
>
> If I use pg_advisory_lock(), can I lock and unlock a table multiple times in
> both transactions without ever needing to worry about them getting
> deadlocked on rows? Doing select locks on rows is not an option because they
> last until the end of the transaction and I cannot control the order in
> which both transactions grab locks on the different tables involved, and
> each transaction may have an affect on the same rows as the other
> transaction in one or more of the same tables.

hm, I'm not sure this is going to completely help you.  all that
you're getting from advisory locks is making sure transactions 1 and 2
are not concurrently accessing the same table.  that's helpful in the
sense you don't have to worry about out of order locking, but it looks
like T1 can get locks on A and T2 can get locks on B then T1 and T2
will swap the tables they are about to lock.  unless i'm crazy, this
should deadlock.

in other words as you've written above you're getting protection from
row ordering on each table, but not access to the tables themselves.
if both transactions went in A->B order it might work.

merlin

On Sat, Apr 21, 2012 at 1:27 AM, Eliot Gable
<egable+pgsql-general@gmail.com> wrote:
> If I use pg_advisory_lock(), can I lock and unlock a table multiple times in
> both transactions without ever needing to worry about them getting
> deadlocked on rows? Doing select locks on rows is not an option because they
> last until the end of the transaction and I cannot control the order in
> which both transactions grab locks on the different tables involved, and
> each transaction may have an affect on the same rows as the other
> transaction in one or more of the same tables.

You have a Dining Philosophers Problem. Why can you not control the
order in which they acquire their locks? That's one of the simplest
solutions - for instance, all update locks are to be acquired in
alphabetical order of table name, then in primary key order within the
table. Yes, select locks last until the end of the transaction, but
are you really sure you can safely release the locks earlier? By
releasing those advisory locks, you're allowing the transactions to
deadlock, I think. Attempting a manual interleave of these:

Transaction 1 grabs pg_advisory_lock(1)
Transaction 2 grabs pg_advisory_lock(2)
Transaction 1 runs a statement that updates multiple rows on Table A
Transaction 2 runs a statement that deletes multiple rows on Table B
Transaction 1 releases pg_advisory_lock(1)
Transaction 2 releases pg_advisory_lock(2)
Transaction 1 continues processing other stuff
Transaction 2 continues processing other stuff

At this point, Transaction 1 retains the locks on rows of Table A, and
Transaction 2 retains locks on B.

Transaction 1 grabs pg_advisory_lock(2)
Transaction 2 grabs pg_advisory_lock(1)
Transaction 1 runs a statement that updates multiple rows on Table B
-- Lock --
Transaction 2 runs a statement that deletes multiple rows on Table A
-- Deadlock --

Your advisory locks aren't actually doing anything for you here.

ChrisA

On Fri, Apr 20, 2012 at 11:25 AM, Eliot Gable
<egable+pgsql-general@gmail.com> wrote:
> On Fri, Apr 20, 2012 at 11:46 AM, Chris Angelico <rosuav@gmail.com> wrote:
>>
>>
>> You have a Dining Philosophers Problem. Why can you not control the
>> order in which they acquire their locks? That's one of the simplest
>> solutions - for instance, all update locks are to be acquired in
>> alphabetical order of table name, then in primary key order within the
>> table. Yes, select locks last until the end of the transaction, but
>> are you really sure you can safely release the locks earlier? By
>> releasing those advisory locks, you're allowing the transactions to
>> deadlock, I think. Attempting a manual interleave of these:
>>
>> Transaction 1 grabs pg_advisory_lock(1)
>> Transaction 2 grabs pg_advisory_lock(2)
>> Transaction 1 runs a statement that updates multiple rows on Table A
>> Transaction 2 runs a statement that deletes multiple rows on Table B
>> Transaction 1 releases pg_advisory_lock(1)
>> Transaction 2 releases pg_advisory_lock(2)
>> Transaction 1 continues processing other stuff
>> Transaction 2 continues processing other stuff
>>
>> At this point, Transaction 1 retains the locks on rows of Table A, and
>> Transaction 2 retains locks on B.
>>
>> Transaction 1 grabs pg_advisory_lock(2)
>> Transaction 2 grabs pg_advisory_lock(1)
>> Transaction 1 runs a statement that updates multiple rows on Table B
>> -- Lock --
>> Transaction 2 runs a statement that deletes multiple rows on Table A
>> -- Deadlock --
>>
>> Your advisory locks aren't actually doing anything for you here.
>>
>> ChrisA
>
>
> How do you control the order in which cascading deletes occur across tables
> and the order in which they fire the triggers which do the locking?
>
> Within a single function or even within a couple of functions, I can control
> the order. But they are called from within triggers on tables on cascading
> delete or update operations. How do I control that? Some functions only need
> to lock certain tables while other functions need a large set of the tables
> locked. All the functions and triggers lock tables in alphabetical order,
> and I have rolled the locking out to the furthest level based on what
> sub-functions call. However, transaction 1 might call function1() first and
> then function2() and transaction 2 might call function2() first and then
> function1() and those functions might grab locks on Table A and B
> independently, but then when transaction 1 or 2 calls function3(), it needs
> to work with both tables, and then they deadlock. Function1() or function2()
> might be called in a transaction without ever calling function3() in that
> transaction, so it doesn't make sense to lock all the tables in function1()
> and function2() that function3() also locks.

yes I agree: I can see the point of wrapping the locks in advisory
locks when doing row-order locking is difficult or impossible but:

*) you are serializing all deletes even if they don't bump into each other
*) you still need to go in A->B order in both functions

merlin

On Sat, Apr 21, 2012 at 2:25 AM, Eliot Gable
<egable+pgsql-general@gmail.com> wrote:
> How do you control the order in which cascading deletes occur across tables
> and the order in which they fire the triggers which do the locking?

Well, I'd guess that they probably have a well-defined order. However...

> Within a single function or even within a couple of functions, I can control
> the order. But they are called from within triggers on tables on cascading
> delete or update operations. How do I control that? Some functions only need
> to lock certain tables while other functions need a large set of the tables
> locked. All the functions and triggers lock tables in alphabetical order,
> and I have rolled the locking out to the furthest level based on what
> sub-functions call. However, transaction 1 might call function1() first and
> then function2() and transaction 2 might call function2() first and then
> function1() and those functions might grab locks on Table A and B
> independently, but then when transaction 1 or 2 calls function3(), it needs
> to work with both tables, and then they deadlock. Function1() or function2()
> might be called in a transaction without ever calling function3() in that
> transaction, so it doesn't make sense to lock all the tables in function1()
> and function2() that function3() also locks.

... if you honestly can't control this much, then you need to fix your
deadlock problems at a different level.

Your advisory locks could be used to solve this problem, but you'd
need to hold them until transaction end. And you would still need to
acquire them in a specific, known order, because otherwise you just
shift your deadlock point.

You may need to majorly rethink your entire locking system. Perhaps
it's easier to acquire stronger locks and reduce concurrency, or
perhaps you can predict which functions are going to be called and
acquire the appropriate locks (either actual table/row locks or
advisory locks) before calling any of them. If you really cannot
control the transactions, then the only recourse is to accept that
you're going to have some rolled back for deadlocks, and retry those
transactions (and accept the performance penalty).

ChrisA