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 :