Introduction

For a while now, the production database that is part of my current project, has been throwing SQL Server deadlock exceptions. Deadlocks are usually caused by resource contention – in particular two processes wanting to ‘write’ to the same resource at the same time. They do not (should not) arise when one process is simply reading and another trying to write – unless you have the most pessimistic form of isolation level set on your database.  Needless to say, deadlocks are more than a nuisance – at worst, they lead to loss of data (if the process that sql server terminates is one ‘writing’ to the database).

In detecting the source of our SQL Server deadlocks, I discovered some interesting experiences from the developer of stackoverflow.com. His thoughts on sql server’s deadlocks are summarized in this (humorous) post. More importantly, he points a way out – something that the DBA purists may not recommend, but something that may fit your database perfectly.

Also, applications using Entity Framework or nHibernate or other persistence layers, seem to run into this more often (we were encountering this from within nHibernate’s transaction management).

The read committed snapshot mode

ReadCommitted is the default isolation level in SQLServer. Data that has been modified (but not yet committed) cannot be read. That is fine – but should SQL Server throw a deadlock exception if it encounters this ‘dirty read’ taking place? It seems that it does throw these exceptions! Simply trying to read uncommitted data is enough to throw this exception. Overly pessimistic – after all, the worst thing that can happen is that your fetched data is going to be ‘a little late’ (having to wait on the ‘write’ to finish).

In any case, the SQL Server team developed a completely new ‘locking mode’ to deal with the ‘performance impact’ of their locking mechanism. This new mode is called readcommitted snapshot. From their documentation:

Snapshots rely on an entirely new data change tracking method … more than just a slight logical change, it requires the server to handle the data physically differently. Once this new data change tracking method is enabled, it creates a copy, or snapshot of every data change. By reading these snapshots rather than live data at times of contention, Shared Locks are no longer needed on reads, and overall database performance may increase.

If you have a database that has more frequent reads and far fewer writes, this may be the isolation level your database needs. All you need to do to set it is :

ALTER DATABASE [MySite] SET READ_COMMITTED_SNAPSHOT ON

Summary

If you have a database that has more frequent reads and far fewer writes, SQL Server’s overly pessimistic locking may not be buying you anything (and hurting your performance at the same time). Try setting  your database’s isolation level to read committed snapshot as shown in the snippet above.  If you are encountering these deadlock exceptions from within your persistence layer, setting this snapshot isolation level may be all you need.

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin