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 waitingAll 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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 waitingAll 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 isnot
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/grayhempPhones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979Skype: 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:
Show quoted text
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 waitingAll 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/grayhempPhones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979Skype: 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general