We analyzed the application side. It doesn't seem to be create a transaction and keep it open. StackTraces indicate that it is BLOCKED in JDBC openConnection.
Any JDBC driver issue or other scenarios which can result in <*idle in transaction*> ? Anoop On Mon, Feb 11, 2013 at 11:16 AM, Sergey Konoplev <gray...@gmail.com> wrote: > On Wed, Feb 6, 2013 at 1:28 AM, Anoop K <anoo...@gmail.com> wrote: > > We are hitting a situation where REINDEX is resulting in postgresql to > go to > > dead lock state for ever. On debugging the issue we found that > > 3 connections are going in to some dead lock state. > > > > idle in transaction > > REINDEX waiting > > SELECT waiting > > > > All these connections are made in the same minute. Once in deadlock > state we > > are not able to make new connections to db.(So not able to view pg_locks > > also). New connections appears as 'startup waiting' in ps output. > Initially > > we suspected <idle in transaction> is the result of not closing a > > connection. But it seems it got stuck after creating a connection and is > not > > able to proceed. > > The 'idle in transaction' means that someone started a transaction > (BEGIN) and did not finished it (COMMIT/ROLLBACK) in the first > connections. The 'startup waiting' message means that something got an > exclusive lock on some system catalogs. > > You should not allow persistent or long running 'idle in transaction's > that could affect tables that are actively used by other connections > mostly if these tables are system ones. You need to find out what > caused this 'idle in transaction', in the other words why the > transaction was not finished, to solve the problem. > > > > > Any clues .. > > > > Thanks > > Anoop > > > > -- > Sergey Konoplev > Database and Software Architect > http://www.linkedin.com/in/grayhemp > > Phones: > USA +1 415 867 9984 > Russia, Moscow +7 901 903 0499 > Russia, Krasnodar +7 988 888 1979 > > Skype: gray-hemp > Jabber: gray...@gmail.com >
