Thread: detecting deadlocks
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'm on 7.1.3... -Jonathan
"Jonathan Ellis" <jbe@familyellis.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?
Well, one of them is the one that died ;-).
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?
regards, tom lane
Tom Lane wrote:
> "Jonathan Ellis" <jbe@familyellis.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
"Jonathan Ellis" <jbe@familyellis.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
> > * 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?
All we have now is:
o Add SHOW command to display locks
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Tue, Jan 22, 2002 at 07:25:02PM -0500, Tom Lane wrote: > 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. If the SQL queries are being logged, then the PID would be enough to find the query in the logfile. Come to think of it though, if the deadlock message is logged, then it would include the PID anyway... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Terrorists can only take my life. Only my government can take my freedom.