Re: pg_advisory_lock() and row deadlocks

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