Re: pg_advisory_lock() and row deadlocks

On Sat, Apr 21, 2012 at 1:27 AM, Eliot Gable
<egable+pgsql-general(at)gmail(dot)com> 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