All entries for Tuesday 21 February 2006

hibernate executeUpdate + stale data

background

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.

optimisation

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()) {
      unlockedPages.add(p);
    } else {
      lockedPages.add(p);
    }
  }
  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…....

: 21 Feb 2006 13:52 |  Tags: Data Executeupdate Hibernate Stale |  Comments (2) |  Close comments |  Report a problem