All entries for Tuesday 21 February 2006

February 21, 2006

hibernate executeUpdate + stale data


Whenever I develop applications I tend to do the simplest thing first and only "optimise" if needs be. So I had some logic which had to apply a Lock to a page and (optionally) all it's descendants.

So, the "lock application" logic should be seperated from the list of pages it is working on, so I introduced a LockAction which took a strategy for retrieving the pages that needed to be locked.

Fine, it all worked, but in terms of SQL it was extremely inefficient, executing one update for every page.


Given that all the different strategies for selecting the pages to be locked could be expressed in SQL, an obvious optimisation is to execute a single SQL statement to update a number of pages, and then another SQL select to retrieve all the pages I had locked.

Fair enough, easy to do:

  Date dateLockExpires = new Date(System.currentTimeMillis() – lockThresholdInMs);
  String hql = "update " + AbstractPage.class.getName() + " p " +
                        " set p.lock.lockedBy=:user, p.lock.lockedSince=:lockDate" +
                        " where (" +
                        " p.lock.lockedBy is null" +
                        " or p.lock.lockedSince is null" +
                        " or p.lock.lockedBy = :user" +
                        " or p.lock.lockedSince <= :dateLockExpires" +
                        ") and ";
  hql += "p.url = :url";   // this is the strategy to select the set of pages
  Query query = session.createQuery(hql);
  query.setString("user", user.getUserId());
  query.setString("url", page.getUrl());
  query.setDate("dateLockExpires", dateLockExpires);
  query.setDate("lockDate", new Date());
  int numberUpdated = query.executeUpdate();

  String hqlForAllPages = "from " + AbstractPage.class.getName() + " p";
  hqlForAllPages += " where ";
  hqlForAllPages += "p.url ='" + page.getUrl() + "'";  // strategy to select the pages

  List lockedPages = new ArrayList();
  List unlockedPages = new ArrayList();
  List allPages = session.createQuery(hqlForAllPages).list();
  for (Page p: allPages) {
    Lock lock = p.getLock();
    if (lock == null || lock.getLockedBy() != user.getUserId()) {
    } else {
  return new LockReportImpl(lockedPages, unlockedPages);

(ignore the horrible use of hql for the second select)
So basically we execute an update and then execute a select.

Simple? Yes. Works? No :( Unfortunately, even though the SQL statements are being sent to the database, and this all happens within the same transaction, the second select retrieves stale data, as if the previous executeUpdate() had never happened :(

One possible reason why is because we are using a version column, and the executeUpdate is not updating it. Modifying the update to increment the version column also does nothing :(

The only thing that works is to clear the entire session (session.clear()), but why should we have to do this? I am not asking Hibernate to execute raw SQL, it is executing hql, so why doesn't it do something sensible? I cannot expect it to know which rows have been updated, but I could (surely) expect it to invalidate all instances of that page from the first level cache?

Anyways, session.clear() works. Not sure what the impact will be on objects already loaded by that session…....

February 2006

Mo Tu We Th Fr Sa Su
Jan |  Today  | Mar
      1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28               

Search this blog



Most recent comments

  • Interesting… While I'm not completely convinced in such microbenchmarks, I'm pretty sure that 1ms … by Alexander Snaps on this entry
  • Hello. I bought the book yesterday. I was trying to find the source code for chapter 11 and chapter … by Suleman on this entry
  • by live mashup demo on this entry
  • Thanks mate ….. This blog was really helpful. by Maaz Hurzuk on this entry
  • Ty. Not directly helpful for my problem, but pointed me in the right direction. You will also get th… by Mike E. on this entry

Blog archive

Not signed in
Sign in

Powered by BlogBuilder