All 1 entries tagged Stale

No other Warwick Blogs use the tag Stale on entries | View entries tagged Stale at Technorati | There are no images tagged Stale on this blog

February 21, 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…....


December 2020

Mo Tu We Th Fr Sa Su
Nov |  Today  |
   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 29 30 31         

Search this blog

Tags

Galleries

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
  • http://woosight.net/account/login?username=demo 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

Loading…
RSS2.0 Atom
Not signed in
Sign in

Powered by BlogBuilder
© MMXX