Performance tuning tips from the SQLGrease team

On more than a few occasions I’ve noticed that systems developed in .Net were running into lock waits due to serializable transactions. After further research I realized that developers were taking advantage of a feature for managing transactions within their .Net code. This feature is known as System.Transactions.TransactionScope. This feature is quite handy when it comes to writing clean transaction aware code. Unfortunately, the default usage pattern of it sets the transaction isolation level to Serializable. If your developers are using System.Transactions.TransactionScope, you should make sure they are aware that the default behavior uses Serializable transactions, and make sure they understand the implications of this.

So what’s the implication of using Serializable Transactions?

A select will leave a lock on a row until the transaction rolls back or commits. Take the following example (example is AdventureWorks2012 database):

SELECT * FROM Sales.Customer WHERE CustomerID = 9

If the above statement was issued from within a Serializable transaction, it will leave a lock on the record in the Sales.Customer table. As a result, the following statement will block until the transaction that the above statement was involved in commits:

So what’s the potential impact to my system?

I’ve seen this resolve intermittent “hangs” in systems. I’ve also seen this resolve deadlocks in systems that encountered frequent deadlocks. Of course the results will always vary based on the nature of the system.