------ Original Message ------
From: "Merlin Moncure" <mmoncure@xxxxxxxxx>
To: "Victor Sterpu" <victor@xxxxxxxx>
Cc: "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx>
Sent: 4/2/2014 6:49:28 PM
Subject: Re: Lock problem
On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu <victor@xxxxxxxx> wrote:
Hello
I have a problem that it seems to be very hard to debug.
Problem is from some postgresql locks. I use PostgreSQL 9.1.8.
I runned this query to fid the locks:
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.current_query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.procpid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid
AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
WHERENOT bl.granted;
The result is a recursive lock.
Pid 10665 is blocked by pid 9844 and pid 9844 is blocked by 10665.
These 2 inserts are in 2 separate transactions.
Can this be a postgresql bug?
blocked_pid blocked_user blocking_statement blocking_duration
blocking_pid blocking_user blocked_statement blocked_duration
10665 postgres <IDLE> in transaction
"<IDLE> in transaction" is a locking red flag. It means your
application has opened a transaction and is sitting there holding the
transaction open. This is a very common cause of subtle application
locking bugs. It can be legit if the application is doing heavy
processing during a transaction or you simply raced to an idle
transaction in pg_stat_activity, but in my experience 95%+ of the time
it means transaction leakage which in turn leads to locking problems.
merlin
This is weird because all of my transaction have commit or rollback. I
don't leave unterminated transactions.
I can't see how this is happening.
Is there a possibility that some network problems generate this problem?
If this is the case is these some server protection for this situation?
But why a unterminated transaction blocks all table operations?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
