Turn on Snapshot Isolation from the Beginning

My team released a major new version of a system to production. Significant changes were made on all levels. Despite extensive tests and emulation of realistic load scenarios we started encountering deadlocks on our SQL Server DB after go-life.
After a short investigation I realized that enabling Snapshot Isolation on the DB should significantly reduce the likelihood of deadlocks. This article explains it quite well: http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

“The term “snapshot” reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.”

Enabling this feature does carry risks. Concurrency control implementation and temp table size have to be considered. Given we had years of development spent on the system project management explored other options. Considerable time was invested in alternative solutions. To make things more difficult we were not able to reproduce the scenarios causing deadlocks in a test or development environment. Multiple releases of new versions failed to reduce the errors in production.

Finally we convinced management that the risk was manageable and turned on Snapshot Isolation. It solved the issue immediately and no side effects ever arose.

After this experience I made it a rule to enable Snapshot Isolation on SQL Server from the beginning on any new project. Why this isn’t turned on by default is a mystery… If you develop on a different RDBMS check your option (most systems have a similar setting). While you develop you will continuously test against Snapshot Isolation level. If you have to enable it late in a project make sure you run regression tests.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>