Deadlock in the Entity Framework(EF6) Issue Resolved – SQL Server Solution

How to prevent and resolve deadlock problems in SQL Server?

In this article, I will share how I solve the entity framework deadlock issue. Recently, I faced one common but big issue related to deadlock in the entity framework(EF6). we are working on the entity framework as a data access layered project. Project-based on job scheduling using background services, having SQL server operations like add, update, delete, etc.

Using SQL server transaction of entity framework(EF) which updates data in more than 10 tables in a single transaction. So, we are driving SQL server data from those database tables and showing it on a front-end website form. Suppose, We want to display all the information from all the database tables. When an SQL server transaction is running at that time, SQL uses the locking method at the table or row level.

Now at this point, we have encountered a deadlock issue in the entity framework and also found out the total permanent solutions for deadlock issues related to entity framework 6.

Do You Need a Dedicated SQL Server Developer for Hire?

Hire our best SQL developer on an hourly, Full-time, or part-time basis. We are ready to help you resolve the problems for SQL servers in EF web development

Deadlock Problem statement:

SQL server transaction is running in Windows service which locks the tables/rows and we are reading data from table to display on our website. but deadlocks are a general problem with database applications using transactions, so we are getting the following deadlock error in a website.

• Error: Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
• Deadlock issues with EF

Deadlock Solution:

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

There are options like this [click here](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql),  and use of a store procedure with a NOLOCK keyword (Select * from Test NOLOCK) in a query. this stored procedure is called from the entity framework. Also, we can use SQL View with an NOLOCK keyword in a query to prevent deadlock.

To overcome this issue we have to implement a single solution for the whole project, which is READ UNCOMMITTED data to display on a website.

Entity framework uses SQL server transaction ISOLATION LEVEL  by default which READ COMMITTED data. We have updated the ASP.NET MVC C# code to always read uncommitted data by setting the isolation level.

Kindly follow the ASP.NET C# code, though you can resolve the deadlock issue in the asp.net MVC project. The following code shows all READ UNCOMMITTED data on the website.

Example Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Repositories;
using Repositories.EntityFramework;
using System.Data;

namespace Web.Controllers
{
    public class TestController : Controller
    {
        // dbContext
        private DBContext dbContextReadOnly = new DBContext();
        
        // Repository
        private IStudentRepository studentRepository { get; set; }

        // Close Connection
        protected override void Dispose(bool disposing)
        {
            try
            {
                base.Dispose(disposing);
            }
            finally
            {
                if (dbContextReadOnly.Database.Connection.State == ConnectionState.Open)
                {
                    dbContextReadOnly.Database.Connection.Dispose();
                }
            }
        }

        public TestController()
        {
            InitialiseController();
        }

        public void InitialiseController()
        {
            // Check DB connection
            if (dbContextReadOnly.Database.Connection.State == ConnectionState.Closed)
            {
                // SET TRANSACTION ISOLATION LEVEL
                dbContextReadOnly.Database.Connection.Open();
                dbContextReadOnly.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
            }

            // Initialise Repository
            studentRepository = new StudentRepository(dbContextReadOnly);
        }

        public ActionResult Index()
        {
            // Read lock data
            try
            {
                // Read locked table data
                var studentList = studentRepository.Query.ToList();

                // Read locked item
                var student = studentRepository.Query.Where(x => x.Id == 1).FirstOrDefault();
                
                return View(student);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        [HttpPost]
        public ActionResult Index(int id)
        {
            // Add/Update/Delete data using TransactionScope
            using (var dbContextWriteOnly = new DBContext())
            {
                using (var transactionResult = dbContextWriteOnly.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
                {
                    // Re-Initialise Repository
                    studentRepository = new StudentRepository(dbContextWriteOnly);

                    try
                    {
                        var student = studentRepository.Query.Where(x => x.Id == id).FirstOrDefault();
                        student.DateTimeStamp = DateTime.Now;
                        studentRepository.SaveChanges();
                        transactionResult.Commit();

                        return View();
                    }
                    catch (Exception ex)
                    {
                        transactionResult.Rollback();
                        throw ex;
                    }
                }
            }
        }
    }
}

Pros and Cons of Using Read-Uncommitted and SQL NoLock:

• https://sqlblog.com/blogs/tamarick_hill/archive/2013/05/06/pros-cons-of-using-read-uncommitted-and-nolock.aspx

Reference Links:

• https://msdn.microsoft.com/en-us/data/dn456843.aspx
• https://msdn.microsoft.com/en-IN/library/ms173763.aspx
• https://blogs.msdn.microsoft.com/diego/2012/03/31/tips-to-avoid-deadlocks-in-entity-framework-applications/

Conclusion:

The recommended technique for resolving Deadlock issues in entity framework 6 is the easiest way and time-saving for ASP.NET MVC Developers.

With the use of isolation level, We can show READ UNCOMMITTED information on the ASP.NET MVC website like the above example. we have lots of tips and tricks related to asp.net MVC development.

For more information,  Go to the ISOLATION LEVEL option.

Thank you for reading.