Re: Hibernate / other ORM and pg_advisory_lock

See responses below:
> - 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.
I've seen a number of blogs suggesting similar trigger based approaches
so it will possibly work, I've just never gone down that path. You may
want to also check Hibernates documentation, which discourages changes
to the version number outside of Hibernate:

http://www.hibernate.org/hib_docs/annotations/reference/en/html_single/#entity-mapping-entity-version

"The application must not alter the version number set up by Hibernate
in any way. To artificially increase the version number, check in
Hibernate Entity Manager's reference documentation LockMode.WRITE"

http://www.hibernate.org/hib_docs/entitymanager/reference/en/html_single/#d0e1162

"LockMode.WRITE prevents dirty-reads and non repeatable read on a given
entity and force an increase of the version number if any."

> 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.
So back to pessimistic locking which I believe we both had agreed was
not a desirable behaviour, particularly in a user interaction model? I
apologise but I feel that I am not following your logic, so you may have
a valid approach, but I'm missing something and so to me it does not
sound appropriate. I guess if you have multiple clients in multiple
technologies directly accessing the DB, then your situation is
immediately a lot more complex, and pessimistic locking may be your only
solution to ensure ACID transactions. The ideal situation would be if
the DB was only accessed through a single shared access layer by all
clients using a multi-tiered decoupled design, though I acknowledge that
is not always possible, particularly with many thick client
architectures that are tightly coupled two tiered designs at best.

I can understand how you would release the advisory lock by running
another SQL statement in another thread to do so, but I do not know if
that is of much use to you. As far as I know you can't tell a running
JDBC query to abort or cancel a running query. Such calls, either at
the JDBC level or at the JPA level are blocking calls for the client and
about the only influence you have on it is by setting the query time out
before executing (which has its own set of issues in the postgresql
driver
(http://www.nabble.com/Implementing-setQueryTimeout()-td15537669.html)).
Of course the UI client can always manage calls to its access layer via
another thread and kill that thread if the end user becomes impatient
and not want to wait. But from my understanding, that query with the
back end database process will still be running and the DB connection
will still be in use, including all of the associated locks and DB
threads. So a subsequent attempt on a long running query will also
block. However it the queries are all short running but are part of a
long running transaction, then you can rollback the transaction at any
point, but any other calls in a separate transaction dependent on those
locks held by the running transaction will result in the aforementioned
blocked call. You have to remember that at the ORM level (which is just
a wrapper to the JDBC), or at the JDBC level, you do not have fine grain
control of what is happening in the back end, and in my mind you should
be looking at what JDBC provides, not what the underlying DB may be able
to provide, and in using pg advisory locks, you are mixing the two.

And obviously I'm missing the main point of your argument, as I still do
not know how using pg advisory locks mixed with transaction locks will
improve your end users experience. Also I think I'm getting a little
confused, as previously you had stated:
> 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.
and then in the last mail the reason was:
> 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.
For me, your initial rational can only be addressed with some form of
polling on some lock or pseudo lock, whether automated or manual,
particularly if you give the user the option to wait. The second
rational is simply around ACID transactions which a well designed
architecture and optimistic locking with versioning can provide, or
worst case a pessimistic locking strategy at the cost of scalability,
but either way without a need for the pg advisory locks. I'm sure you
have valid scenarios in mind that justify your desired approach,
otherwise you would not feel so strongly, but I am having some trouble
working out such a scenario. So I think that may be my failing, and is
probably not important anyway from the perspective of responding to
specific technical aspects of your question.

Cheers,

Andy