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:

update sales.CUstomer set modifieddate = getdate() where CustomerID = 9

This is the typical usage pattern I see when developers use TransactionScope:

using (TransactionScope scope = new TransactionScope())
{...}

By not specifying any TransactionOptions, .Net will default to setting the transaction Isolation Level to Serializable.

Instead, the following should be set in order to specify a less stringent transaction isolation level (I used ReadCommitted in this example):

TransactionOptions transactionOptions = new TransactionOptions();
transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{...}

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.

 

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store: