On Fri, Apr 20, 2012 at 11:25 AM, Eliot Gable <egable+pgsql-general@xxxxxxxxx> wrote: > 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 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
