Re: postgres deadlocks

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