Re: Hibernate / other ORM and pg_advisory_lock

Thanks for your interesting and clear response. My comments are
interleaved below.

Andrew wrote:
> 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.
At least in a desktop app it's sometimes desirable to inform a user that
(for example) somebody else is currently modifying a customer record, so
they can't edit it right now, then possibly offer to wait. In this
environment that's significantly preferable in user interface terms to
telling the user "Whoops, can't save your changes, somebody else beat
you to it", reloading the data in the UI and getting them to make their
changes again. It's not something I can do with optimistic locking, and
doing it with JPA pessimistic locking a transaction must be held open
during user think-time. Or lunch-time, for that matter, though I'd time
out any exclusive access after a while. Such long running transactions
are not an attractive option.

The locking to protect against conflicting concurrent updates in a
transaction context is required even if advisory locking is used; the
advisory locking is just useful to minimise the risk of conflicts in
cases where the conflict can't be resolved without bugging the user. The
app should still work and shouldn't mangle data even if advisory locking
attempts were replaced with a call that always succeeded immediately,
though it'd have to force users to retry/reenter some things.
>
> 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 [..] 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.
Yep, that was my understanding. I was really hoping there'd also be the
option to use traditional SELECT ... FOR UPDATE locking because I have
existing apps using the database that handle trickier updates that way,
but it doesn't look like that's covered within the JPA spec.

The spec defines a lock(....) method on the EntityManager:

http://java.sun.com/javaee/5/docs/api/javax/persistence/EntityManager.html#lock(java.lang.Object,%20javax.persistence.LockModeType)

and defines the LockModeType enumeration, though it assumes that locking
will be done using a version column rather than using SELECT ... FOR UPDATE.

Other apps use this database, and it's not at all attractive to be
forced to use the (to my mind clunky, though efficient under high loads)
JPA version column approach for update collision detection. All the
other DB-using code would also have to be altered to increment the
version column, and all the rest already uses normal database
concurrency control like using safe updates (UPDATE blah SET thing =
thing + 1) or SELECT ... FOR UPDATE locking to make sure everything goes
smoothly.

I might be able to do something hairy like a trigger that increments the
version column on UPDATE only if it's not explicitly updated by the app,
but I'd prefer to get the Java app to fit in with the usual locking and
concurrency control scheme. This is the sort of thing I was worried
about with the use of ORM tools.
> 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.
It is OK for the workload of this app, but certainly an issue for some
apps. This app is already highly tied to Pg, and to a fair extent is
just a user interface for the brains of the system that live in Pg.
> Additionally, any pessimistic locking will limit the scalability of
> your solution.
Again, no big deal in the cases its intended for. Areas where I'd be
using advisory locking are generally low concurrency, and the advisory
locking is intended to improve "user experience" rather than as a data
protection strategy. The app will still use appropriate locking during
the transaction used to actually commit the changes; the advisory lock
is to ensure there's no chance of another user trying to then modify the
same data and having the change rejected.
> 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.
Yep, those are exactly the characteristics that make advisory locking
attractive. A lock can be taken to inform other apps that "the user
intends to modify this <thing>" where <thing> might be a particular
record, collection of records with a certain common characteristic, etc.
Other apps can test the lock and use that information for things like
warning users "this <thing> is being modified by someone else", and can
also where it's useful wait on the lock. No long-running transactions
with locks need to be held open. They're also neatly cleaned up on
disconnect so there are fewer issues of dangling locks of an app
crashes, loses its network connection, etc.

To my mind advisory locking is entirely separate to, and does not
replace, proper locking or versioning during transactions, and the goal
is to minimize conflicts where those conflicts would result in user
annoyance or confusion.

The alternative is to use an appserver for inter-client messaging to
essentially the same effect, or to use a database table for non-waitable
advisory locking (and lots of MVCC bloat).
>
> 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.
The above matches my understanding of the situation. I also agree with
regards to avoiding long running transactions, and I'm seeking to make
sure that I can safely use Pg's advisory locking specifically to avoid
having to either use a long running transaction or tell a user something
like "Your update could not be completed because somebody else has
changed this record. The copy of the record on screen has been updated.
Please check it, and if necessary re-apply any changes" because of a
conflicting update detected by @Version style optimistic locking.

>
> 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.
That much I figured out. [Insert pathetic wailing about about lack of
RAII and lexically scoped object lifetimes here]. It pretty much
confirms what I was thinking - use an explicit close/cleanup that
releases any external resources (like pg advisory locks). Then, in
finalize(), check to make sure the object has been closed and yell
loudly in the log if not before doing the cleanup, so that any missed
closes can be found and fixed.

--
Craig Ringer