Deadlock with Entity Framework when inserting several rows in parallel

Question

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/

Selfevident

Answer 1

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/

Selfevident

Answer 2

The situation is still the same. EF doesn't have any additional features to avoid this. So your solution can be:

• Manual synchronization in the service so that only one call can insert record at time. This is pretty ugly and it will greatly affect throughput but it is quite easy solution to implement simple pessimistic locking only for this single operation so it depends on type of the application you are building.
• The select you see at the end is caused by using autogenerated ID. EF needs to be informed about this ID. You cannot turn this feature off only for inserting. What you can do is not using auto-generated Ids in the database and handle Id generation in your application. You will move Id generation outside of DB/EF and you will have full control over its synchronization. After that you will never see this select again (you must also set StoreGeneratedPattern for Id property to None). You can for example implement custom HiLo Id algorithm.

Andaman

We are thinking about changing the select query to: select scope_identity() as [CommandRequestId] where @@ROWCOUNT > 0 What do you think? – Selfevident

That query is auto generated by EF. How do you want to change it? – Andaman

you can change it in the model using a Function, we have already done that and it works. – Selfevident

You mean by mapping stored procedure to insert operation? – Andaman

Nop, it is similar, but you write the query in the xml of the model. <Function Name="InsertCommandRequest" IsComposable="false" > <CommandText> insert [dbo].[CommandRequests]( [CommandId] ... ) values (...) select scope_identity() as [CommandRequestId] where @@ROWCOUNT > 0 </CommandText> – Selfevident

Yes it is generally the same but you will lose ability to update your model from the database or vice-versa because default EF designer will delete your changes. In such case you can modify it and it should work. – Andaman

let us continue this discussion in chat – Selfevident

@PabloCastilla Did you find a fix as we seem to be struck with same issue :( – Parquet

@robjb: When you insert entity with autogenerated ID, EF needs to get that ID back to correctly set it in the entity. – Andaman

Answer 3

I think the reason is CommandRequestId is not the primary key. If you set it as primary key you will not get dead lock. I had the same issue and when I set the Identity column as primary key it worked fine.

Enchanter