Using Snapshot Isolation with SQL Server and Hibernate

In the course of working on some deadlock issues, I found that MS SQL Server exhibits some unexpected (to me, at least) locking behaviors that can affect performance and cause deadlocks. In the end, I found that SQL Server provides an isolation level called Snapshot isolation which removes the need for locks on rows and removes lock contention.

To use this isolation level, you need to execute the following SQL on the database that you want to use the isolation level with (replacing MyDatabase with your database name, of course):

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Next, you need to modify your hibernate.cfg.xml file to add a property to tell Hibernate to use the Snapshot Isolation level:

<!-- The 4096 isolation level is the setting to use with the jTDS or Microsoft JDBC drivers -->
<property name=”hibernate.connection.isolation”>4096</property>

Snapshot isolation is not a panacea, and care must be taken in its use. It can be a useful option in your tool-belt, however. Before blindly applying this to your problem, make sure to test this out with some load in a test environment before and after the change. But first, what is Snapshot isolation doing, anyway?

Before talking about Snapshot isolation, it is probably useful to understand how SQL Server deals with locks in a transaction. When selecting rows in a transaction, SQL Server puts a SHARED lock on the rows you are selecting. If your query would return more than just a handful of rows, then often the query optimizer will decide to escalate those individual row level locks to page level locks. SQL Server manages data in 8KB pages that can span rows. So the premise behind lock escalation is that page level locks are less costly to maintain than individual row level locks. Normally this wouldn’t be a problem, but it starts to become a problem when selects are mixed in with inserts or deletes. Inserts or deletes need to have an EXCLUSIVE lock to be able to execute, and so they will wait on the SHARED locks to be removed before they execute. This waiting can manifest itself as a deadlock if you have multiple threads trying to select and insert rows on the same table.

There are many ways to correct this situation. The most correct way is to try to make sure that your code is written in such a way that data manipulation happens in a separate transaction from your selects. Since Hibernate generates most of the SQL for our operations, it can be non-trivial to figure out exactly where you need to break things up to prevent deadlocks and not cause your application to function differently.

Another popular option is to use (NOLOCK) with your selects, but that has a few problems. First, it is an SQL Server specific syntax and would require you to modify your queries. This would mean overriding Hibernate's syntax and losing a major benefit of using Hibernate in the first place. Second, (NOLOCK) acts a bit like using the READ UNCOMMITTED transaction level which means if you aren't careful with it, you can suffer from a variety of data read issues.

However, since SQL Server 2005, there is another option called Snapshot isolation. You can read a little bit about the Snapshot isolation level at http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx.

Simply put, Snapshot isolation works a little bit like version control system for row data. Different versions of rows are kept for each connection while transactions are executing so that locks don’t have to be used at all. This is a bit like the way Hibernate uses optimistic locking for versioned objects.

The benefit is that lock contention is totally removed because there aren’t any locks on rows! This means that concurrency can be much higher because you don’t have threads waiting on locks. The downside is that select operations can be a little bit slower because of the overhead of scanning for the proper versions of the rows.

I haven’t performed any scientific load tests with this setting, but basic tests seem to show only a couple percentage point slow down on selects. In an application with lots of users, this performance loss is often regained (and sometimes results in an overall performance gain!) due to threads no longer dealing with lock contention.