Transactions and Isolation Levels

The problem: two requests, one row

Our shop has only one phone left in stock. Two customers click “Buy” at the same moment. This is the code:

var product = await db.Products.FirstAsync(p => p.Id == productId, ct);
if (product.Stock > 0)
{
    product.Stock -= 1;
    await db.SaveChangesAsync(ct);
}

See what happens, step by step:

1. The first request reads the stock: 1.
2. The second request also reads the stock: 1.
3. Both see the condition as true.
4. Both set the stock to zero and save.
5. We sold one phone twice.

The interesting part: the SaveChangesAsync method already runs inside a transaction. So the problem is not a “missing transaction”. The problem is the gap between reading and writing. This is called a Race Condition.

What does a transaction guarantee?

A transaction has four guarantees. Together they are called ACID:

1. Atomicity. All changes are saved together, or none of them. If reducing the stock works but saving the order fails, both are rolled back.
2. Consistency. The database rules (foreign keys, unique keys, checks) are still true after the transaction.
3. Isolation. Concurrent transactions are separated from each other, up to a point. The isolation level decides “how far”.
4. Durability. Once saved, the data survives even a power cut.

All concurrency problems come from the third letter. Full isolation is expensive. So by default, databases do not use full isolation.

Isolation levels

Each level prevents some of these problems:

1. Dirty Read. My transaction sees a change that another transaction has not committed yet, and may roll back.
2. Non-Repeatable Read. I read one row twice and see two different values.
3. Phantom. I run one query twice, and the second time I see new rows.

Why not always pick the highest level? Because a higher level means more locks or more rejected transactions. So there is more waiting, more Deadlocks and more retries.

Locks or versioning?

Databases have two tools for isolation:

1. Locks. The writer locks the row. The reader must wait.
2. Row versioning (MVCC). The writer creates a new version. The reader reads the previous committed version and does not wait.

PostgreSQL always uses versioning. In SQL Server, the Read Committed level uses locks by default. The Read Committed Snapshot (RCSI) option turns on versioning, and then reads and writes no longer block each other. But two writes on the same row still wait for each other.

Fix one: atomic update

Back to the phone purchase. If the check and the decrease are in one statement, no gap is left. The database runs a single UPDATE statement atomically:

var updated = await db.Products
    .Where(p => p.Id == productId && p.Stock > 0)
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.Stock, p => p.Stock - 1), ct);

if (updated == 0)
    return Results.Conflict("Out of stock");

Why is this correct?

1. The UPDATE statement locks the row, checks the condition and decreases the stock. All together.
2. The second request waits for the lock. Then it checks the condition with the new stock (zero).
3. The condition is false, so no row changes and the result is zero.

This is the simplest and fastest way for counters and stock.

Fix two: version check (Optimistic Concurrency)

Now a different problem. In the support panel, two clerks open the page of the same order. Clerk A changes the address and clicks save. A few seconds later, clerk B changes the status and clicks save. B’s form sends the whole order, with the old address.

Click the buttons and see what happens:

This is called a Lost Update. The last write won, and A’s change was lost silently. Here a transaction does not help at all:

1. These are two separate requests, minutes apart.
2. Each save is correct and successful on its own.
3. The problem is that B saves with stale data.

The fix is a version column. In SQL Server, a rowversion column changes automatically on every change to the row:

public class Order
{
    public int Id { get; set; }
    public string ShippingAddress { get; set; } = "";
    public OrderStatus Status { get; set; }
    [Timestamp] public byte[] RowVersion { get; set; } = [];
}

The form gets the version when it opens, and sends it back when it saves:

app.MapPut("/orders/{id:int}", async (int id, OrderForm form, ShopDb db, CancellationToken ct) =>
{
    var order = await db.Orders.FindAsync([id], ct);
    if (order is null) return Results.NotFound();

    // Compare with the version the clerk saw, not the one we just read
    db.Entry(order).Property(o => o.RowVersion).OriginalValue = form.RowVersion;
    order.ShippingAddress = form.ShippingAddress;
    order.Status = form.Status;

    try
    {
        await db.SaveChangesAsync(ct);
        return Results.NoContent();
    }
    catch (DbUpdateConcurrencyException)
    {
        return Results.Conflict("Someone else changed this order. Reload it.");
    }
});

How does it work?

1. EF Core adds a condition to the UPDATE statement: “only if the version is still the same”.
2. A’s save has changed the version. So the condition is false for B, and no row changes.
3. EF Core throws a DbUpdateConcurrencyException, and we return status code 409.
4. The form tells B to open the order again. What to do next is a business decision.

Deadlock

Now two orders arrive at the same time. Each order, in one transaction, decreases the stock of its items one by one:

1. Order one: first the phone, then the case.
2. Order two: first the case, then the phone.

The database detects this cycle and picks one transaction as the victim. The fixes:

1. Fixed lock order. Always sort the items by id. Now both transactions go to the phone first. The second one just waits, and there is no deadlock.
2. Short transactions. HTTP calls, sending email and heavy calculation must not be inside a transaction. Shorter locks mean fewer conflicts.
3. The right index. Without an index, the database reads and locks more rows to find the row it needs.
4. Retry. Deadlocks never go fully to zero. Run the whole transaction again.
5. Find the cause. In SQL Server the Deadlock Graph report, and in PostgreSQL the database log, show which queries were involved.

In EF Core, the EnableRetryOnFailure option retries transient errors (including deadlocks). But when you open a transaction yourself, you must put all the work inside an Execution Strategy, so the whole transaction runs again:

var strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    await using var tx = await db.Database.BeginTransactionAsync(ct);

    // Same order for every transaction: sort by product id
    foreach (var line in order.Lines.OrderBy(l => l.ProductId))
    {
        var updated = await db.Products
            .Where(p => p.Id == line.ProductId && p.Stock >= line.Quantity)
            .ExecuteUpdateAsync(s => s.SetProperty(p => p.Stock, p => p.Stock - line.Quantity), ct);
        if (updated == 0)
            throw new OutOfStockException(line.ProductId);
    }

    db.Orders.Add(order);
    await db.SaveChangesAsync(ct);
    await tx.CommitAsync(ct);
});

Key rules

1. Read, then decide, then write means danger. If you can, write the check and the change in one atomic statement.
2. For long forms, use a version check. Never hold a lock between two HTTP requests.
3. Keep the transaction short. No network work inside a transaction.
4. Take locks in a fixed order. For example, always by row id.
5. Have a retry for deadlocks. Retry the whole transaction, not only the last statement.
6. Choose the isolation level on purpose. Know the default, and raise it only where you need to.

Common mistakes