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.
Question on deadlocks in EF6
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
3 answers
Sort by: Newest
-
-
Stesvis 1,041 Reputation points
2022-09-16T15:11:31.563+00:00 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 isAPI 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? -
Lan Huang-MSFT 26,516 Reputation points Microsoft Vendor
2022-09-16T07:04:03.013+00:00 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.