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-03 07:06:40
Message-ID: 47F48200.1010700@pacific.net.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
I must admit that I wasn't even aware of the JPA lock, and it does 
appears to be very restricted.  Shows how much attention I've paid to 
the API.  When I have had to use locking through Hibernate, I have 
usually even bypassed Hibernate and passed the isolation level lock hint 
directly to the DB within the query as then I have finer control, but I 
have so far usually had the "luxury" of working on applications that are 
and forever will be tied to a particular DB, probably even fossilised to 
a particular version.  But thanks for the link.

You have to consider when using an ORM, why you would choose to do so, 
which is mainly to do much of the boilerplate coding for you.  I don't 
buy into the argument that it gives you DB portability, as so far, when 
using an ORM I have found myself more tightly bound to a DB dialect than 
if I'd gone through JDBC directly.  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.  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.  If you have 
an existing DB that is not conducive to modifying, that contains mostly 
natural keys, many of which are composite, then you can still use an 
ORM, but you will be wrestling with it all the way.  If the DB tables 
have datatypes that are not standard in the JDBC or ORM, then you can 
make it work, but again you are potentially faced with additional effort 
above and beyond. Mileage will vary from project to project, but ORM's 
are no panacea, they simply serve a purpose for some situations, and for 
others you need to weigh up whether the cost is worth the returns.

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

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?  Rhetorical question.  For me to entertain such an 
approach, I would personally want a pretty strong case for it.

Considering your enthusiasm of RAII, I bet you will be pleased with the 
planned release of C++0x with the inclusion of smart pointers. 
Completely off topic and at the risk of being chastised in this forum, 
you may be interested in this interview with Bjarne Stroustoup: 
http://www.ddj.com/cpp/207000124;jsessionid=4D4OJVJ3EPMOCQSNDLPSKH0CJUNN2JVN?_requestid=437406
Sadly, according to Stroustoup, C++0x will most likely be C++10, not 
that I have used a ~ for many years.

Cheers,

Andy



In response to

Responses

pgsql-jdbc by date

Next:From: Albe LaurenzDate: 2008-04-03 08:25:28
Subject: Re: CallableStatement and getUpdateCount
Previous:From: Craig RingerDate: 2008-04-03 03:16:30
Subject: Re: Hibernate / other ORM and pg_advisory_lock

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