Prevent Dead Lock in Entity Framework

Introduction

Recently I was working with the Entity Framework as a data access layer based project. We found many performance issues with this application. We encountered some queries that created a deadlock or that require more time to execute because of some other select query created a lock on the table. Generally a deadlock is a situation when two or more tasks are blocking each other by each having a lock on a resource that the other is trying to lock.

SQL Transaction Isolation Level and Lock

Isolation Level defines the degree to which one transaction must be isolated from another transaction that makes changes in the data.

There are mainly four types of Isolation Levels available with SQL Server.

Read uncommitted

This is the lowest Isolation Level. There is no shared lock on the request, it allow us to read the data being modified by another transaction. In other words, we can read values that are modified by another transaction before they are committed to the SQL Server.

Read committed (default Isolation Level)

This is the default Isolation Level of SQL Server. It only retrieves committed data. Here there is a shared lock on the request; there is an exclusive lock on data while it is modified by the other transaction. In other words, modified data will not be visible within another transaction until it is committed to the database. All queries will wait until the transaction is completed and the lock is released.

Repeatable Read

This is similar to Read Committed Isolation Level. This maintains a shared lock on the record and it reads this record until the transaction is not finished. In other words, if any transaction tries to modify records then it is forced to wait for a read transaction to complete. There is a phantom read in this type of transaction Isolation Level because SQL Server locks the row it reads but does not prevent insertion of new rows. Repeatable read Isolation Level does not guarantee that a query result is always the same but it does guarantee that the rows that have been read are locked and no other transaction can modify it.

Serializable (highest level)

Serializable is very close to repeatable read Isolation Level but it prevents the phantom rows. Serializable Isolation Level is an applied range lock so that an other transaction cannot insert a new row within this row. That prevents phantom rows.

Additionally Snapshot Isolation Level is introduced in SQL Server 2005. Snapshot Isolation Level does not apply a lock while reading a row but it does not read uncommitted rows or data. In other words, if we read rows from any table and another transaction is trying to modify rows then snapshot Isolation Level reads the last committed data on that row.

Problem statement

Implementing NOLOCK in LINQ to Entities Query

How to prevent LINQ to Entity Query blocking writer from reader and vice versa?

Solution

We have many ways to prevent query blocking. Here I am only discussing how to prevent deadlock in Entity Framework.

Entity Framework is never an introduced transaction Isolation Level on queries. Entity Framework uses SQL Server transaction Isolation Level by default.

1. Use Read uncommitted transaction at Context Level
   
   We can set Isolation Level using the ExecuteStoreCommand method of the context. It would affect generated LINQ to Entity queries.
   
   //Example
   //With ObjectContext

   Entities entities  =  new Entities();
   entities.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

   //With DBContext

   Var entities  =  newDBContext();

   Entities. Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
   

2. Use Read uncommitted at the transaction level
   
   DBContext or Object context also supports explicitly setting the transaction on the context. Using transaction scope we can set transaction Isolation Level for current transaction.
   
   //Example
   using (TransactionScope scope = new                                                                                        TransactionScope(TransactionScopeOption.Required
                      , newTransactionOptions {
   IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
   {
       //do your code or write your query.
       scope.Complete();
   }
   
   We also have other options like use of a Store Procedure with a NOLOCK Query and this Stored Procedure is called from Entity Framework. Also we can use SQL View with a NOLOCK query to prevent deadlock.

Conclusion

Using the methods described above we can prevent deadlock. We can also use Snapshot Isolation Level at transaction scope. It is a good idea to use Snapshot Isolation Level since then there is no lock placed on a data row when it is read, so snapshot transaction never blocks another transaction.