How To Prevent Dead Lock in Entity Framework

Introduction

I recently worked 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 required more time to execute because some other select query created a lock on a table. In this article, we will learn how to prevent deadlocks when using Entity Framework as the DAL of your application. 

A deadlock is a situation when two or more tasks are blocking each other by having a lock on a resource the other is trying to lock. Deadlocks are bad for an application's performance, and it is critical for every software architect and developer to make sure there are no deadlock situations in your code or backend. 

SQL Transaction Isolation Level and Lock

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

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

Read uncommitted

This is the lowest Isolation Level. The request has no shared lock; it allows us to read the data being modified by another transaction. In other words, we can read values modified by another transaction before committing to the SQL Server.

Read committed (default Isolation Level)

This is the default Isolation Level of the SQL Server. It only retrieves committed data. Here there is a shared lock on the request; there is an exclusive lock on data while the other transaction modifies it. 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 the Read Committed Isolation Level. This maintains a shared lock on the record and reads it until the transaction is not finished. In other words, if any transaction tries to modify records, it must 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 the insertion of new rows. A repeatable read Isolation Level does not guarantee that a query result is always the same, but it does ensure that the rows that have been read are locked and that no other transaction can modify it.

Serializable (highest level)

Serializable is close to repeatable read Isolation Level, but it prevents phantom rows. A serializable Isolation Level is an applied range lock so that another 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 does not read uncommitted rows or data. In other words, if we read rows from any table and another transaction tries to modify rows, then the snapshot Isolation Level reads the last committed data on that row.

Problem statement

Implementing NOLOCK in LINQ to Entities Query

How can LINQ to Entity Query be prevented from blocking the writer from the reader and vice versa?

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 the 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 levels for the 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 using 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.

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

Conclusion

Deadlocks are not just in the databases but could also be in your code while using multi-threading and tasks. Here is an article on Deadlock and Ways to Avoid It. This article taught us how to Prevent Dead Lock in Entity Frameworks with code examples.