user3818229
user3818229

Reputation: 1647

EF: Update the same DB table row in multiple threads

I have an education task and don't understand what's wrong in my code. I'm trying to update DB table and isolate this logic in the transaction. Finally, I get a deadlock. Can someone explain to me what's I doing wrong, please?
Here my thread creation logic:

for (int i = 0; i < 10; i++)
{
    var thread = new Thread(() => UpdateOrder(orderId));
    threadList.Add(thread);
    thread.Start();
}
foreach (var t in threadList)
{
    t.Join();
}
//Display sum by order items amount and amount property from order entity
WriteAmount(orderId);

And UpdateOrder method

public static void UpdateOrder(int orderId)
{
    using (var db = new OrderContext()
    {
        using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew
            ,new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))
        {
            var o1 = new OrderItem { Title = "title", Amount = 50, Count = 1, OrderId = orderId };
            db.OrderItems.Add(o1);
            var order = db.Orders.Find(orderId); //Include(o => o.Items).Single(o => o.Id == orderId);
            order.Amount += 50; //order.Items.Sum(oi => oi.Amount);
            db.SaveChanges();
            transactionScope.Complete();
        }
    }
}

I've created own context for each thread. Why my DB detect a deadlock?
This is the exception message: "Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Thanks in advance!

Upvotes: 1

Views: 1673

Answers (1)

Kantora
Kantora

Reputation: 111

You have the problem on the database side. Why did you use the IsolationLevel.RepeatableRead transaction type? Suppose this is the cause. You can read about different isolation level here: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql.

I'll advise you to: 1. Change isolation level to default READ COMMITTED 2. Remove order.Amount += 50;. Instead, create a trigger on the database side to handle INSERT/UPDATE/DELETE events on OrderItems table and modify the Orders table.

Upvotes: 1

Related Questions