Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TGG5Z-00005c-Qs for pgsql-admin@postgresql.org; Mon, 24 Sep 2012 21:21:14 +0000 Received: from col0-omc1-s1.col0.hotmail.com ([65.55.34.11]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TGG5Y-0001mi-Bt for pgsql-admin@postgresql.org; Mon, 24 Sep 2012 21:21:13 +0000 Received: from COL104-W25 ([65.55.34.7]) by col0-omc1-s1.col0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Mon, 24 Sep 2012 14:21:11 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_1b551f76-8a57-4e83-8ef1-94f8bc7db2ac_" X-Originating-IP: [80.61.220.165] From: henk de wit To: Subject: Spurious failure to obtain row lock possible in PG 9.1? Date: Mon, 24 Sep 2012 23:21:10 +0200 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 24 Sep 2012 21:21:11.0267 (UTC) FILETIME=[851F3B30:01CD9A9A] X-Pg-Spam-Score: -2.5 (--) X-Archive-Number: 201209/141 X-Sequence-Number: 40082 --_1b551f76-8a57-4e83-8ef1-94f8bc7db2ac_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 =3D :customer_id FOR UPDATE NOWAIT Once every 10 to 20 times Postgres fails to obtain the lock for no apparent= reason: 18:22:18=2C285 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: = 0=2C SQLState: 55P0318:22:18=2C285 ERROR [org.hibernate.util.JDBCExceptionR= eporter] ERROR: could not obtain lock on row in relation "customer" I'm "pretty" sure there's really no other process that has the lock=2C as I= 'm the only one on a test DB. If I execute the query immediately again=2C i= t 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=2C 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 f= rom a pool join a JTA transaction.) Thanks! = --_1b551f76-8a57-4e83-8ef1-94f8bc7db2ac_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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 =3D :customer_= id

FOR UPDATE NOWAIT

Once every 10 to 20 times Postgres fails to obtain the lock for no appar= ent reason:

18:22:18=2C285 WARN =3B [org.hib= ernate.util.JDBCExceptionReporter] SQL Error: 0=2C SQ= LState: 55P03
18:22:18=2C285 ERROR [org.hibernate.util.= JDBCExceptionReporter] ERROR: could not obtain lock o= n row in relation "customer"

I'm "pretty" sure there's really n= o other process that has the lock=2C as I'm the only one on a test DB. If I= execute the query immediately again=2C it does succeed in obtaining the lo= ck. I can however not reproduce this via e.g. PGAdmin.

Is it possible or perhaps even known= that PG has this behavior=2C or should I look for the cause in the Java co= de? (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.)<= /div>

Thanks!

= --_1b551f76-8a57-4e83-8ef1-94f8bc7db2ac_--