Обсуждение: postgres deadlocks
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
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/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.