Re: deadlocks in postgresql 7.2.1

Am Montag, 28. Juli 2003 11:41 schrieb Peter Eisentraut:
> Philipp Reisner writes:
> > Once in a while (about 3 times a day) one or more INSERTS/DELETES simply
> > go into the "waiting" state, and block the whole database. The only way
> > out is to terminate the client connection (i.e. to abort the blocked
> > INSERT/DELETE query)
> >
> > Further investigation with ps -e -o wchan... showed that the backed
> > process was simply sleeping in "semop".
> >
> > Output of ps:
> >
> > 762 ? S 0:00 /usr/lib/postgresql/bin/postmaster
> > 764 ? S 0:00 postgres: stats buffer process
> > 765 ? S 0:00 postgres: stats collector process
> > 24872 ? S 0:00 postgres: sd sd 10.2.2.6 idle in transaction
> > 24873 ? R 68:01 postgres: sd sd 10.2.2.6 SELECT
> > 24932 ? S 3:09 postgres: sd sd 10.2.2.6 idle in transaction
> > 24943 ? R 3:02 postgres: sd sd 10.2.2.6 SELECT
> > 25004 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction
>
> [snip]
>
> All these "idle in transaction" sessions have unfinished transactions that
> are probably holding locks that the INSERT is waiting for. If you
> constantly have loads of "idle in transaction" sessions, you need to fix
> your application.
>
> In 7.3 there is a system table called pg_locks that you can use to
> investigate locks. I don't believe there was one in 7.2.

[ Sorry about this offtopic posting ]

Thanks a lot for the reply!

The applications uses the jdbc driver with autocommit turned off,
commit and rollback. Do you know if the jdbc driver just starts
a new transaction as soon as the last one was ended with commit/
rollback ?

BTW, The development system is still on postgres 7.3.3. The is
the pg_locks table:

relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+-----------------+---------
6520806 | 6520640 | | 20988 | AccessShareLock | t
| | 63435 | 21154 | ExclusiveLock | t
6521098 | 6520640 | | 20988 | AccessShareLock | t
6521041 | 6520640 | | 20988 | AccessShareLock | t
6520884 | 6520640 | | 20988 | AccessShareLock | t
6520894 | 6520640 | | 20988 | AccessShareLock | t
| | 63442 | 21153 | ExclusiveLock | t
| | 57548 | 21140 | ExclusiveLock | t
6520810 | 6520640 | | 20988 | AccessShareLock | t
| | 63434 | 21160 | ExclusiveLock | t
| | 63110 | 21109 | ExclusiveLock | t
6520833 | 6520640 | | 20988 | AccessShareLock | t
| | 57837 | 21096 | ExclusiveLock | t
6521078 | 6520640 | | 20988 | AccessShareLock | t
| | 63437 | 21156 | ExclusiveLock | t
6520702 | 6520640 | | 20988 | AccessShareLock | t
| | 63436 | 21155 | ExclusiveLock | t
| | 63438 | 21157 | ExclusiveLock | t
| | 63440 | 21159 | ExclusiveLock | t
6520814 | 6520640 | | 20988 | AccessShareLock | t
6520899 | 6520640 | | 20988 | AccessShareLock | t
6520652 | 6520640 | | 20988 | AccessShareLock | t
| | 57826 | 20987 | ExclusiveLock | t
| | 63439 | 21158 | ExclusiveLock | t
6521092 | 6520640 | | 20988 | AccessShareLock | t
16757 | 6520640 | | 21153 | AccessShareLock | t
| | 57535 | 20988 | ExclusiveLock | t
(27 rows)

-Philipp
--
: Dipl-Ing Philipp Reisner Tel +43-1-8178292-50 :
: LINBIT Information Technologies GmbH Fax +43-1-8178292-82 :
: Schönbrunnerstr 244, 1120 Vienna, Austria http://www.linbit.com :