On Sat, Apr 21, 2012 at 1:27 AM, Eliot Gable <egable+pgsql-general@xxxxxxxxx> wrote: > If I use pg_advisory_lock(), can I lock and unlock a table multiple times in > both transactions without ever needing to worry about them getting > deadlocked on rows? Doing select locks on rows is not an option because they > last until the end of the transaction and I cannot control the order in > which both transactions grab locks on the different tables involved, and > each transaction may have an affect on the same rows as the other > transaction in one or more of the same tables. 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
