Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
c# entity-framework entity-framework-6 sql transactionscope
Question
I'm getting a...
Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
...in the following code. How come?
// SIMPLE ORDERNUMBER LOGIC
var orderNumber = 1;
Order order = null;
using (TransactionScope scope = new TransactionScope())
{
if (db.Orders.Any(o => o.OrderNumber.HasValue))
{
// 1. Get the last successful order OrderNumber
var lastSuccessfulOrder = db.Orders.Where(o => o.OrderNumber.HasValue).OrderByDescending(o => o.OrderNumber).FirstOrDefault();
if (lastSuccessfulOrder != null)
{
orderNumber = lastSuccessfulOrder.OrderNumber.Value + 1;
}
}
// 2. Create the new order with null values except OrderNumber column
order = new Order();
order.OrderNumber = orderNumber;
db.Orders.Add(order);
System.Threading.Thread.Sleep(2000);
db.SaveChanges();
scope.Complete();
}
I'm looking in SQL Profiler at the Deadlock graph but I can't really understand it to be honest.
The Thread.Sleep(2000) I put there to simulate a transaction that takes a little bit longer to process; which btw seems to be the culprit in some way, because when I remove it I don't get any deadlock. Any ideas?
Here are the Deadlock Graphs:
Popular Answer
So by the deadlocks graphs looks like you are deadlocking because of the Serializable isolation level. Think on this scenario:
- Request 1 came and read the whole PK index to find out the latest Order Number.Locking it for writes as it is Serializable
- Request 1 sleeps for 2 seconds
- Request 2 came and read the whole PK index to find out the latest Order Number.Locking it for writes as it is Serializable
- Request 2 sleeps for 2 seconds.
- Request 1 awakes and try to write but the PK index is locked by Request 2 for writes.
- Request 2 awakes and try to write but the PK index is locked by Request 1 for writes.
There are different ways you can address this, two options here
- Generate order numbers for all orders not only successful ones. You can do this on the database with an IDENTITY column and you won't need to do that read.
- Change your Isolation level to Read Committed so the read doesn't block the table, but that means that your read could became "dirty" and you could end up generating duplicated order numbers. You can add a Unique key to handle that on database, and in case the constraint is violated you can retry again.
In general Serializable is not a good scaling isolation level, if you can do it with Read Commited or Read Commited Snapshot isolation levels better.
Also generating incremental Ids on code is better to be left to the database which have different mechanisms to avoid locks.
If you want to have consecutive order numbers (why? having a few gaps shouldn't be a big deal and will save you a lot of hassle) you could have a bottleneck on that order number generation. You could just make your db side single threaded and attend one request at a time so you don't need to even create transactions for this. But obviously you will have a scalability problem.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow