Question on deadlocks in EF6

Question

Hello,
after some long investigation we detected that some deadlocks are happening in our SQL Server db.

We use EF6 for CRUD operations with the UnitOfWork pattern.

Someone suggested to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED globally in DbContext such as:

   public ApplicationDbContext()  
           : base("DefaultConnection", throwIfV1Schema: false)  
           {  
               Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");  
           }

Can anyone tell me if there would be any CONS, and what they are about, if we use this approach?

Thanks

Answer

Hi @Stesvis ,
A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

Entity Framework uses the SQL Server transaction isolation level by default.

The method you provide should 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;");

You can also 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();  
}

Best regards,
Lan Huang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

Thanks @Lan Huang-MSFT that's also what I found but was wondering about any downside of setting it on context level.
I tested it from the frontend and the time to consume the same API (that executes the query that randomly locks the table) went from 530ms to 1.2s (with the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;".

I am now thinking about the other option, using the scope at query level, but because I use UnitOfWork I am not sure if it will cause a conflict?
Also I am no entirely sure what I should wrap in the scope.
Let me show some code, the structure is API Controller --> Service --> Repository --> UnitOfWork:

Controller:

   workOrdersList = AppService.WorkOrders  
                           .Filter(clientId, filterViewModel, out recordsTotal, request.Start, request.Length, order?.Column, order?.Dir);

Service:

   using var unitOfWork = new UnitOfWork();  
   var workOrders = unitOfWork.WorkOrders.Filter(clientId, filter, orderByIndex, orderDirection);

Repository:

   var workOrders = ApplicationContext.WorkOrders  
   	.AsNoTracking()  
   	.Include(wo => wo.Client)  
   	.Include(wo => wo.CreatedByUser)  
   	.Include(wo => wo.AssignedToUser)  
   	.Include(wo => wo.CompletedByUser)  
   	.Include(wo => wo.Customer)  
   	.Include(t => t.OrderStatus)  
   	.Include(t => t.FertilizerData.Select(x => x.CleanType))  
   	.Include(wo => wo.ProductList.Select(wop => wop.Product))  
   	.Include(t => t.Truck.TruckProducts.Select(tp => tp.Product))  
   	.Include(t => t.PaymentOption)  
   	.Where(t =>  
   		t.ClientId == clientId)  
   	.OrderByDescending(t => t.CreatedDate);  
     
   	// ... other stuff...  
   	  
   workOrders = workOrders  
   	.Where(wo => true  
   		&& (filter.Id == null || wo.Id == filter.Id.Value)  
   		&& (filter.CustomerId == null || wo.CustomerId == filter.CustomerId.Value)  
   		&& (filter.ProductId == null || wo.ProductList.Any(x => x.ProductId == filter.ProductId.Value && x.Status != Status.Deleted))  
   		&& (string.IsNullOrEmpty(filter.CompletedByUserId) || wo.CompletedByUserId == filter.CompletedByUserId)  
   		&& (completedOnFrom == null || DbFunctions.TruncateTime(wo.CompletedDate) >= completedOnFrom)  
   		&& (completedOnTo == null || DbFunctions.TruncateTime(wo.CompletedDate) <= completedOnTo)  
   		&& (filter.OrderStatusId == null || wo.OrderStatusId == filter.OrderStatusId)  
   		&& (string.IsNullOrEmpty(filter.AssignedToUserId) || wo.AssignedToUserId == filter.AssignedToUserId)  
   		&& (filter.IsDirectDrop == null || wo.IsDirectDrop == filter.IsDirectDrop)  
   		&& (filter.IsPickUp == null || wo.IsPickUp == filter.IsPickUp)  
   		&& (filter.IsSchedule == null || (filter.IsSchedule == true && wo.Schedule != null) || (filter.IsSchedule == false && wo.Schedule == null))https://learn.microsoft.com/en-us/answers/questions/1010189/question-on-deadlocks-in-ef6.html?childToView=1010530#editor-preview-2  
   		&& (string.IsNullOrEmpty(filter.Status) ||  
   			(filter.Status == Status.Active &&  
   			(wo.Status == Status.Active ||  
   				(  
   					// hide completed delivery orders older than 1 day ago  
   					wo.Status == Status.Completed &&  
   					wo.CompletedDateUtc.HasValue &&  
   					limitShowDate < wo.CompletedDateUtc.Value  
   			))) ||  
   			wo.Status == filter.Status)  
   		&& (string.IsNullOrEmpty(filter.SearchValue) ||  
   			wo.CreatedDate >= oneYearBack && (  
   			!string.IsNullOrEmpty(wo.Customer.Company) && wo.Customer.Company.ToLower().Contains(filter.SearchValue.ToLower()) ||  
   			!string.IsNullOrEmpty(wo.AssignedToUserId) && (wo.AssignedToUser.FirstName.ToLower().Contains(filter.SearchValue.ToLower()) || wo.AssignedToUser.LastName.ToLower().Contains(filter.SearchValue.ToLower())) ||  
   			wo.LocationName.ToLower().Contains(filter.SearchValue.ToLower()) ||  
   			wo.Notes.ToLower().Contains(filter.SearchValue.ToLower()) ||  
   			wo.ProductList.Any(x => x.Product.ShortName.ToLower().Contains(filter.SearchValue.ToLower()))))  
   	)  
   	//.OrderBy(wo => wo.DueDate)  
   	.AsQueryable();

At this point the question is, should i use the TransactionScope in the Repository or in the Service, or does it even matter where?

Answer

a better approach than read uncommitted, is to change the sql database to row versioning (snapshot). configure the database as Read Committed Snapshot or Snapshot.

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16

Question on deadlocks in EF6

3 answers