Re: detecting deadlocks

"Jonathan Ellis" <jbe(at)familyellis(dot)org> writes:
> Tom Lane wrote:
>> 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

SQL from other backends isn't readily available, and showing it would
create security issues anyway (eg, could expose sensitive data values
to someone who doesn't have the right to see 'em). About the only thing
that we could provide given the current shared-memory datastructures is
the PID of each backend involved in the deadlock cycle. This'd be
somewhat useful, I guess, but not really a direct indicator of which
command was being executed.

> * list of locks held by each (so you could guess what had preceeded)

The trouble is that what we've got at that level is a "lock tag" that's
just some numbers. It's difficult (maybe even impossible in some cases)
to reverse that into something a user would recognize. Still, we could
probably do it in enough cases to be useful.

Hey Bruce, do we have a TODO item about giving better deadlock error
messages?

regards, tom lane