Re: pg_advisory_lock() and row deadlocks

On Fri, Apr 20, 2012 at 10:27 AM, Eliot Gable
<egable+pgsql-general(at)gmail(dot)com> wrote:
> Is it possible to prevent row deadlocks by using pg_advisory_lock()? For
> example:
>
> Transaction 1 grabs pg_advisory_lock(1)
> Transaction 1 runs a statement that updates multiple rows on Table A
> Transaction 1 releases pg_advisory_lock(1)
> Transaction 1 continues processing other stuff
> Transaction 1 grabs pg_advisory_lock(2)
> Transaction 1 runs a statement that updates multiple rows on Table B
> Transaction 1 releases pg_advisory_lock(2)
> Transaction 1 continues processing other stuff
>
> At the same time...
>
> Transaction 2 grabs pg_advisory_lock(2)
> Transaction 2 runs a statement that deletes multiple rows on Table B
> Transaction 2 releases pg_advisory_lock(2)
> Transaction 2 continues processing other stuff
> Transaction 2 grabs pg_advisory_lock(1)
> Transaction 2 runs a statement that deletes multiple rows on Table A
> Transaction 2 releases pg_advisory_lock(1)
> Transaction 2 continues processing other stuff
>
> If these two transactions run simultaneously, is there any way that they can
> have a row deadlock given the way the pg_advisory_lock() calls are made?
>
> My underlying problem is trying to break row deadlocks due to cascading
> deletes on foreign keys in one transaction colliding with updates to rows in
> another transaction.
>
> 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.

hm, I'm not sure this is going to completely help you. all that
you're getting from advisory locks is making sure transactions 1 and 2
are not concurrently accessing the same table. that's helpful in the
sense you don't have to worry about out of order locking, but it looks
like T1 can get locks on A and T2 can get locks on B then T1 and T2
will swap the tables they are about to lock. unless i'm crazy, this
should deadlock.

in other words as you've written above you're getting protection from
row ordering on each table, but not access to the tables themselves.
if both transactions went in A->B order it might work.

merlin