Re: Spurious failure to obtain row lock possible in PG 9.1?

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!