Search Postgresql Archives

Re: Lock problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm sure is not right, but is a there a server side solution for such sitations?
A configuration - timeout for idle transactions.

------ Original Message ------
From: "Victor Sterpu" <victor@xxxxxxxx>
To: "Victor Sterpu" <victor@xxxxxxxx>; "Merlin Moncure" <mmoncure@xxxxxxxxx>
Cc: "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx>
Sent: 4/2/2014 9:13:22 PM
Subject: Re[2]:  Lock problem

There may pass a few days or weeks until next lock.
But I don't undesrtand why the whole table is locked if there is one uncommited transaction.

The following scenario might be the cause:
1. Transaction is started
2. the client application is closed because of a power surge, the started transaction will never be commited 3. from the server point of view there is a unfinished transaction that will block future statements

Is this normal behaviour?


------ Original Message ------
From: "Victor Sterpu" <victor@xxxxxxxx>
To: "Merlin Moncure" <mmoncure@xxxxxxxxx>
Cc: "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx>
Sent: 4/2/2014 7:19:06 PM
Subject: Re:  Lock problem

I followed all your advice and it is obiuos that this log will show exactly what I need to debug the situation.
Great tip, thank you.

------ Original Message ------
From: "Merlin Moncure" <mmoncure@xxxxxxxxx>
To: "Victor Sterpu" <victor@xxxxxxxx>
Cc: "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx>
Sent: 4/2/2014 7:08:08 PM
Subject: Re:  Lock problem

On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu <victor@xxxxxxxx> wrote:
 All my transactions have commit or rollback.

Well, you have to verify that. There's a couple of ways to do it.
Probably the most direct is to:
1. Make sure database is logging pids in log_line_prefix (this is a
good idea all around)
2. turn on all statement logging (be advised: this can eat a lot of
log space and slow down the server).

Those two changes do not require a restart. A pg_ctl reload should
be sufficient.

Once you can do that, you should be able to locate database sessions
per pg_stat_activity that are 'idle in transaction' for a long time
without activity (anything over a second or so should be suspicious).
That will give the pid which you can then use to grep through the
statement log.

Common culprits are:
*) Dubious connection pooling solutions (php pconnect comes to mind)
*) Bad error handling logic in application (say, badly handled thrown exception)

merlin



-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux