NHibernate and Snapshot Isolation

Isolation levels make a large difference in how scalable your application is. Pick the wrong one, and your users will experience the kind of pain you see on The Daily WTF. But, if you're running into concurrency issues (row locks), or other scalability problems, it may be worth your time to take a look at the Snapshot isolation.

Typically, with NHibernate, you have four isolation strategies to choose from:

• ReadUncommitted - The one you really want to stay away from unless you're 100% you know what you're doing. You can end up with cascaded failures on transactions. That in and of itself should steer you away.


• ReadCommitted - The default isolation level used by NHibernate, and recommended for use in NHibernate In Action, and other such qualified sources


• RepeatableRead - Really, you should get the majority of the benefits of this from using caching and versioning


• Serializable - The flip side to ReadUncommitted, except this encounters scalability issues

However, NHibernate will use any isolation level supported by the database (and to that extent, ADO.NET). Which gives us "Snapshot" (recently introduced to MsSql2005+)

Lets talk briefly about the Snapshot isolation model (ref: MSDN Article):

When writing to rows, snapshot isolation actually copies the rows from the table, into the TempDB (or another comparable system if not using MsSql), performs the necessary modifications, assigning those rows to transaction ID's, then commits the snapshot rows back when it's done. While the first transaction is performing operations on the rows, it will not cause a read lock. So a ReadCommitted isolation level can actually read the rows the way they were before the snapshot isolation transaction began.

If you attempt to write to rows as a second transaction that are in a first transaction who's isolation level is set to snapshot, then the second transaction will attempt to write to the same row our isolation transaction is attempting to write to, it will raise an error and roll back the snapshot transaction.

This mode can be a major benefit, giving you some of the concurrency benefits of ReadUncommitted, while still maintaining the data integrity of a more Serializable approach.

First, you must enable snapshots on your database. For MsSql:

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

Then you set the default isolation level in the NHibernate Configuration section. Add the following config element:

<property name="connection.isolation">Snapshot</property>

You can alternatively change the isolation level inline when a transaction first begins:

   1:  ISession session = NewSession();

   2:  session.BeginTransaction(IsolationLevel.Snapshot);

This recently (along with other tweaks) helped us solve a concurrency issue in a large application. Is it a fixall? No, absolutely not. As always, you should be pragmatic in your approach to improving performance.