Fixed: "MySqlException: Deadlock found when trying to get lock" in EF Core

Under load, half the orders failed with MySqlException: Deadlock found when trying to get lock; try restarting transaction, and with an HTTP 500. The code looked fine. Each order just locked its products in whatever order the client listed them, so two orders could each hold what the other needed.

What I Was Doing

In my .NET C# Web API, placing an order takes stock for each line, in a transaction, with EF Core 10.0.14 and MySQL 9.7.4:

src/Dewiride.Inventory.Api/Features/Orders/OrderEndpoints.cs (excerpt)

foreach (var line in request.Lines)
{
    // MySQL locks the row, checks the condition and changes the row in one
    // statement. A second buyer waits here, then sees the new quantity.
    var taken = await db.Products
        .Where(p => p.Id == line.ProductId && !p.IsArchived && p.QuantityOnHand >= line.Quantity)
        .ExecuteUpdateAsync(
            setters => setters
                .SetProperty(p => p.QuantityOnHand, p => p.QuantityOnHand - line.Quantity)
                .SetProperty(p => p.UpdatedAtUtc, now),
            ct);

    if (taken == 0)
    {
        // Leaving without a commit rolls back the stock already taken.
        return await ExplainRefusalAsync(db, line, ct);
    }
}

30 orders came in at once, half listing products 1 then 2, and half the other way round. How to Test a Race Condition in a Web API from the Terminal explains this command, and has a PowerShell version:

Terminal

(seq 15 | xargs -P 15 -I{} curl -s -o /dev/null -w "%{http_code}\n" -X POST http://localhost:5190/api/v1/orders -H "Content-Type: application/json" -d '{"customerName":"Buyer {}","customerEmail":"buyer{}@example.com","lines":[{"productId":1,"quantity":1},{"productId":2,"quantity":1}]}' &
seq 15 | xargs -P 15 -I{} curl -s -o /dev/null -w "%{http_code}\n" -X POST http://localhost:5190/api/v1/orders -H "Content-Type: application/json" -d '{"customerName":"Buyer {}","customerEmail":"buyer{}@example.com","lines":[{"productId":2,"quantity":1},{"productId":1,"quantity":1}]}' &
wait) | sort | uniq -c

What I Expected

30 orders, with some waiting a moment for each other.

What I Got

Output

  15 201
  15 500

API log

MySql.Data.MySqlClient.MySqlException (0x80004005): Deadlock found when trying to get lock; try restarting transaction

15 of 30, and the same again on the next run.

What I Checked

The numbers. No data was damaged: the quantity and the sum of the stock movements still agreed for both products, because the rolled-back orders took nothing.

MySQL's report. SHOW ENGINE INNODB STATUS gave the cycle:

Output (the deadlock section, shortened)

*** (1) TRANSACTION:
UPDATE `products` AS `p`
WHERE ((`p`.`Id` = 2) AND NOT (`p`.`IsArchived`)) AND (`p`.`QuantityOnHand` >= 1)
*** (1) HOLDS THE LOCK(S):
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
*** (2) TRANSACTION:
UPDATE `products` AS `p`
WHERE ((`p`.`Id` = 1) AND NOT (`p`.`IsArchived`)) AND (`p`.`QuantityOnHand` >= 1)
*** (2) HOLDS THE LOCK(S):
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
*** WE ROLL BACK TRANSACTION (2)

The first transaction is updating product 2, and the second product 1. Each holds the other's row.

Whether it was retried. The log has no will be retried line, because Oracle's provider retries nothing by itself.

Why It Happens

An UPDATE keeps its row lock until the transaction ends. Order A locks product 1 and wants 2. Order B locks 2 and wants 1. Neither can go on, so MySQL rolls one back. The deadlock handling page says what to do: "When modifying multiple tables within a transaction, or different sets of rows in the same table, do those operations in a consistent order each time."

The Fix

Sort the lines before the transaction, so every order locks its products lowest ID first:

src/Dewiride.Inventory.Api/Features/Orders/OrderEndpoints.cs (excerpt)

// Every order locks its products in the same order, lowest ID first, so two orders
// for the same products can't each hold what the other one needs.
var lines = request.Lines.OrderBy(l => l.ProductId).ToList();

And loop over lines instead of request.Lines. Then the second order waits at its first product, holding nothing, and no cycle can form.

Keep a retry for the rare one that still happens, since the same page says "Always be prepared to re-issue a transaction if it fails due to deadlock." List 1213 in errorNumbersToAdd, or use a custom strategy.

After the Fix

Output (the same 30 orders)

  30 201

30 of 30, with no retries needed.

Common Mistakes

Mistake	What happens	Do this instead
Looping in the order the client sent	Clients decide your lock order	Sort first
Adding only a retry	Half the work is thrown away and redone	Fix the order, and keep the retry
Sorting inside the retry only	It works, and sorts on every attempt	Sort once, before the strategy
Inserting a child row before updating its parent	A deadlock with no ordering problem in sight	Update the parent first

Troubleshooting

Symptom	Likely cause	Fix
Deadlocks with one product per order	A shared lock from a foreign key	The fix
Could not save changes, with this as the inner exception	It happened inside SaveChanges	The same fix
Still deadlocks after sorting	Another code path uses a different order	Compare the statements in the report

FAQ

Is a deadlock a bug in MySQL?

No. It's MySQL resolving a situation that had no other way out. The error handling page says "Both deadlocks and lock wait timeouts are normal on busy servers".

Conclusion

Two transactions that lock the same rows in opposite orders deadlock. Sort your rows before you start, and the second one waits instead. Here that took the failures from 15 of 30 to none. Keep a retry for the rest.

Additional Resources

• How to Minimize and Handle Deadlocks — what to change.
• Connection Resiliency — retries in EF Core.

Related

• How to Avoid Deadlocks in MySQL by Locking Rows in the Same Order
• How to Test a Race Condition in a Web API from the Terminal
• Fixed: "ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction" in MySQL