Skip site navigation (1) Skip section navigation (2)

Hibernate / other ORM and pg_advisory_lock

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Hibernate / other ORM and pg_advisory_lock
Date: 2008-04-02 17:08:51
Message-ID: 47F3BDA3.1030208@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi all

I'm about to settle on a database implementation for the Java part of 
the development project I'm working on, and I'm wondering if anybody 
here has any experience using PostgreSQL's advisory locking features 
with ORM database layers like Hibernate, TopLink, OpenJPA, Cayenne, 
iBatis, etc. I'm hoping to avoid relying on locking using long running 
transactions that're open across user interaction (ugh) and 
pg_advisory_lock etc seem well suited to the task.

At this point it's looking like I'll be using Hibernate (via Hibernate 
EntityManager / JPA), so I'm especially interested in comments related 
to it. In particular, in a J2SE desktop environment, has anybody here 
made use (successful or otherwise) of Pg's advisory locking features? 
They work fine in my test cases with Hibernate EntityManager and with 
OpenJPA (not with TopLink Essentials due to connection management 
issues) but working in a few reasonably simple tests is very different 
from working reliably in production.

I'm particularly worried about ensuring that connections aren't cached, 
pooled, or terminated & reestablished behind my back - say after an 
error that results in a transaction rolling back. The connection 
drop/reestablish would release any advisory locks being held, and if 
that's a risk then data must be selected FOR UPDATE and compared to the 
old copies before making any changes. Which also involves persuading the 
ORM layer to do that, and do it the right way.

[Java newbie warning] I'm also curious about strategies people have used 
to ensure reliable advisory lock release when the persistence layer 
finishes with the object. I'd prefer to avoid the need to explicitly 
release the locks, instead  having them released when the persistence 
layer "forgets" about the object. In C++ (w/o a gc at least) I might 
just use a dtor, but with the unpredictability of destruction timing in 
a gc'd environment it's not so attractive in Java. Right now I'm 
inclined to define a finish() method that causes all subsequent calls to 
accessors to throw, and call it from finalize in case some code path 
fails to call it manually when done with the object. Totally stupid, or 
workable sounding? [/Java newbie warning]

So ... any thoughts/experiences using Hibernate or other ORM  tools with 
Pg's advisory locking?

--
Craig Ringer

Responses

pgsql-jdbc by date

Next:From: Sam LawrenceDate: 2008-04-02 17:52:16
Subject: Re: CallableStatement and getUpdateCount
Previous:From: Kris JurkaDate: 2008-04-02 17:07:23
Subject: Re: CallableStatement and getUpdateCount

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group