Re: Database deadlock/hanging

On Wed, 2007-03-07 at 10:08 -0600, John Gateley wrote:
> I'm guessing it's something related to table locks.
...
> Any pointers on what I should be looking for to prevent this from
> happening again? What information I should be tracking to figure
> out what is exactly happening?

Your inserts almost certainly have a table or index exclusively locked
and thereby causing a backlog of selects.

You can fish current and waiting locks out of pg_locks, but those use
internal identifiers rather than names. Here's a view that will make
pg_locks more readable:

rkh(at)csb-dev=> CREATE OR REPLACE VIEW pgutils.locks AS
SELECT l.pid, d.datname AS "database", n.nspname AS "schema", c.relname AS relation, l.locktype, l."mode",
CASE l."granted"
WHEN true THEN 'RUN'::text
ELSE 'WAIT'::text
END AS state, a.usename, a.current_query, to_char(now() - a.query_start, 'HH24:MI:SS'::text) AS duration
FROM pg_locks l
JOIN pg_database d ON l."database" = d.oid
JOIN pg_class c ON l.relation = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_stat_activity a ON l.pid = a.procpid
ORDER BY l.pid, d.datname, n.nspname, c.relname, l."granted";

rkh(at)csb-dev=> select * from pgutils.locks ;
pid | database | schema | relation | locktype | mode | state | usename | current_query | duration
-------+----------+------------+--------------------+----------+-----------------+-------+---------+---------------+----------
28434 | csb-dev | pg_catalog | pg_class | relation | AccessShareLock | RUN | rkh | <IDLE> | 00:00:21
28434 | csb-dev | pg_catalog | pg_class_oid_index | relation | AccessShareLock | RUN | rkh | <IDLE> | 00:00:21
28434 | csb-dev | pg_catalog | pg_locks | relation | AccessShareLock | RUN | rkh | <IDLE> | 00:00:21
28434 | csb-dev | pg_catalog | pg_namespace | relation | AccessShareLock | RUN | rkh | <IDLE> | 00:00:21
28434 | csb-dev | pg_catalog | pg_stat_activity | relation | AccessShareLock | RUN | rkh | <IDLE> | 00:00:21
28434 | csb-dev | pgutils | locks | relation | AccessShareLock | RUN | rkh | <IDLE> | 00:00:21
(6 rows)

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.