Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Question

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: enter image description here

PussInBoots

View more on Stack Overflow

Juan · Accepted Answer · 7/20/2015 11:56:09 AM

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.

Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related