[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Spurious failure to obtain row lock possible in PG 9.1?
From: henk de wit <henk53602 () hotmail ! com>
Date: 2012-09-29 12:40:35
Message-ID: COL104-W5618EFE88792B13A894923F5810 () phx ! gbl
[Download RAW message or body]
Hi there,
> henk de wit wrote:
> > I'm using Postgres 9.1 on Debian Lenny and via a Java server (JBoss AS
> > I'm "pretty" sure there's really no other process that has the lock,
> as I'm the only one on a test DB.
> > If I execute the query immediately again, it does succeed in obtaining
> the lock. I can however not
> > reproduce this via e.g. PGAdmin.
>
>
> There must be at least a second database connection that holds
> locks on the objects you need.
> Look in pg_stat_activity if you see other connections.
>
> It is probably a race condition of some kind.
It indeed most likely was, but not exactly the kind of race condition I had in mind.
I was (wrongfully) thinking that a "... for update nowait" lock, would only not wait \
for other "... for update nowait" locks. However, as it turned out it also \
immediately returns with the error code if there's a kind of transitive "normal" lock \
related to a plain insert or update elsewhere (plain = without a 'for update' \
clause). As I was the only one on the Database, I was pretty sure there was no other \
"... for update nowait" query executing, but there *was* another parallel insert of a \
row that had a foreign key to the entry in the table I was trying to lock explicitly. \
That insert caused the lock in the other query to immediately fail. To me this was \
quite unexpected, but that's probably just me. What I thus actually need from PG is a \
"nowaitforupdate" or such thing; e.g. if there's a normal insert going on with a FK \
that happens to reference that row, it's okay to wait. The only thing I don't want to \
wait for is explicit locks that are hold by application code. I've worked around the \
issue by creating a separate table called "customer_lock" without any foreign keys \
from it or to it. It's used exclusively for obtaining those explicit locks. It \
violates the relational model a bit, but it does work. Thanks for your help! \
[Attachment #3 (text/html)]
<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Tahoma
}
--></style></head>
<body class='hmmessage'><div dir='ltr'>
Hi there,<br><div><div id="SkyDrivePlaceholder"></div><br>> henk de wit \
wrote:<br>> > I'm using Postgres 9.1 on Debian Lenny and via a Java server \
(JBoss AS<br>> > I'm "pretty" sure there's really no other process that has the \
lock,<br>> as I'm the only one on a test DB.<br>> > If I execute the query \
immediately again, it does succeed in obtaining<br>> the lock. I can however \
not<br>> > reproduce this via e.g. PGAdmin.<br>> <br>> <br>> \
There must be at least a second database connection that holds<br>> locks on the \
objects you need.<br>> Look in pg_stat_activity if you see other \
connections.<br>> <br>> It is probably a race condition of some \
kind.</div><div><br></div><div>It indeed most likely was, but not exactly the kind of \
race condition I had in mind.</div><div><br></div><div>I was (wrongfully) thinking \
that a "... for update nowait" lock, would only not wait for other <span \
style="font-size: 10pt; ">"... for update nowait" locks. However, as it turned out it \
also immediately returns with the error code if there's a kind of transitive "normal" \
lock related to a plain insert or update elsewhere (plain = without a 'for update' \
clause).</span></div><div><span style="font-size: 10pt; "><br></span></div><div>As I \
was the only one on the Database, I was pretty sure there was no other <span \
style="font-size: 10pt; ">"... for update nowait"</span><span style="font-size: 10pt; \
"> query executing, but there *was* another parallel insert of a row that had a \
foreign key to the entry in the table I was trying to lock explicitly. That insert \
caused the lock in the other query to immediately fail. To me this was quite \
unexpected, but that's probably just me.</span></div><div><span style="font-size: \
10pt; "><br></span></div><div><span style="font-size: 10pt; ">What I thus actually \
need from PG is a "nowaitforupdate" or such thing; e.g. if there's a normal insert \
going on with a FK that happens to reference that row, it's okay to wait. The only \
thing I don't want to wait for is explicit locks that are hold by application code. \
I've worked around the issue by creating a separate table called "customer_lock" \
without any foreign keys from it or to it. It's used exclusively for obtaining those \
explicit locks. It violates the relational model a bit, but it does \
work.</span></div><div><br></div><div>Thanks for your help!</div> \
</div></body> </html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic