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:
So by the deadlocks graphs looks like you are deadlocking because of the Serializable isolation level. Think on this scenario:
There are different ways you can address this, two options here
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.