Snapshot Isolation in SQL Server

And now to something completely different: a database topic. I avoid posts about this as there’s enough material about .NET, EF, and databases in general available. However after a professional discussion about transactions and snapshot isolation in SQL server, I feel this topic might be interesting for some .NET developers.

If you open a transaction and insert something in a table, or update this table, this table is locked. If you now select data from this table at the same time, there are several resolution possibilities, depending on your read strategy: classic approaches is either to SELECT data with a READ COMMITTED or READ UNCOMMITTED strategy leading either to blocking/delay of the application thread selecting the data (due to the waiting time required for the parallel transaction to complete) or to data inconsistencies due to a dirty read of uncommitted data.

If you have an open transaction on SELECT data and have SNAPSHOT ISOLATION enabled in your database, you will retrieve the last committed data set, as each transaction is stored with a temporary ID only, and a SELECT will return the last valid data (i. e. from the last committed transaction), or no data in the special case, that no transaction previously took place.

Therefore when you have a transaction-intensive application using an SQL server and you want to avoid manual locks/synchronisation operations in your code, I would always recommend you go for SNAPSHOT ISOLATION and leave the messy details about sorting out transactions and queries to the SQL server.

So lets have a look at a quick example utilizing NuGet System.Data.SqlClient.

You have to enable snapshot isolation for your database.

ALTER DATABASE tempdb SET ALLOW_SNAPSHOT_ISOLATION ON

Then this example would perform an INSERT (and leave the transaction open), while you use a query on the data with snapshot isolation enabled.

using var connection1 = new SqlConnection(connectionString);
connection1.Open();

using var command1 = connection1.CreateCommand();
command1.CommandText = "ALTER DATABASE demodb SET ALLOW_SNAPSHOT_ISOLATION ON";
command1.ExecuteNonQuery();

command1.CommandText = "CREATE TABLE demotable (ID int primary key, V int)";
command1.ExecuteNonQuery();

command1.CommandText = "INSERT INTO demotable (ID, V) VALUES (1,1)";
command1.ExecuteNonQuery();

// begin transaction 1 and block the table by using IsolationLevel.Serializable
var transaction1 = connection1.BeginTransaction(IsolationLevel.Serializable);
command1.Transaction = transaction1;
command1.CommandText = "UPDATE demotable SET V=2 WHERE ID=1";
command1.ExecuteNonQuery();

using var connection2 = new SqlConnection(connectionString);
connection2.Open();

// open the query while the update transaction is still open
using var command2 = connection2.CreateCommand();
var transaction2 = connection2.BeginTransaction(IsolationLevel.Snapshot);
command2.Transaction = transaction2;
command2.CommandText = "SELECT ID, V FROM demotable";
var reader2 = command2.ExecuteReader();

while (reader2.Read())
{
	// get value 1 instead of value 2, as the transaction for value 2 is still pending
	Console.WriteLine(reader2.GetValue(1).ToString());
}

transaction2.Commit();

// etc ...

Happy coding 🚀! If you like my post follow me on LinkedIn 🔔!