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!
