Improving performance by using Snapshot isolation level

While working on the performance tuning on the my website, I was getting the following error:

Transaction (Process ID XXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

To reduce this deadlock, I changed the default isolation level of the database to Snapshot.

ALTER DATABASE dBname

SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE dBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE dBname SET READ_COMMITTED_SNAPSHOT ON;

ALTER DATABASE dBname SET MULTI_USER;

 

I then changed the webservice calls to the database, such that the transaction scope for all read operations is set to IsolationLevel.Snapshot

 

var _transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Snapshot, Timeout = TimeSpan.FromMinutes(1) });

More information on snapshot isolation level can be found here:

http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx

It implements optimistic concurrency – such that user can read the last committed data, while its being modified.