postgres deadlocks

On Wed, Jul 25, 2001 at 05:38:06PM -0400, Joseph Shraibman wrote:

My postgres deadlocked again today. Logs indicated that before the
nightly vacuum -z everything was fine, but after it was locked. The
postgres.log contained the end of the vacuum which appeared to be
consistant with the other vacuums in the log. A ps showed no vacuuming
processes, but it did show one 'idle in transaction'. This problem last
happened in Februrary when I was using 7.0.3.

select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

[root@p1 /var/log] ps ax | grep postgres
16973 ? S 0:36 postgres: user2 owl 127.0.0.1 idle
17105 ? S 0:01 postgres: user2 owl 127.0.0.1 idle
17122 ? S 1:51 postgres: user2 owl 127.0.0.1 idle
17302 ? S 1:19 postgres: user2 owl 127.0.0.1 UPDATE waiting
17453 ? S 0:32 postgres: user2 owl 127.0.0.1 idle
18202 ? S 0:03 postgres: user2 owl 127.0.0.1 idle
18351 ? S 0:12 postgres: user2 owl 127.0.0.1 idle in
transaction
18352 ? S 0:00 postgres: user2 owl 127.0.0.1 idle
7137 ? S 0:00 postgres: user2 owl 127.0.0.1 idle

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Whenever I've seen something like this it's turned out to be a deadlock
between the process in the transaction and the transaction (UPDATE in this
case) waiting.

I think it's generally bad for a client to be idle in a transaction?

Stephen

Stephen Robert Norris wrote:

On Wed, Jul 25, 2001 at 05:38:06PM -0400, Joseph Shraibman wrote:

My postgres deadlocked again today. Logs indicated that before the
nightly vacuum -z everything was fine, but after it was locked. The
postgres.log contained the end of the vacuum which appeared to be
consistant with the other vacuums in the log. A ps showed no vacuuming
processes, but it did show one 'idle in transaction'. This problem last
happened in Februrary when I was using 7.0.3.

select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

[root@p1 /var/log] ps ax | grep postgres
16973 ? S 0:36 postgres: user2 owl 127.0.0.1 idle
17105 ? S 0:01 postgres: user2 owl 127.0.0.1 idle
17122 ? S 1:51 postgres: user2 owl 127.0.0.1 idle
17302 ? S 1:19 postgres: user2 owl 127.0.0.1 UPDATE waiting
17453 ? S 0:32 postgres: user2 owl 127.0.0.1 idle
18202 ? S 0:03 postgres: user2 owl 127.0.0.1 idle
18351 ? S 0:12 postgres: user2 owl 127.0.0.1 idle in
transaction
18352 ? S 0:00 postgres: user2 owl 127.0.0.1 idle
7137 ? S 0:00 postgres: user2 owl 127.0.0.1 idle

Whenever I've seen something like this it's turned out to be a deadlock
between the process in the transaction and the transaction (UPDATE in this
case) waiting.

I think it's generally bad for a client to be idle in a transaction?

Not generally. I program could do a SELECT FOR UPDATE, do some
processing, do an UPDATE, and then do an END, but my program wasn't
doing that (as far as I can tell, and I've been looking very closely).

But *something* got idle in trasaction.

Even more fustrating it seemed that my whole database was locked, not
just one table, and what could have locked the whole thing except
vacuum?

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com

On Wed, Jul 25, 2001 at 06:32:48PM -0400, Joseph Shraibman wrote:

But *something* got idle in trasaction.

Even more fustrating it seemed that my whole database was locked, not
just one table, and what could have locked the whole thing except
vacuum?

My guess is that if vacuum was processing say pg_shadow when it locked, that
could prevent other people from logging in. Strange though, because vacuum
checks for all locks *before* actually doing anything, so vacuum would be
waiting, not actually locked.

Kill the transaction with kill -INT is the way out.

You should find out what exactly was being updated that stop
mid-transaction.

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/