Transactions and Concurrency

Real-world analogy

What is it?

A transaction groups several database changes into one unit that either fully happens or does not happen at all. Concurrency is the other half: what happens when two requests touch the same row at the same moment. EF Core handles the simple case for you, because SaveChanges is already a transaction. For bigger flows you start one yourself. For conflicting edits you add a concurrency token, so the second writer is told instead of silently winning.

Real-world relevance

Money transfers, stock counts and seat bookings are where this bites. Two buyers click "buy" on the last ticket at the same moment, and without a concurrency token both succeed. Teams usually choose optimistic concurrency: it costs nothing when there is no conflict, and a conflict becomes an exception that the app turns into "this item changed, please review". Pessimistic locks are reserved for hot rows where retries would be constant.

Key points

• SaveChanges is one transaction — All the changes in one SaveChanges call succeed together or fail together. You only need your own transaction when you span several calls.
• Explicit transactions span saves — BeginTransactionAsync wraps several statements, and CommitAsync makes them permanent. If an exception skips the commit, disposing the transaction rolls everything back.
• ACID in four lines — Atomic means all or nothing, and Consistent means constraints still hold. Isolated means parallel work does not see half-done changes. Durable means committed data survives a crash.
• Isolation levels trade safety for speed — Read Uncommitted may read changes not yet committed (PostgreSQL treats it as Read Committed). Read Committed sees only committed data. Repeatable Read keeps rows you read stable; Serializable acts as if transactions ran one at a time.
• Read Committed is the usual default — It is the default in SQL Server and PostgreSQL, and a second read can still differ. SQL Server readers may wait for writers' locks unless READ_COMMITTED_SNAPSHOT is on; PostgreSQL readers do not.
• Lost update problem — Two users read balance 100, one saves 80, the other saves 70. The second write replaces the first without knowing it existed.
• Optimistic concurrency uses a token — In SQL Server, a [Timestamp] byte array maps to rowversion, and EF Core adds it to the UPDATE condition. PostgreSQL uses its xmin system column instead. If the version changed, no row matches and you get a DbUpdateConcurrencyException.
• Pessimistic locking blocks others — You lock the row inside a transaction while you work: UPDLOCK in SQL Server, FOR UPDATE in PostgreSQL. EF Core has no LINQ method for it, so you use raw SQL. Use it when conflicts are frequent and retries are costly.
• Keep transactions short — An open transaction holds locks and a connection. Do not call a slow API or wait for a user inside one.

Code example

public class Account
{
    public int Id { get; set; }
    public decimal Balance { get; set; }
    [Timestamp] public byte[] RowVersion { get; set; } = null!;   // SQL Server rowversion
}

public async Task TransferAsync(ShopContext db, int fromId, int toId, decimal amount, CancellationToken ct)
{
    await using var tx = await db.Database.BeginTransactionAsync(ct);

    var debited = await db.Accounts
        .Where(a => a.Id == fromId && a.Balance >= amount)
        .ExecuteUpdateAsync(s => s.SetProperty(a => a.Balance, a => a.Balance - amount), ct);
    if (debited == 0) throw new InvalidOperationException("Not enough money");

    await db.Accounts
        .Where(a => a.Id == toId)
        .ExecuteUpdateAsync(s => s.SetProperty(a => a.Balance, a => a.Balance + amount), ct);

    await tx.CommitAsync(ct);   // both changes become visible together
}

public async Task<bool> TryDepositAsync(ShopContext db, Account account, decimal amount, CancellationToken ct)
{
    account.Balance += amount;
    try { await db.SaveChangesAsync(ct); return true; }
    catch (DbUpdateConcurrencyException) { return false; }   // someone else changed the row first
}

Line-by-line walkthrough

1. 1. Account has a RowVersion marked [Timestamp]. In SQL Server the database changes it on every update.
2. 2. TransferAsync opens a transaction first, so everything after it stands or falls together.
3. 3. The first ExecuteUpdateAsync takes money only if the balance is enough, and returns how many rows it changed.
4. 4. If it changed 0 rows, we throw. Disposing the transaction rolls it back, so nothing is saved.
5. 5. The second ExecuteUpdateAsync adds the money to the other account. CommitAsync then makes both changes permanent.
6. 6. TryDepositAsync saves a tracked entity. If the RowVersion no longer matches, the exception tells us someone else won.

Spot the bug

public async Task PlaceOrderAsync(ShopContext db, Order order, int productId, int qty)
{
    db.Orders.Add(order);
    await db.SaveChangesAsync();

    // Stock has a [Timestamp] token, so a clash also throws below
    var stock = await db.Stock.FirstAsync(s => s.ProductId == productId);
    stock.Quantity -= qty;   // may violate a CHECK (Quantity >= 0) constraint
    await db.SaveChangesAsync();
}

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• Transactions in EF Core (Microsoft Learn)
• Handling concurrency conflicts in EF Core (Microsoft Learn)
• PostgreSQL: Transaction Isolation (PostgreSQL Documentation)