How to prevent EntityFramework deadlock when concurrently running these two statements

Question

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.

Answer 1

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

Answer 2

Will the following work for you?

using(MyContext ctx = CreateMyContext())
{

     ctx.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");


     // Get the user session for the client session         
     ...
}