Sql Server, Transaction Isolation and Entity Framework

Recently I had to work through some deadlock issues that I was getting while using EF.

Here are some things that I found out and is useful information for you to know:

1. EF by default uses SQL-Server’s default isolation mode, which is Read Committed. Something to know about read-committed reads is that by default it performs the reads using shared locks which will block reads of modified data.(Unless you use the setting READ_COMMITTED_SNAPSHOT and set it to on).
2. When EF performs a SaveChanges, it implicitly uses a transaction. This by default will be Read Committed.
3. But, if EF finds an ambient transaction, it will use that transaction.
4. So you can override the isolation level by using a transaction.
5. But, remember, by default transactions in .Net use “Serializable” isolation level, which is the MOST restrictive transaction isolation level.
6. So, its important to use a transaction, that uses an isolation level that makes sense for your operation. Here is an example:
• using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel= IsolationLevel.Snapshot }))
  {
          // update some tables using entity framework
          context.SaveChanges();
          transactionScope.Complete();
  }
7. I am using IsolationLevel.Snapshot in the above transaction to reduce the possibility of deadlocks. The reason for this can be seen in this paragraph from MSDN’s documentation:
• SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read, so SNAPSHOT transactions do not block other transactions from writing data. Transactions that write data do not block snapshot transactions from reading data. You need to enable snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option in order to use it.

 

MSDN:

• Deadlocking: http://msdn.microsoft.com/en-us/library/ms177433.aspx
• Making it real: Connections, Transactions, Performance and More: http://msdn.microsoft.com/en-us/library/orm-9780596520281-01-16.aspx
• Minimizing deadlocks: http://msdn.microsoft.com/en-us/library/ms191242.aspx
• Set Transaction Isolation:http://msdn.microsoft.com/en-us/library/ms173763.aspx
• IsolationLevel enumeration:http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx
• Using Snapshot Isolation: http://msdn.microsoft.com/en-us/library/tcbchxcb(v=VS.80).aspx