Re: REINDEX deadlock - Postgresql -9.1

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(dot)ru(at)gmail(dot)com> wrote:

> On Wed, Feb 6, 2013 at 1:28 AM, Anoop K <anoopk6(at)gmail(dot)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(dot)ru(at)gmail(dot)com
>