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@svana.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