troubleshooting deadlocks

[Ed L.]

I'm wrestling with tracking down a deadlock. Here's a 7.4.6 deadlock
message:

ERROR: deadlock detected
DETAIL: Process 15655 waits for ShareLock on transaction 9381; blocked by
process 15600.
Process 15600 waits for ShareLock on transaction 9388; blocked by process
15655.

I know the original statement is printed right after this, but with complex
triggers doing lots of write queries, I'm finding it difficult to identify
which subsequent query in the trigger is really the one immediately
preceding the deadlock. It would be helpful in debugging if the error
message included info on which tables are involved, maybe even the
deadlocking query itself, in the "DETAIL" output for future releases.
Maybe something like:

DETAIL: Process 15655 waits on transaction 9381 for ShareLock on
public.this_tab le for statement: UPDATE public.this_tab le SET foo = 1;
blocked by process 15600.
Process 15600 waits on transaction 9388 for ShareLock on public.that_tab le
for statement: UPDATE public.that_tab le SET bar = 1; blocked by process
15655.

Maybe there is a simpler way to troubleshoot this that I'm overlooking?

ps - Here's a query I use to show locks, maybe others might find it useful
(or find bugs in it):

SELECT dbu.usename as locker, l.mode as locktype,
pg_stat_get_bac kend_pid(S.back endid) as pid,
db.datname||'.' ||n.nspname||'. '||r.relname as relation, l.mode,
substring(pg_st at_get_backend_ activity(S.back endid), 0, 30) as query
FROM pg_user dbu,
(SELECT pg_stat_get_bac kend_idset() AS backendid) AS S,
pg_database db, pg_locks l, pg_class r, pg_namespace n
WHERE db.oid = pg_stat_get_bac kend_dbid(S.bac kendid)
AND dbu.usesysid = pg_stat_get_bac kend_userid(S.b ackendid)
AND l.pid = pg_stat_get_bac kend_pid(S.back endid)
AND l.relation = r.oid
AND l.database = db.oid
AND r.relnamespace = n.oid
AND l.granted
ORDER BY db.datname, n.nspname, r.relname, l.mode;


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Re: troubleshooting deadlocks

"Ed L." <pgsql@bluepolk a.net> writes:[color=blue]
> I know the original statement is printed right after this, but with complex
> triggers doing lots of write queries, I'm finding it difficult to identify
> which subsequent query in the trigger is really the one immediately
> preceding the deadlock. It would be helpful in debugging if the error
> message included info on which tables are involved, maybe even the
> deadlocking query itself, in the "DETAIL" output for future releases.[/color]

I suppose the problem here has to do with conflicting SELECT FOR UPDATEs
from foreign-key references. This does actually work nicely in CVS tip,
for non-deferred FKs:

regression=# select boo(2);
ERROR: insert or update on table "zork" violates foreign key constraint "zork_f1_fk ey"
DETAIL: Key (f1)=(2) is not present in table "zork2".
CONTEXT: SQL statement "insert into zork values( $1 )"
PL/pgSQL function "boo" line 2 at SQL statement
regression=#

For deferred FKs you're pretty much out of luck, because the original
statement is long gone by the time the FK trigger fires (and no, it's
not reasonable to keep a copy around). The difficulty in making 7.4
do this is that in 7.4 AFTER triggers never fire within a user function,
so you're always dealing with the "deferred" situation.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Ed L.]

Re: troubleshooting deadlocks

On Tuesday November 9 2004 10:36, Tom Lane wrote:[color=blue]
> "Ed L." <pgsql@bluepolk a.net> writes:[color=green]
> > I know the original statement is printed right after this, but with
> > complex triggers doing lots of write queries, I'm finding it difficult
> > to identify which subsequent query in the trigger is really the one
> > immediately preceding the deadlock. It would be helpful in debugging
> > if the error message included info on which tables are involved, maybe
> > even the deadlocking query itself, in the "DETAIL" output for future
> > releases.[/color]
>
> I suppose the problem here has to do with conflicting SELECT FOR UPDATEs
> from foreign-key references.[/color]

That appears to be the issue. We upgraded to 7.4.6 (thanks to slony,
production downtime was minimal), then used 7.4.6 debug output to track
relations, then guessed on fkey references, dropped them, and viola, no
more deadlock.

Thanks,
Ed


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org