Re: pg_advisory_lock() and row deadlocks

On Sat, Apr 21, 2012 at 2:25 AM, Eliot Gable
<egable+pgsql-general(at)gmail(dot)com> wrote:
> How do you control the order in which cascading deletes occur across tables
> and the order in which they fire the triggers which do the locking?

Well, I'd guess that they probably have a well-defined order. However...

> Within a single function or even within a couple of functions, I can control
> the order. But they are called from within triggers on tables on cascading
> delete or update operations. How do I control that? Some functions only need
> to lock certain tables while other functions need a large set of the tables
> locked. All the functions and triggers lock tables in alphabetical order,
> and I have rolled the locking out to the furthest level based on what
> sub-functions call. However, transaction 1 might call function1() first and
> then function2() and transaction 2 might call function2() first and then
> function1() and those functions might grab locks on Table A and B
> independently, but then when transaction 1 or 2 calls function3(), it needs
> to work with both tables, and then they deadlock. Function1() or function2()
> might be called in a transaction without ever calling function3() in that
> transaction, so it doesn't make sense to lock all the tables in function1()
> and function2() that function3() also locks.

... if you honestly can't control this much, then you need to fix your
deadlock problems at a different level.

Your advisory locks could be used to solve this problem, but you'd
need to hold them until transaction end. And you would still need to
acquire them in a specific, known order, because otherwise you just
shift your deadlock point.

You may need to majorly rethink your entire locking system. Perhaps
it's easier to acquire stronger locks and reduce concurrency, or
perhaps you can predict which functions are going to be called and
acquire the appropriate locks (either actual table/row locks or
advisory locks) before calling any of them. If you really cannot
control the transactions, then the only recourse is to accept that
you're going to have some rolled back for deadlocks, and retry those
transactions (and accept the performance penalty).

ChrisA