Re: REINDEX deadlock - Postgresql -9.1

On Sun, Feb 10, 2013 at 9:55 PM, Anoop K <anoopk6(at)gmail(dot)com> 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(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
>
>

--
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