Re: Hibernate / other ORM and pg_advisory_lock - Mailing list pgsql-jdbc

Hi Craig,

I have not used the pg_advisory_lock, so I can't comment on the use of
it with various tools.

With JPA you can add a version column to your table, either an integer
or timestamp datatype, with the preference being an integer field as the
timestamp column may not be granular enough to avoid data integrity
issues, and flag that column as a version field with the @Version
annotation in your corresponding entity, which will then tell JPA to use
this field for efficient optimistic locking.

I'm happy to stand corrected, but my understanding of JPA is that by
default it uses optimistic locking anyway.  But without a @Version
field, data integrity is not guaranteed as it uses the last commit wins
strategy, whereas if a @Version annotation exists, it compares the
version value, and if it does not exist on the tuple in the database for
the current object you hold in memory, JPA will throw an
OptimisticLockException.  Without the @Version annotation, you run the
risk of blowing away someone else's entries when you do your update with
older data.  If you are happy with just a dirty read, and do not need to
get an optimistic lock, then you can use the
@OptimisticLock(excluded=true) annotation, which is particularly useful
on relationships that you will not be updating at the same time as the
current entity, such as for reference values.

To set pessimistic locking, you need to go to the Entity Manager and
place the lock there, with the different JPA vendors all having
different ways of doing so, which doesn't make using pessimistic locking
in JPA very portable.  Using a pessimistic locking strategy such as the
use of pg_advisory_lock ties you to the database vendor, again not a
very portable approach, which may still be okay for you.  Additionally,
any pessimistic locking will limit the scalability of your solution.  It
may be the best solution for your particular domain model, but in most
cases, pessimistic locking is generally deemed an evil.  I would be very
hesitant in going down the path of using pg_advisory_locks as from what
I understand, they are tied to the session life, not to a transaction.
So the lock will persist even after a transaction is committed or rolled
back.  Which means that the onus is on you to call pg_advisory_unlock
successfully to remove the lock at the appropriate time and you need to
manage the uniqueness of the lock handle across multiple clients.  I can
see some really useful things to do with this function, but I wouldn't
be using it for plain CRUD work.  That is what the ORM layer is there for.

I'm not quite sure I follow what your concern is with the transaction.
If I understand correctly you are concerned that with a transaction
rolling back and then a subsequent update not updating correctly as it
is updating with a stale version that was cached?  If you use the
@Version annotation, this will resolve that issue, while still providing
you with full ACID behaviour, but in a highly concurrent environment,
you may need to handle a number of OptimisticLockException's in the
client, which is usually better than handling longer transaction times
per client.  Using either Optimistic or Pessimistic locking though will
not stop you from getting race conditions and deadlocks if you have not
designed the application well.  But the again, even the best developers
run into them from time to time.  Additionally, with an ORM such as JPA,
the entities you use to populate your client UI are created outside of a
transaction - at least created by default with a read lock which is
dropped once the entities are created.  It is only when you need to add
a new record, or update an existing one, that you enlist a transaction
for that particular event.  So transactions in an Optimistic locking
model should have very short life spans.  If you have a long running
transaction using pessimistic locking while waiting for user
interaction, then you probably need to reconsider your design.

With an ORM such as JPA, the EM should clean up any locks on completion
of the transaction, though you should ideally declare the completion of
the transaction either with a commit or a rollback.  The typical pattern
is to wrap the DB call in a try catch block, and commit at the end of
the try, rollback in the catch, and close your JPA/JDBC objects in the
finally block - which are themselves wrapped in a try block depending on
how defensive your coding style is.  However, some web frameworks such
as Seam take care of the whole transaction cycle by default without a
need to call any transaction demarcation points, and you need to
override the behaviour if you want more atomic control.

In Java, unlike the C destroy(), the finalize() method is
non-deterministic, in that it only gets run when the GC cleans up the
object, which may be never.  It is not a good idea to depend on it to
clean up your resources.  Additionally, the finalize method is only ever
called once on an object, so if the object is GC'd and the finalize
method is called and in the finalize method it resurrects itself,
perhaps by passing a reference of itself to another object, the finalize
method will not be called again for that particular object when it comes
time again to dispose of it.  Generally, you avoid the finalize method,
as there are not many examples that you would depend on it for, other
than for a last ditch best effort attempt to clean up any resources you
may not have otherwise been able to.  It is another coding style that is
generally deemed an evil.

Hopefully I have answered your question, and if not, at least pointed
you in the right direction.

Cheers,

Andy

Craig Ringer wrote:
> 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
>