Search code examples
entity-framework-4database-deadlocks

Deadlock with Entity Framework when inserting several rows in parallel


We are having a problem inserting several entities with EF in parallel. A WCF operation is called by a lot of processes to generate an entity with a different distributed transaction in each call. As we see in the sql server profiler it generates the following sql:

(@0 int,@1 nvarchar(32),@2 datetime2(7),@3 nvarchar(64),@4 int,@5 int,@6 bit)
insert [dbo].[CommandRequests](
   [CommandId]
 , [DeviceId]
 , [StartDateTime]
 , [EndDateTime]
 , [Parameters]
 , [Caller]
 , [Result]
 , [Priority]
 , [Timeout]
 , [ParentRequestId]
 , [IsSuccessful]
 , [Host])
  values (@0, @1, @2, null, null, @3, null, @4, @5, null, @6, null)

  select [CommandRequestId]
  from [dbo].[CommandRequests]
  where @@ROWCOUNT > 0 and [CommandRequestId] = scope_identity()   

So EF give us an insert and later a select. Because it is done in parallel lots of them are aborted by deadlock.

We are using the EF 4.0, not the 4.1 or 4.2.

Any idea how to solve this? I have seen this, but it is quite old: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/4f634d8f-1281-430b-b664-ec7ca413b387/


Solution

  • At the end the problem was with deadlocks in the serializable transaction, nothing to do with the creation of the id.

    Here I explain the problem: http://pablocastilla.wordpress.com/2012/01/19/deadlocks-in-serializable-transactions-with-sql-server/