On Fri, Apr 20, 2012 at 11:46 AM, Chris Angelico <ros...@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.
