NH SP, lock, composite Id

1. Set NoCount OFF

Updatable SP needs to turn Set NoCount OFF, NH is very peaky to the response from SP action. Here is an post in Chinese whose example has the line Set NoCount ON in SP commented out. One of the possible problem will be “NHibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect”

According to NH doc:

The stored procedures are by default required to affect the same number of rows as NHibernate-generated SQL would. NHibernate uses IDbCommand.ExecuteNonQuery to retrieve the number of rows affected. This check can be disabled by using check="none" attribute in sql-insert element.

2. Concurrency Check

Timestamp should be placed on top of the property list and right after id/composite id section. Repository/DAO class can then use “session.Lock(attribute, LockMode.Read)” to do concurrency check, throw staleObjectStateException when timestamp doesn’t match.

LockMode.Read just checks version column. It’s a very good solution when using DTO pattern to apply changs from DTO back to domain model. DTO should always carry a original timestamp field, we can do dirty check in service layer before apply changes, but it will be much easier to put the following code into baseRepository:



public override void SaveOrUpdate(T objToSave)
 // Do not check new objects.

 catch (Exception ex)


private void ConcurrencyCheck(T obj)
 if (IsNew(obj)) return;

 if (WasTimeStampCachedBefore())
 if (IsTimeStampChanged(obj))
 throw new ConcurrencyException();

 // Don't try to lock an object with Id. All kinds of exceptions:
 //  1. reassociated object has dirty collection

 // if we didn't cache timestamp
//                Session.Evict(obj); // Evict will disable update
//                Session.Lock(obj, LockMode.Read);
 using (var session = NHibernateFactory.OpenSession())
 session.Lock(obj, LockMode.Read);
 catch (StaleObjectStateException)
 throw new ConcurrencyException();
        public void should_throw_stale_exeption_for_dirty_data()
            var session1 = NHibernateFactory.OpenSession();
            var repository1 = new AttributeRepository(new InMemorySessionStorage(session1));

            var attribute = repository1.FetchByTypeAndCode("003", 8);

            // apply changes from DTO
            attribute.Description = "test data " + new Random().Next();

            // DTO might contains older timestamp
            attribute.LastModifiedDate = ((DateTime) attribute.LastModifiedDate).AddSeconds(-8);


Note:  TimeStamp in SQLServer == dotnet Byte[]
Version/TimeStamp in NHibernate == dotnet Datetime  (Datetime if set unsaved-value=nullable)

I added Flush and Evict before Lock to fix the errors of “collection binding to two open sessions” and “child association dirty” problem. Thanks to this post.

Somebody worked very hard to make NHibernate support db.TimeStamp by introducing UserType. Thanks God we don’t have any timestamp in our db.

Notice I could just do a version check out of repository, say service layer, check version number right after retrieve and before applying changes from DTO, as shown in this post 10.4.3 . The idea is similar, and more efficiency actually, only one drawback is that code can’t push down to a base class.

3. Composite Id

Composite Id can be done through a component serializable class overriding Equals and GetHashCode methods.if not using version/timestamp column, unsaved-value should set to any instead of none,  to check it’s a new object when doing SaveOrUpdate().

–update on Dec. 23, 2009.

I’m supprised that composite id actually is very simple, just declare the combination column in hbm file, but the version column still need to set to Generated Never if integrated id is used. I do have a workaround for this, will post later.
sql-insert, update, delete section can split to mulit-lines with “–” as the comment tag to filter out unused properties by SP.Or just use @p0, @p1, @p2 directly instead of using ?,?,?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s