On Fri, Apr 20, 2012 at 11:46 AM, Chris Angelico <rosuav@xxxxxxxxx> 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 BTransaction 1 releases pg_advisory_lock(1)
Transaction 2 releases pg_advisory_lock(2)
Transaction 1 continues processing other stuff
Transaction 2 continues processing other stuffAt 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.
