EF: Update the same DB table row in multiple threads

Question

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!

probably because all contexts and transactions need a writelock on the OrderItems table, and none is releasing its locks until the read operation is complete. this results in a deadlock for 9 of the contexts - it simply won't work with 2PL. Also, keep in mind that the DbContext is not thread safe - you can't expect it to work flawlessly when using it in a multi threaded environment. — DevilSuichiro
– DevilSuichiro, Commented Jun 23, 2017 at 8:08

Kantora · Accepted Answer · 2017-06-23 06:24:48Z

1

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.

answered Jun 23, 2017 at 6:24

Kantora

1114 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3818229 Over a year ago

If I set IsolationLevel.RepeatableRead the final Sum by order items is not equals with Amount property from Order.

Kantora Over a year ago

Of course. This is happening because you are trying to calculate it from parallel transactions. So you are trying to modify the same data from parallel threads simultaneously. Even if you are lucky and don't get transaction deadlock from the database you update it with incorrect data. As between reading the amount of order sum and writing this number to the database (even if it is two sequential lines of code) the number of order items can be changed, So to be sure - you need triggers. They are designed in the DBMS to maintain data consistency.

user3818229 Over a year ago

So there is no another way to fix multi-threading problem using only database tools, right? May be I can lock some row in table, can't I?

Kantora Over a year ago

Hmm... There are many ways to do this... You can use something like this msdn.microsoft.com/en-us/library/ms189823(SQL.90).aspx, in case of mssql, or you can use Redis to provide cross-process locks or e.t.c. Or you can create a dictionary of objects for each order and use lock statement to prevent parallel processing of the same order (but this will fail if you update order from other process or from another method), But, from my point of view - everything like this is somewhat rectal.

user3818229 Over a year ago

Thanks for ur replay. But I need to fix this problem by DB tools only. I've read about updlock, rowlock and now thinking how can I use them in current case.

Collectives™ on Stack Overflow

EF: Update the same DB table row in multiple threads

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related