Thread: Spurious failure to obtain row lock possible in PG 9.1?
I'm using Postgres 9.1 on Debian Lenny and via a Java server (JBoss AS 6.1) I'm executing a simple "select ... for update" query:
SELECT
importing
FROM
customer
WHERE
id = :customer_id
FOR UPDATE NOWAIT
Once every 10 to 20 times Postgres fails to obtain the lock for no apparent reason:
18:22:18,285 WARN [org.hibernate.util. JDBCExceptionReporter] SQL Error: 0, SQLState: 55P03
18:22:18,285 ERROR [org.hibernate.util. JDBCExceptionReporter] ERROR: could not obtain lock on row in relation "customer"
18:22:18,285 ERROR [org.hibernate.util.
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.
Is it possible or perhaps even known that PG has this behavior, or should I look for the cause in the Java code? (I'm using Java EE"s entity manager to execute a native query inside an EJB bean that lets a JDBC connection from a pool join a JTA transaction.)
Thanks!
henk de wit wrote: > I'm using Postgres 9.1 on Debian Lenny and via a Java server (JBoss AS 6.1) I'm executing a simple > "select ... for update" query: > > > SELECT > > importing > > FROM > > customer > > WHERE > > id = :customer_id > > FOR UPDATE NOWAIT > > > Once every 10 to 20 times Postgres fails to obtain the lock for no apparent reason: > > 18:22:18,285 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: 55P03 > 18:22:18,285 ERROR [org.hibernate.util.JDBCExceptionReporter] ERROR: could not obtain lock on row in > relation "customer" > > > 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. > > > Is it possible or perhaps even known that PG has this behavior, or should I look for the cause in the > Java code? (I'm using Java EE"s entity manager to execute a native query inside an EJB bean that lets > a JDBC connection from a pool join a JTA transaction.) 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. Turn on logging og connections and disconnections. Set log_statement='all' That way you should be able to see from the log entries who issues what queries concurrently with you. Yours, Laurenz Albe
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.
> 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!