I have been trying to write rather complex query using Linq To NHibernate, I gave up and wrote it in the SQL instead. While writing the query in <sql-query> tag, the Visual Studio had offered me a <synchronize> tag. I had no idea what the tag was for.

The tag itself has one required attribute table, so it is used like <synchronize table="SOME_TABLE" />.

The NHibernate is infamous for its quality of official documentation, so I obviously had no luck there. Not even Hibernate was much more forthcoming. Open source to the rescue: In the Beings.hbm.xml of one NH test was following comment:

(2) use of to ensure that auto-flush happens
correctly, and that queries against the derived entity
do not return stale data

The test itself was for a read-only entity created from select (interesting feature I didn’t know about), not a SQL query, but I hoped it would be similar.

I have opened my project for testing the NHibernate (Nerula) and started writing code and observing how does the tag behave.

Observations:

The purpose of the <synchronize> tag is to flush entities that use the specified table before running the query, but it doesn’t flush entities unrelated to the table. The secondary objective is for notifying the second level cache about update/delete sql queries, but that is for another post.

To demonstrate the concept, I have created three identical sql-queries, each with different synchronize tags:

 <sql-query name="DeleteProjectSync">
  <![CDATA[ delete from Project where Code = :Code ]]>
  <query-param name="Code" type="String" />
  <synchronize table="Project"/>
</sql-query>

<sql-query name="DeleteProject">
  <![CDATA[ delete from Project where Code = :Code ]]>
  <query-param name="Code" type="String" />
</sql-query>

<sql-query name="DeleteProjectSyncWrongTable">
  <![CDATA[ delete from Project where Code = :Code ]]>
  <query-param name="Code" type="String" />
  <!-- We are flushing the wrong table -->
  <synchronize table="Blog"/>
</sql-query>

I have loaded and modified a Code property of a Project entity and my queries will try to delete the modified project row from the database.

var project = session.Query().First();
var originalCode = project.Code;
project.Code = "Test";

So what happend when I tried to delete a Project with Code “Test”? Let’s go over each query.

var deletedCount = session.GetNamedQuery("DeleteProject").SetString("Code", "Test").ExecuteUpdate();
Assert.AreEqual(0, deletedCount);

In the query without any synchronization, the project is not deleted (ExecuteUpdate returns number of modified/deleted rows), because the project is modified only in the session, it is not synced with the database and thus the SQL query can’t find a project with specified code. If I manually flush the session (session.Flush()) before running the query, the record is deleted.

var deletedCount = session.GetNamedQuery("DeleteProjectSync").SetString("Code", "Test").ExecuteUpdate();
Assert.AreEqual(1, deletedCount);

In second query, I use tag for the table from which we are deleting. All entities using the table Project are flushed to the database and we actually delete one record with our specified Code.

var deletedCount = session.GetNamedQuery("DeleteProjectSyncWrongTable").SetString("Code", "Test").ExecuteUpdate();
Assert.AreEqual(0, deletedCount);

Just to make sure, we will try with a third query that has a synchronize tag, but for wrong table. This is to demonstrate that we don’t flush all entities in session, in this case we also delete zero rows, because the updated code is still not in db. If I use session.Flush(), the row is deleted.

I hope this sufficiently demonstrates the concept.

This is all nice and well, but what about 2nd level cache? When and how is it updated? I am quite sure (courtesy of BulkOperationCleanupAction) that entities affected by tables in sycnhronize tag are also evicted from 2nd level cache, but I haven’t tested it yet.

Update:

The <synchronize> tag respects the FlushMode of the session. If you set FlushMode to Always, all queries will perform delete, if you set FlushMode to Commit, all will fail, including the DeleteProjectSync.