Entity Framework DeadLock/NoLock [ISSUE RESOLVED] - SQL Server Solution

How to prevent and resolve deadlock problem in SQL Server?

In this article, I will share how would I comprehend element system gridlock issue. As of late, I confronted one normal yet a major issue identified with gridlock in the substance framework(EF6). we are dealing with the substance structure as information get to layered venture. Venture in light of employment planning utilizing foundation administrations, having SQL server tasks like include, refresh, erase and so on. 

Utilizing SQL server exchange of element framework(EF) which is refresh information in excess of 10 tables in a solitary exchange. So that, we are driving SQL server information from those database tables and demonstrating it on a front end site frame. Assume, We need to show all the data from all the database tables. At the point when SQL server exchange is running around then, SQL utilizes locking strategy at table or line level. 

Presently now, we have experienced a gridlock issue in substance system, likewise discover the aggregate changeless answers for halt issues identified with element structure 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.

Deadlock Problem statement

SQL server exchange is running in windows benefit which bolts the tables/columns and we are perusing information from table to show on our site. be that as it may, stops are a general issue with database applications utilizing exchanges, so we are getting the accompanying halt blunder in a site. 

• Blunder: Transaction (Process ID 57) was stopped on bolt assets with another procedure and has been picked as the gridlock casualty. Rerun the exchange. 
• Stop issues with EF.

Deadlock Solution

We have numerous approaches to determine halt issues. Here I am just examining how to anticipate halt in Entity Framework. 

There are alternatives like this snap here, and utilization of a putaway methodology with a NOLOCK watchword (Select * from Test NOLOCK) in an inquiry. this putaway system is called from substance structure. Likewise, we can utilize SQL View with a NOLOCK catchphrase in an inquiry to avoid stopping. 

To defeat this issue we have executed single arrangement of the entire undertaking, which READ UNCOMMITTED information to show on a site. 

Substance structure utilizes SQL server exchange ISOLATION LEVEL as a matter of course which READ COMMITTED information. We have refreshed ASP.NET MVC C# code to dependably read uncommitted information by setting confinement level. 

Generously take after the ASP.NET C# code, as you can resolve stop issue in the asp.net MVC venture. The accompanying code is demonstrating all READ UNCOMMITTED information on the site.

Example Code

using System;

using System.Collections.Generic;
using System.Linq;

using System.Web.Mvc;
using System.Web;

using Repositories.EntityFramework;
using Repositories;

namespace Web.Controllers
using System.Data;

{

    public class TestController : Controller
{
        //dbContext

        //Repository
DBContext dbContextReadOnly = new DBContext();

        //Close Connectoin
IStudentRepository studentRepository { get; set; }

                base.Dispose(disposing);
protected override void Dispose(bool disposing)
        {
            try
            {
            }

                if (dbContextReadOnly.Database.Connection.State == ConnectionState.Open)
finally
            {
                    dbContextReadOnly.Database.Connection.Dispose();
            }
        }


        public void InitialiseController()
public TestController()
        {
            InitialiseController();
        }

        {
            //Check DB connection

                dbContextReadOnly.Database.Connection.Open();
if (dbContextReadOnly.Database.Connection.State == ConnectionState.Closed)
            {
                //SET TRANSACTION ISOLATION LEVEL

            studentRepository = new StudentRepository(dbContextReadOnly);
dbContextReadOnly.Database.ExecuteSqlCommand(SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;);
            }
            //Initialise Repository
        }

        public ActionResult Index()
        {
            //Read lock data

                var student = studentRepository.Query.Where(x => x.Id == 1).FirstOrDefault();
try
            {
                //Read locked table Data
                var studentList = studentRepository.Query.ToList();
                //Read locked item
                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())

                    studentRepository = new StudentRepository(dbContextWriteOnly);
using (var transactionResult = dbContextWriteOnly.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
                {
                    // Re-Initialise Repository
                    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 tricksrelated to asp.net mvc development.

For More information,  Go to  ISOLATION LEVEL option.

Thank you for reading.