How to prevent EntityFramework deadlock when concurrently running these two statements

c#, deadlock, entity-framework

Solution

I found an article that talks about this HERE. It basically sounds like you can start and stop a transaction that surrounds your EF call... The block gives the following code example so credit goes to Diego B Vega... The blog post also links to another blog with additional information.

using (var scope = new TransactionScope(TransactionScopeOption.Required, new 
    TransactionOptions { IsolationLevel= IsolationLevel.Snapshot }))
{
    // do something with EF here
    scope.Complete();
}

Problem

Calls into my web service use the following code to ensure that the caller has a valid session. If a valid session found then it updates the session details and saves the changes. All simple enough and works fine. ``` // Create the Entity Framework context using(MyContext ctx = CreateMyContext()) { // Get the user session for the client session UserSession session = (from us in context.UserSessions.Include("UserEntity") where us.SessionId = callerSessionId select us).FirstOrDefault<UserSession>(); if (session == null) return false; else { // Update session details session.Calls++; session.LastAccessed = DateTime.Now.Ticks; Console.WriteLine("Call by User:{0}", session.UserEntity.Name); // Save session changes back to the server ctx.SaveChanges(); return true; } } ``` All works fine until the same caller, and hence the same session, makes multiple concurrent calls (which is perfectly valid to happen). In this case I sometimes get a deadlock. Using SQL Server Profiler I can see the following is happening. Caller A performs the select and acquires a shared lock on the user session. Caller B performs the select and acquires a shared lock on the same user session. Caller A cannot perform its update because of Caller B's shared lock. Caller B cannot perform its update because of caller A's shared lock. Deadlock. This seems like a simple and classic deadlock scenario and there must be a simple method to resolve it. Surely almost all real world applications have this same problem.But none of the Entity Frameworks books I have mention anything about deadlocks.

Original source