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

OK, I think I now see how to get the ORM layer's version-based
optimistic locking to play well with existing apps using the DB, and how
to effectively use Pg's advisory locking. Here's what I'm thinking in
case it's ever of use to anybody:

- Add version columns for ORM tools to use, setting defaults for apps
unaware of the versioning so version columns get sensible initial values.

- Add a trigger that, on updates to tables with version columns, does:

   IF new.version = old.version AND new <> old THEN
      new.version = new.version + 1;
   END IF;

   ... to support other apps that don't know about the versioning and
rely on transaction-level locking. The new <> old check is added in case
an ORM tool assumes that if it UPDATEs a row without incrementing the
version or changing anything (say, to use the side effect that the
UPDATE obtains a lock on the row) then the row version won't change.
Without that an ORM tool might try to update the row later and think
it's conflicting with another update, even though the conflicting update
was its own.

- Where the UI can be significantly improved by doing so use advisory
locking to suppliment (not replace) the existing handling of conflicting
updates, minimising the need for users to re-enter changes. Assume
advisory locking is a best effort service that may fail (either due to
my bad coding resulting in a lock not being taken, another app not
knowing about the lock, the ORM layer switching connections behind my
back, etc) and always be prepared to handle update conflicts.

Does that sound sane to folks here? In particular, does the behaviour of
the version column trigger sound sensible?

More comments interleaved below.

Andrew wrote:

>  Using an ORM comes at a cost, and
> I'm not just talking about performance overheads, as it attempts to map
> object trees to relational data constructs and there is a pronounced
> impedance mismatch between the two.  If you start out with a fresh DB
> and can design it with an ORM client in mind, single field surrogate
> keys on all tables, version fields on tables likely to have multiple
> users, choose data types that are only supported by the ORM and client
> language, don't mind feeling compromised when doing the DB design, then
> it fits beautifully.

I'm lucky enough that the DB design is already a reasonable fit for ORM
use. The only real sticking point is the need for version columns
because ORM systems want to do cross-transaction locking/versioning .
I'd probably land up with them eventually anyway.

Everything has a surrogate key, because almost all natural keys have
issues in this DB. Everything's highly normalised. Most types are mapped
to native Java types automatically by the PostgreSQL JDBC, and the few
that aren't are just domains that're easily handled by the ORM once it's
told what the base type is.

> However, and this is where I begin to speak some
> heresy...   If you access the DB through SP's or functions, don't even
> bother considering an ORM.  Treat the DB as a service API.

Sounds sensible to me. The ORM layers seem to have a limited facility
for SPs that's good enough for in-DB queries that return
normaltable%rowtype, but beyond that it doesn't look like much fun. I'd
  want to use something like Spring-JDBC instead.

> So back to your problem and off my soap box, perhaps an ORM is not
> necessarily the best fit for your solution?

For most of the job an ORM is actually a very good fit, at least
conceptually. The amount of repetitive code required will be
significantly reduced by the ability to  map a DB record to a Java
object, then bind members/properties of the object to GUI elements in a
form. After all, significant parts of this app aren't much more
sophisticated than what you can do with MS access (if you don't mind the
licensing fees, weird quirks, and total lack of portability).

The tricky bits will probably bypass the ORM and do direct SQL, but
that'll be touching largely separate parts of the DB so there won't be
too many ORM cache issues.

> Regardless of what locking mechanism you choose, optimistic locking or
> pseudo-locking, at some point one user will be faced with stale data and
> you still have to reconcile that data for the user.  Any attempt to
> check if the data is changed by someone else before any commit or to
> check if another user also has the same set of data with the potential
> to modify without a transactional lock would require some form of
> sentinel pattern and polling of that sentinel, whether that sentinel is
> the pg_advisory_lock, a flag in a dedicated table, or whatever else you
> choose.  And you are still faced it how to handle data that has changed
> for the current user.  Is there really a business case to set up a
> polling of your DB by all of your DB clients, just for some slight
> usability gains?

First: thankfully polling is not required. One of the nice things about
pg's advisory locks is the ability for the client to block on a lock. If
the app wants to wait on an advisory lock all it has to do is attempt to
acquire the lock; the thread will block until a result is returned (ie
the lock is obtained).

If the user gets bored of waiting the UI thread can just signal the
blocked DB thread to abort the query and get on with something else.

Of course, even with advisory locking it's always *possible* that
someone else might sneak in an update. They might not check the lock. It
might be another app that doesn't know about the advisory locks. Etc. So
it's always necessary to use opportunistic or pessimistic transaction
level locking/versioning as well, to ensure there are no update
collisions, and it's necessary to be able to handle failed updates.

However, I DO consider it *well* worth the small cost to improve the
user "experience" by minimising the risk of failed updates in areas
where it must directly affect the user.

> Considering your enthusiasm of RAII, I bet you will be pleased with the
> planned release of C++0x with the inclusion of smart pointers.

For a moment I thought you were going to say "In the next version of
Java, there will be ..."

*sob*

I'm already using standard c++ shared_ptr through std::tr1 . It's
trivial to support std::tr1::shared_ptr for modern compilers and fall
back to boost::shared_ptr for older compilers. However I often prefer to
just follow strict ownership rules, use std::auto_ptr, etc.

--
Craig Ringer