Re: pg_advisory_lock() and row deadlocks

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.