Skip site navigation (1) Skip section navigation (2)

Re: Hibernate / other ORM and pg_advisory_lock

From: Andrew <archa(at)pacific(dot)net(dot)au>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Hibernate / other ORM and pg_advisory_lock
Date: 2008-04-07 06:24:52
Message-ID: 47F9BE34.5080605@pacific.net.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

Responses

pgsql-jdbc by date

Next:From: Craig RingerDate: 2008-04-07 06:30:06
Subject: Re: Hibernate / other ORM and pg_advisory_lock
Previous:From: Craig RingerDate: 2008-04-07 04:36:56
Subject: Re: Character Encoding problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group