On Sun, Feb 10, 2013 at 9:55 PM, Anoop K <anoopk6@xxxxxxxxx> wrote: > 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> ? There are no other scenarios for 'idle in transaction'. Unfortunately I am not a JDBC specialist but googling 'postgresql jdbc idle in transaction' shows a lot of threads where people discuss such things. I am pretty sure there is an answer among them. > > Anoop > > > On Mon, Feb 11, 2013 at 11:16 AM, Sergey Konoplev <gray.ru@xxxxxxxxx> wrote: >> >> On Wed, Feb 6, 2013 at 1:28 AM, Anoop K <anoopk6@xxxxxxxxx> 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.ru@xxxxxxxxx > > -- 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.ru@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
