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