troubleshooting deadlocks
От | Ed L. |
---|---|
Тема | troubleshooting deadlocks |
Дата | |
Msg-id | 200411082129.43787.pgsql@bluepolka.net обсуждение исходный текст |
Ответы |
Re: troubleshooting deadlocks
|
Список | pgsql-general |
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_table for statement: UPDATE public.this_table SET foo = 1; blocked by process 15600. Process 15600 waits on transaction 9388 for ShareLock on public.that_table for statement: UPDATE public.that_table 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_backend_pid(S.backendid) as pid, db.datname||'.'||n.nspname||'.'||r.relname as relation, l.mode, substring(pg_stat_get_backend_activity(S.backendid), 0, 30) as query FROM pg_user dbu, (SELECT pg_stat_get_backend_idset() AS backendid) AS S, pg_database db, pg_locks l, pg_class r, pg_namespace n WHERE db.oid = pg_stat_get_backend_dbid(S.backendid) AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid) AND l.pid = pg_stat_get_backend_pid(S.backendid) 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;
В списке pgsql-general по дате отправления: