Re: detecting deadlocks

Tom Lane wrote:
> "Jonathan Ellis" <jbe(at)familyellis(dot)org> writes:
> > I get this logged
> > logger: ERROR: Deadlock detected.
> > logger: ^ISee the lock(l) manual page for a possible cause.
>
> > but that's kind of useless to actually fix the problem. Is there a way
to
> > get it to log which two transactions are deadlocking?
>
> I think we could persuade the lock manager to return more info than it
> does now, but I'm not sure how to translate the low-level info (backend
> IDs and lock tags) into something useful to a user. How would you want
> the other transactions to be identified?

I don't know what's within the realm of possibility here, but something
like this could help identify the problem
* sql from both transactions that was deadlocked, i.e. the statement
where the backend realized it couldn't keep executing
* list of locks held by each (so you could guess what had preceeded)

My deadlock is happening on a table that is updated in at
least a dozen places, and I've tried to make sure transactions lock
tables in the same order, but I must have missed something. It sure
would be nice to be able to narrow it down some.

-Jonathan