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

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

 



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!

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux