Обсуждение: REINDEX deadlock - Postgresql -9.1
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. 1. *idle in transaction * 2. *REINDEX waiting * 3. *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. Any clues .. Thanks Anoop
On 2/6/2013 1:28 AM, Anoop K wrote: > 3 connections are going in to some dead lock state. > > 1. *idle in transaction * > 2. *REINDEX waiting * > 3. *SELECT waiting * > you need to track down what resources are being locked by those processes, by joining pg_stat_activity against pg_locks and.... (been awhile, I forget the magic join query that gives you useful info here) IDLE in Transaction means that connection did a BEGIN; but isn't executing any commands at all at present and is just sitting there. This should never happen, and is generally a sign of buggy application software, or poorly designed ORM or something. -- john r pierce 37N 122W somewhere on the middle of the left coast
On Wed, Feb 6, 2013 at 1:28 AM, Anoop K <anoopk6@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.ru@gmail.com
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.ru@gmail.com> wrote: > On Wed, Feb 6, 2013 at 1:28 AM, Anoop K <anoopk6@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.ru@gmail.com >
On 2/10/2013 9:55 PM, Anoop K 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*> ? JDBC has a wretched habit of autostarting transactions on any query if the connection is not in autocommit mode. if you don't want to use autocommit mode, then you need to issue Commit() calls after each batch of queries, even if the queries are read only, to release any implied locks. -- john r pierce 37N 122W somewhere on the middle of the left coast
Yes, we do that. On Mon, Feb 11, 2013 at 11:53 AM, John R Pierce <pierce@hogranch.com> wrote: > On 2/10/2013 9:55 PM, Anoop K 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*> ? > > > JDBC has a wretched habit of autostarting transactions on any query if the > connection is not in autocommit mode. if you don't want to use autocommit > mode, then you need to issue Commit() calls after each batch of queries, > even if the queries are read only, to release any implied locks. > > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > >
On Sun, Feb 10, 2013 at 9:55 PM, Anoop K <anoopk6@gmail.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.ru@gmail.com> wrote: >> >> On Wed, Feb 6, 2013 at 1:28 AM, Anoop K <anoopk6@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.ru@gmail.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.ru@gmail.com
On 2/10/2013 10:25 PM, Anoop K wrote: > Yes, we do that. well, you need to figure out which connection isn't doing that, as one of them is leaving a long running transaction pending. as I said, join pg_stat_activity.pid with pg_locks and whatever to find out what tables its locking on. try these http://wiki.postgresql.org/wiki/Lock_Monitoring -- john r pierce 37N 122W somewhere on the middle of the left coast