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 &gt; 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/

score 3 · Answer 1 · answered Nov 14 '12 at 16:40

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.

answered Nov 14 '12 at 16:40

Asad Ullah

87
4

score 3 · Accepted Answer · answered Jan 19 '12 at 10:20

3

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/

answered Jan 19 '12 at 10:20

Pablo Castilla

2,723
2
28
33

score 2 · Answer 3 · edited May 23 '17 at 12:23

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.

edited May 23 '17 at 12:23

Community

1
1

answered Dec 29 '11 at 18:29

Ladislav Mrnka

360,892
59
660
670

We are thinking about changing the select query to: select scope_identity() as [CommandRequestId] where @@ROWCOUNT > 0 What do you think? – Pablo Castilla Jan 03 '12 at 10:59
That query is auto generated by EF. How do you want to change it? – Ladislav Mrnka Jan 03 '12 at 11:20
you can change it in the model using a Function, we have already done that and it works. – Pablo Castilla Jan 03 '12 at 11:36
You mean by mapping stored procedure to insert operation? – Ladislav Mrnka Jan 03 '12 at 11:38
Nop, it is similar, but you write the query in the xml of the model. insert [dbo].[CommandRequests]( [CommandId] ... ) values (...) select scope_identity() as [CommandRequestId] where @@ROWCOUNT > 0 – Pablo Castilla Jan 03 '12 at 11:40
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. – Ladislav Mrnka Jan 03 '12 at 11:42
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/6345/discussion-between-pablo-castilla-and-ladislav-mrnka) – Pablo Castilla Jan 03 '12 at 11:46
@PabloCastilla Did you find a fix as we seem to be struck with same issue :( – Nexus Jan 18 '12 at 10:13
@robjb: When you insert entity with autogenerated ID, EF needs to get that ID back to correctly set it in the entity. – Ladislav Mrnka Oct 09 '12 at 10:31

Deadlock with Entity Framework when inserting several rows in parallel

3 Answers3