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

How to prevent and resolve deadlock problem in SQL Server?

In this article, I will share how do I solve entity framework deadlock issue. Recently, I faced one common but a big issue related to deadlock in the entity framework(EF6). we are working on the entity framework as 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 is update data in more than 10 tables in a single transaction. So that, 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 SQL server transaction is running at that time, SQL uses locking method at table or row level.

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

Hire Now!

Do You Need Dedicated SQL Server Developer for Hire?

Hire our best SQL developer on hourly, Full-time, Part-time bases. We are ready to help you resolve the problems for SQL server in EF web development.
Hire Now!

 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,  and use of a store procedure with an NOLOCK keyword (Select * from Test NOLOCK) in a query. this stored procedure is called from entity framework. Also, we can use SQL View with an NOLOCK keyword in a query to prevent deadlock.

To overcome this issue we have implement single solution of the whole project, which 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 ASP.NET MVC C# code to always read uncommitted data by setting isolation level.

Kindly follow the ASP.NET C# code, through you can resolve deadlock issue in the asp.net MVC project. The following code is showing 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
        DBContext dbContextReadOnly = new DBContext();
        //Repository
        IStudentRepository studentRepository { get; set; }
        //Close Connectoin
        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(product);
            }
            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(System.Data.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
http://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
Recommended technique for the resolving Deadlock issues in entity framework 6 is the easiest way and time saving for ASP.NET MVC Developer.

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

For More information,  Go to  ISOLATION LEVEL option.

Thank you for reading.

By | 2017-09-20T11:17:51+00:00 October 3rd, 2016|Asp.Net, Asp.Net MVC|