pg_advisory_lock() and row deadlocks

From: Eliot Gable <egable+pgsql-general(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_advisory_lock() and row deadlocks
Date: 2012-04-20 15:27:22
Message-ID: CAD-6L_U=8hpnPvp1NCtEbFG=+ekW63xJfnpyMZnmWwQDd3Hngg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be
a crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live;
not live to eat.) ~Marcus Tullius Cicero

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vincenzo Romano 2012-04-20 15:36:59 How to drop a temporary view?
Previous Message F. BROUARD / SQLpro 2012-04-20 14:43:09 Re: Why did pg_relation_filepath does not give a correct path ?