Optimistic vs pessimistic concurrency in EF Core, explained with a wallet balance

Short answer: when two requests read the same balance and both decide "there's enough money", you get a race condition, and the classic symptom is money that appears or disappears. EF Core gives you three tools: optimistic concurrency (a rowversion token makes the second save fail so you can retry), an atomic conditional update (check and write in one SQL statement), and pessimistic locking (lock the row so the second request waits). Pick by how much logic sits between the read and the write.

A real example: the double withdrawal

A wallet holds 100. Two withdrawal requests of 80 arrive at the same moment, maybe a double-click, maybe a retry from a mobile app on a bad connection. Each request does the obvious thing:

1. read the balance (100),
2. check it covers 80 (it does),
3. write 100 − 80 = 20.

Both requests pass step 2 before either reaches step 3. Both succeed. The user withdrew 160 from a wallet that held 100, and the stored balance says 20. No exception, no log line: the code did exactly what it was told.

Wrapping it in a transaction doesn't help by itself. Under SQL Server's default READ COMMITTED isolation, both transactions can read the same committed balance. The protection has to come from somewhere else.

Runnable code

A Wallet with a SQL Server rowversion. EF Core adds it to the WHERE clause of every UPDATE:

using System.ComponentModel.DataAnnotations;
using System.Data;
using Microsoft.EntityFrameworkCore;

public sealed class Wallet
{
    public Guid Id { get; set; }

    public long BalanceRials { get; set; }

    [Timestamp]
    public byte[] Version { get; set; } = [];
}

public sealed class WalletDb(DbContextOptions<WalletDb> options) : DbContext(options)
{
    public DbSet<Wallet> Wallets => Set<Wallet>();
}

public sealed class InsufficientFundsException(Guid walletId) : Exception($"Wallet {walletId} has insufficient funds.");

1. Optimistic: detect the conflict and retry

public async Task WithdrawOptimisticAsync(Guid walletId, long amount, CancellationToken ct)
{
    for (var attempt = 1; ; attempt++)
    {
        var wallet = await db.Wallets.SingleAsync(w => w.Id == walletId, ct);
        if (wallet.BalanceRials < amount)
        {
            throw new InsufficientFundsException(walletId);
        }

        wallet.BalanceRials -= amount;
        try
        {
            await db.SaveChangesAsync(ct);
            return;
        }
        catch (DbUpdateConcurrencyException) when (attempt < 3)
        {
            // Someone changed the row since we read it: forget our copy and re-read.
            db.ChangeTracker.Clear();
        }
    }
}

The UPDATE only matches when the row's Version is still the one we read. If another request got there first, zero rows match, EF Core throws DbUpdateConcurrencyException, and we re-read the new balance and check again.

2. Atomic: make the check part of the write

public async Task<bool> TryWithdrawAtomicAsync(Guid walletId, long amount, CancellationToken ct)
{
    var updated = await db.Wallets
        .Where(w => w.Id == walletId && w.BalanceRials >= amount)
        .ExecuteUpdateAsync(s => s.SetProperty(w => w.BalanceRials, w => w.BalanceRials - amount), ct);

    return updated == 1;
}

This becomes a single UPDATE … SET BalanceRials = BalanceRials - @amount WHERE Id = @id AND BalanceRials >= @amount. The database evaluates the condition and the write under one row lock, so there is no window between "check" and "write" to race in. If the balance is too low, zero rows change and you get false.

3. Pessimistic: lock the row first

public async Task WithdrawPessimisticAsync(Guid walletId, long amount, CancellationToken ct)
{
    await using var transaction = await db.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted, ct);

    var wallet = await db.Wallets
        .FromSql($"SELECT * FROM Wallets WITH (UPDLOCK, ROWLOCK) WHERE Id = {walletId}")
        .SingleAsync(ct);

    if (wallet.BalanceRials < amount)
    {
        throw new InsufficientFundsException(walletId);
    }

    wallet.BalanceRials -= amount;
    await db.SaveChangesAsync(ct);
    await transaction.CommitAsync(ct);
}

UPDLOCK takes an update lock on the row for the rest of the transaction, so a second request running the same SELECT waits until the first commits. Then it reads the new balance. FromSql with an interpolated string still sends walletId as a parameter, so this isn't string concatenation.

Prove it under load

Don't trust any of these on faith. Seed a wallet with 100, fire 20 parallel withdrawals of 10 against a real SQL Server, and assert two things: exactly 10 succeed, and the final balance is 0, never negative. Do the same with the naive read-check-write version and watch the final state stop adding up. In-memory providers don't take locks or check rowversion, so run this against SQL Server (a Docker container is enough).

Which one, when?

Situation	Choose
A simple debit or credit that one UPDATE can express	Atomic conditional update
Several fields change under rules computed in C#	Optimistic (rowversion) + retry
Retrying is expensive or has side effects (calling a gateway, sending a message)	Pessimistic lock, kept short
High contention on one hot row	Atomic update; avoid long-held locks

Pros and cons

Optimistic: no locks held while you think, which scales well when conflicts are rare. But you must handle the exception and retry, and under heavy contention retries pile up.

Atomic update: the simplest correct option, with a single round trip and nothing to retry. But it only fits rules SQL can express, and it bypasses EF Core's change tracking, so in-memory entities can be stale afterwards.

Pessimistic: easy to reason about, since the second request simply waits. But locks held too long hurt throughput and can deadlock if rows are locked in different orders. Keep the transaction tiny.

A note on balances

All three protect a balance column. In a ledger design, the balance is derived from recorded transactions, and concurrency control moves to "two debits can't both pass the funds check". The same three tools apply.

---

Building a wallet or payment flow and want the concurrency story checked? See how I can help.