Re: pg_advisory_lock() and row deadlocks

On Fri, Apr 20, 2012 at 11:25 AM, Eliot Gable
<egable+pgsql-general(at)gmail(dot)com> wrote:
> On Fri, Apr 20, 2012 at 11:46 AM, Chris Angelico <rosuav(at)gmail(dot)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