 | |
Re: [BUGS] deadlocks in postgresql 7.2.1
mailing.database.pgsql-bugs mailing.database.pgsql-bugs
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: [BUGS] deadlocks in postgresql 7.2.1 -
07-28-2003
, 04:33 AM
|
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.
--
Peter Eisentraut peter_e (AT) gmx (DOT) net
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
#2
| |||||
| |||||
|
|
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. |
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
Quote:
|
| 63435 | 21154 | ExclusiveLock | t 6521098 | 6520640 | | 20988 | AccessShareLock | t |
6520884 | 6520640 | | 20988 | AccessShareLock | t
6520894 | 6520640 | | 20988 | AccessShareLock | t
Quote:
|
| 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 |
6520652 | 6520640 | | 20988 | AccessShareLock | t
Quote:
|
| 57826 | 20987 | ExclusiveLock | t | 63439 | 21158 | ExclusiveLock | t 6521092 | 6520640 | | 20988 | AccessShareLock | t |
Quote:
|
| 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 :
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly
#3
| |||
| |||
|
|
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 21226 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction 21228 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction 21229 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction 21230 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction 21231 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction 21232 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction 21227 ? S 0:01 postgres: sd sd 10.2.1.5 INSERT waiting 21391 pts/1 S 0:00 grep postg |
is probably waiting for a lock that is held by one of your idle
transactions. You need to fix your client logic to not hold
transactions open for long periods.
(Now that you've moved up to 7.3, you could look at pg_locks to see just
which idle transaction is blocking the INSERT.)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
#4
| |||
| |||
|
| 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 ? Yes, it does. |
can commit(), and *then* setAutocommit(true) if you want, the result is
the same).
I hope, it helps....
Dima
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.