Entity Framework Deadlocks and Concurrency

Question

We make considerable use of Entity Framework in a database first model with entity framework 6 and SqlSever 2012.

We have a number of fairly long running processes (10's of seconds) which each create an object of the same type with different data these objects in their creation both write and delete data in the database using the entity framework. So far so good. In order to improve the performance of the application we are looking to runs these operations in parallel and as such are using the Task construct to achive this as follows:

Private Async Function LongRunningProcessAsync(data As SomeData) As Task(Of LongRunningProcessResult)
    Return Await Task.Factory.StartNew(Of LongRunningProcessResult)(Function()
                                                       Return Processor.DoWork(data)
                                                     End Function)             
End Function

we run 10 of these and wait for them all to complete using Task.WaitAll

Class Processor
    Public Function DoWork(data As SomeData) As LongRunningProcessResult
        Using context as new dbContext() 
           ' lots of database calls 
           context.saveChanges()
        end Using

        ' call to sub which creates a new db context and does some stuff
        doOtherWork()

        ' final call to delete temporary database data
        using yetAnotherContext as new dbContext()
            Dim entity = yetAnotherContext.temporaryData.single(Function(t) t.id = me.Id)
            yetAnotherContext.temporaryDataA.removeAll(entity.temporaryDataA)
            yetAnotherContext.temporaryDataB.removeAll(entity.temporaryDataB)
            yetAnotherContext.temporaryData.remove(entity)

            ' dbUpdateExecption Thrown here
            yetAnotherContext.SaveChanges()
        end using
    End Function
End Class

this works well ~90% of the time the other 10% it deadlocks the database server with an inner deadlocking exception

all processors use the same tables but share absolutely no data between processes (and do not depend on the same FK rows) and create all their own entityframework contexts with no shared interaction between them.

reviewing profiling behavior of the Sql Server instance we see a large number of very short lived lock acquisitions and releases between each successful query. Leading up to a eventual deadlock chain:

Lock:Deadlock Chain Deadlock Chain SPID = 80 (e413fffd02c3)         
Lock:Deadlock Chain Deadlock Chain SPID = 73 (e413fffd02c3)     
Lock:Deadlock Chain Deadlock Chain SPID = 60 (6cb508d3484c) 

The locks themselves are of type KEY and the deadlocking queries are all for the same table but with different keys of the form:

exec sp_executesql N'DELETE [dbo].[temporaryData]
WHERE ([Id] = @0)',N'@0 int',@0=123

We relatively new to the entity framework and are at a loss to identify the root cause of what appear to over-scoped locks (I'm unable to identify through sql profiler the exact rows being locked).

EDIT: deadlock.xdl

EDIT2: Calling saveChanges after each remove statement removes the deadlock still don't quite understand why it was deadlocking

Answer 1

You appear to be the victim of Lock Escalation

To enhance performance, Sql Server (and all modern DB engines) will convert many low level fine grain locks into a few high level coarse grain locks. In your case, it goes from row level locks to a full table lock after the threshold is exceeded. You can resolve this a few different ways:

1. One solution is to call SaveChanges() which you have already done. That will release the lock sooner, preventing lock escalation from occurring, as lower lock count = less likely to hit the escalation threshold.
2. You could also change the isolation level to read uncommitted, which would lower the number of locks by allowing dirty reads, which would also prevent lock escalation from occurring.
3. Finally, you should be able to send an ALTER TABLE command using SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } ). However table level locks are still possible even when Disabled. MSDN points to the example of scanning a table with no clustered index under a serializable isolation level. See here: https://msdn.microsoft.com/en-us/library/ms190273(v=sql.110).aspx

In your case, the solution you have of calling save changes, resulting in the transaction being committed and the lock being released is the preferable option.