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.
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
The output looks something ilke this:
schema | table | transaction | pid | mode
| granted
----------+---------+-------------+-------+--------------------------+---------
schema_1 | idx_e | | 24058 | AccessShareLock | t
schema_1 | d | | 24084 | AccessShareLock | t
schema_1 | d | | 24084 | RowExclusiveLock | t
schema_1 | e | | 24084 | AccessShareLock | t
schema_1 | e | | 24084 | RowExclusiveLock | t
schema_1 | e | | 24008 | AccessShareLock | t
schema_1 | e | | 24008 | RowExclusiveLock | t
schema_1 | idx_e | | 24081 | AccessShareLock | t
schema_1 | m | | 24065 | ShareUpdateExclusiveLock | t
schema_1 | idx_e | | 24091 | AccessShareLock | t
schema_1 | m | | 24065 | ShareUpdateExclusiveLock | t
schema_1 | idx_e | | 24008 | AccessShareLock | t
schema_1 | idx_e | | 24059 | AccessShareLock | t
schema_1 | idx_e | | 24071 | AccessShareLock | t
schema_1 | idx_e | | 24037 | AccessShareLock | t
d, e, and m are tables, idx_e is an index on the table e.
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.)
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
Jack Orenstein
