Re: deadlocks

On Tue, Jun 17, 2003 at 06:10:06PM -0300, Felipe Schnack wrote:
> Hi all!

> I'm having a very strange deadlock problem in PostgreSQL 7.3.2 under
> RedHat Linux. All connections to this database come from Tomcat's 4.1.24
> connection pooling mechanism, and for some reason frequently I have lots
> of INSERTs in "waiting" state. But what they're writing for?? To me
> seems one insert is waiting for the other one... anyone have tips on how
> can I find what's my problem? Here is a listing I got on my server using
> "ps -ax |grep post"

See those "idle in transactions"? A client should try to avoid sitting in a
transaction and it could hold up other transactions (presumably what the
INSERTs are doing). If you have a query log, lookup the queries executed in
the idle ones to see when the holdup is.

> 30132 ? S 0:01 postgres: postgres w2 192.168.5.54 INSERT waiting
> 30133 ? S 0:01 postgres: postgres w2 192.168.5.54 idle in transactio
> 30145 ? S 0:00 postgres: postgres w2 192.168.5.54 idle in transactio
> 30168 ? S 0:00 postgres: postgres w2 192.168.5.54 INSERT waiting
> 30180 ? S 0:00 postgres: postgres w2 192.168.5.54 INSERT waiting
> 30183 ? S 0:01 postgres: postgres w2 192.168.5.54 idle
> 30184 ? S 0:00 postgres: postgres w2 192.168.5.54 idle
> 30185 ? S 0:00 postgres: postgres w2 192.168.5.54 INSERT waiting

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington