Unexpected query/flush behaviour

Andy · **Posted:** Fri Apr 11, 2008 7:01 am

We have a system where we want to update a row in the database based on a message we receive. The series of events is:

1. We set the flush mode to COMMIT.
2. A message arrives in the system
3. We take the ID of the message and load its corresponding object from the database.
4. We update the object based on the data in the message we received.

So at this stage, we have a database row with a value of, say, 15, and we received a message saying it should be a value of 14. We can do a manual dirty read from the database (SQL Server, using a nolock hint) and we can see that the value is still at 15.

5. Next a query is run by a separate part of the application, triggered via an event listener, that happens to be on the same row/object. The query returns a value of 14.
6. Another dirty check of the database shows the value is still 15.

So the question is, if I'm running with a flush mode of commit, why does the query at step 5 return uncommited data? The change of the value hasn't been flushed to the DB, and the query is on multiple columns that aren't primary keys, so the first level cache doesn't come into play.

I'd expect this to hit the database and reload the original row.

Is Hibernate clever enough to realise that it already has a copy of that object in the first level cache and return that version? If the query returns a list will it replace any rows/objects loaded from the database with objects in the first level cache?

Thanks,

Andy.

sanne.grinovero · **Posted:** Fri Apr 11, 2008 7:59 am

Which transaction-isolation did you define in the datasource configuration?

Andy · **Posted:** Fri Apr 11, 2008 9:09 am

Read committed

sanne.grinovero · **Posted:** Fri Apr 11, 2008 9:18 am

Quote:

a query is run by a separate part of the application, triggered via an event listener

Is it possible this "separate part" is actually run by the same thread, and reusing the same open session?

Andy · **Posted:** Fri Apr 11, 2008 9:26 am

I assume that is indeed the case: all of the above takes place in one thread, one session and one transaction.

But the question is: does Hibernate run an EJB-QL (or native) query and then check the first-level cache for objects with matching identifiers?

I though it only used the cache for lookups by primary key (i.e. with EntityManager.find(Class<T> entityClass, Object primaryKey)).

Andy · **Posted:** Tue Apr 15, 2008 10:30 am

So it looks like this is indeed the case. I created a simple test case:

Code:

   EntityManager em = ...;
   em.setFlushMode(FlushModeType.COMMIT);

   Principal p = new Principal("123", "andy");  // Fields are id and name
      
   EntityTransaction transaction = cpdsEntityManager.getTransaction();
   transaction.begin();
   myEntityManager.persist(p);
   transaction.commit();
      
   p.setName("bob");
      
   Query query = cpdsEntityManager.createQuery("FROM Principal WHERE name = 'andy'");
   System.out.println(query);

The query brings back the principal with name of 'andy' in the database, but it displays the name as 'bob' even though that change hasn't been committed. Running a native query with a mapped class has the same effect.

So it looks like Hibernate swaps the copy found in the database with the copy in the first-level cache. Don't know how I'd never observed this behaviour before!

sanne.grinovero · **Posted:** Tue Apr 15, 2008 12:23 pm

I don't know if it helps, maybe you should start a new transaction before executing the second query.

Quote:

Running a native query with a mapped class has the same effect

This is strange but doesn't let me think to hibernate's first level cache, it could also be affected by JDBC bug or setting, or DB setting overriding hibernate's instructions.
If the transaction doesn't help could you use some db-log or read the bulk data (scrollable?) with a native query to verify you are not getting the wrong data back from DB?
I know you tryed from another connection, but you should try just below this code.

Andy · **Posted:** Tue Apr 15, 2008 12:54 pm

We changed the query to be a native query that read the name column, and it brought back the value from the initial insert, as found in the DB.

And then when we use a native query to select Principal.* and pass Principal.class as the class to map to, we see Hibernate swap the loaded row with the one from the session cache.