Re: Interpreting pg_locks; looking for deadlock

On Tue, Jan 24, 2006 at 04:35:59PM -0500, jao@geophile.com wrote:
> I have a postgresql 7.4.8 database which has the same table
> declarations in several schemas. My application accesses each schema
> from a single thread; there is never more than one thread accessing a
> schema at a time.
>
> To try increasing concurrency, I've tried using multiple threads per
> schema. The application quickly locked up, and I suspect deadlock, but
> pg_locks doesn't seem to show deadlock.

PostgreSQL should detect deadlock and abort one of the queries, so
if there's deadlock on the database side then you should be getting
error messages to that effect, like this:

ERROR:  deadlock detected
DETAIL:  Process 2284 waits for ShareLock on transaction 425383; blocked by process 2281.
Process 2281 waits for ShareLock on transaction 425382; blocked by process 2284.

Do you have any foreign key constraints?  If so then consider
upgrading to 8.1, which uses a new lock type that should eliminate
a lot of blocking/deadlock problems commonly encountered when using
foreign keys.

What platform, programming language, and database interface are you
using?  There could be a problem on the client side, especially if
you're using threads with a database interface that isn't thread-
friendly.

> My pg_locks query is as follows:
>
>    select        ns.nspname as "schema",
>        c.relname as "table",
>        L.transaction,
>        L.pid,
>        L.mode,
>        L.granted
>    from pg_locks L, pg_class c, pg_namespace ns
>    where L.relation = c.oid
>    and   c.relnamespace = ns.oid
>    and   ns.nspowner >= 100

This query looks only at locks on relations.  A common cause of
deadlock is locks on transaction ids, as in the example error message
above.  And since PostgreSQL should be able to detect deadlock after
deadlock_timeout milliseconds (default 1000, or 1 second), you'll
seldom see the situation in pg_locks even when it does happen.

> What puzzles me is that there are no 'f' entries in the granted
> column. (This is partial output, but the complete output has no f
> entries.)

Do outer joins against pg_class and pg_namespace so the query also
shows transaction id locks, or omit the joins altogether and cast
the relation oids to regclass (casting a relation oid to regclass
shows the relation's name; your search_path determines whether the
relation's schema name is also shown).

> If this is deadlock, then why don't I see granted = 'f'? And if it
> isn't deadlock, then why do so many backend processes appear to be
> stuck, e.g. (ps output):
>
>      24057 ?        S<     0:01 postgres: risdba ris 127.0.0.1 UPDATE waiting
>      24058 ?        S<     0:01 postgres: risdba ris 127.0.0.1 UPDATE waiting
>      24059 ?        S<     0:01 postgres: risdba ris 127.0.0.1 UPDATE waiting

Blocked queries don't necessarily imply deadlock -- deadlock happens
when transaction A holds a lock that transaction B wants and
transaction B holds a lock that transaction A wants.  As mentioned
above, PostgreSQL should detect this situation and abort one of the
queries with a "deadlock detected" error message.

Try querying pg_locks for all ungranted locks, not just those on
relations.  I suspect you'll see those updates waiting on a transaction
id; if so then find out what transaction holds the lock and why
it's not finishing.

--
Michael Fuhr