Re: Hibernate / other ORM and pg_advisory_lock

> -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.

Is this mechanism supported by the PostgreSQL JDBC driver?

Thanks
Peter

On Sun, Apr 6, 2008 at 10:10 PM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> 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
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>