EF Core — Concurrency & Transactions

Key Points

• Optimistic concurrency via row-version ([Timestamp] on a byte[] property) — most common. EF Core throws DbUpdateConcurrencyException on conflict.
• SaveChanges is implicitly transactional — all entity changes commit or roll back together within a single SaveChanges call.
• db.Database.BeginTransactionAsync() for explicit transactions spanning multiple SaveChanges or external work.
• Avoid TransactionScope in modern code unless you really need ambient/distributed transactions — and remember that distributed (MSDTC) transactions are limited.
• EF Core 7+ IDbContextTransaction integrates natively; share across contexts with UseTransaction.
• Retry on transient failure — built-in via EnableRetryOnFailure() for SQL Server / Azure SQL.

Concepts (deep dive)

Optimistic concurrency with row version

public class Order
{
    public int Id { get; set; }
    public OrderStatus Status { get; set; }

    [Timestamp]
    public byte[]? RowVersion { get; set; }
}

SQL Server: [Timestamp] maps to rowversion column — auto-incremented on every UPDATE. Postgres: use [ConcurrencyCheck] + manual increment, or xmin system column via fluent config.

EF generates UPDATE WHERE Id = X AND RowVersion = Y. If 0 rows affected, throws DbUpdateConcurrencyException.

Handling conflicts

try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    foreach (var entry in ex.Entries)
    {
        var current = await entry.GetDatabaseValuesAsync();
        if (current is null)
        {
            // Row was deleted by another user
            entry.State = EntityState.Detached;
        }
        else
        {
            // Decide: client-wins, server-wins, or ask user
            entry.OriginalValues.SetValues(current);
            // re-attempt SaveChanges
        }
    }
}

Strategies: - Client wins: overwrite database with our values. entry.OriginalValues.SetValues(current) then SaveChanges. - Database wins: discard our changes. entry.Reload(). - Last-write-wins (no concurrency check): don't add [Timestamp]. Risky. - Ask user: present diff; let user resolve.

Implicit transactions in SaveChanges

db.Orders.Add(order);
db.OrderLines.AddRange(lines);
await db.SaveChangesAsync();
// Single transaction; all-or-nothing

EF wraps SaveChanges in a transaction automatically. Multiple inserts/updates commit together.

Explicit transactions

await using var tx = await db.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted, ct);
try
{
    db.Orders.Add(order);
    await db.SaveChangesAsync(ct);

    db.Audits.Add(new Audit { ... });
    await db.SaveChangesAsync(ct);

    await tx.CommitAsync(ct);
}
catch
{
    await tx.RollbackAsync(ct);
    throw;
}

Explicit transactions span multiple SaveChanges calls or include raw SQL operations.

Sharing transaction across contexts

await using var tx = await db1.Database.BeginTransactionAsync();
await db2.Database.UseTransactionAsync(tx.GetDbTransaction());
// Now db1 and db2 SaveChanges enroll in the same transaction

Useful when a request crosses bounded contexts but stays in one database.

Transactions across multiple databases (MSDTC)

using var scope = new TransactionScope();
await db1.SaveChangesAsync();   // database A
await db2.SaveChangesAsync();   // database B
scope.Complete();

TransactionScope enrolls participants automatically. Two-phase commit (2PC) escalates to MSDTC — slow, complex, often unsupported in cloud (Azure SQL doesn't support DTC for all scenarios).

Modern alternative: outbox pattern. Save state + event in one DB transaction; publish via separate process. See Outbox & Inbox Patterns.

Isolation levels

await db.Database.BeginTransactionAsync(IsolationLevel.Serializable, ct);

For most apps: ReadCommitted (default) is right. Snapshot for read-heavy workloads in SQL Server (requires DB option).

Retry on transient failures

opt.UseSqlServer(connStr, sql =>
    sql.EnableRetryOnFailure(
        maxRetryCount: 5,
        maxRetryDelay: TimeSpan.FromSeconds(30),
        errorNumbersToAdd: null));

Wraps queries with retry logic for transient errors (network blip, deadlock, throttling). Caveat: doesn't compose with explicit transactions automatically — wrap manually:

var strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    await using var tx = await db.Database.BeginTransactionAsync();
    /* ... */
    await tx.CommitAsync();
});

ExecutionStrategy ensures the entire block (including transaction) gets retried as a unit.

Deadlocks

When two transactions hold locks the other needs, the database picks a victim and rolls it back. Symptoms: - DbUpdateException with SQL error 1205 (SQL Server). - Random failures under load.

Mitigations: 1. Consistent lock ordering — always acquire locks in the same order across transactions. 2. Smaller transactions — narrower critical section. 3. Snapshot isolation — readers don't block writers (SQL Server requires DB option). 4. Retry — EnableRetryOnFailure includes deadlock by default.

See SQL Server Internals for App Devs.

Code: correct vs wrong

❌ Wrong: no concurrency token

public class Order { public int Id; public string Status; }   // last writer wins silently

✅ Correct: row version

public class Order
{
    public int Id { get; set; }
    public string Status { get; set; }
    [Timestamp] public byte[]? RowVersion { get; set; }
}

❌ Wrong: TransactionScope for cross-DB

using var scope = new TransactionScope();
await sql1.SaveChangesAsync();
await sql2.SaveChangesAsync();
scope.Complete();   // ❌ MSDTC; often unsupported in cloud

✅ Correct: outbox pattern

Write to one DB transactionally; replay events to another DB asynchronously.

❌ Wrong: catching exception, swallowing rollback

try { await db.SaveChangesAsync(); }
catch { return Error(); }   // transaction state? next call?

✅ Correct: re-throw or handle explicitly

try { await db.SaveChangesAsync(); }
catch (DbUpdateConcurrencyException) { return Conflict(); }
catch (Exception ex)
{
    Log(ex);
    throw;
}

Design patterns for this topic

Pattern 1 — "[Timestamp] row version on every entity"

• Intent: optimistic concurrency by default.

Pattern 2 — "ExecutionStrategy + explicit transaction"

• Intent: retry-safe transactions.

Pattern 3 — "Outbox over distributed transactions"

• Intent: avoid 2PC; use eventual consistency.

Pattern 4 — "Reload on conflict for CRUD UIs"

• Intent: show fresh data; let user re-edit.

Pattern 5 — "Isolation level explicit per scenario"

• Intent: known semantics; avoid relying on default.

Pros & cons / trade-offs

When to use / when to avoid

• Use [Timestamp] on every entity that's user-editable.
• Use explicit transactions when crossing multiple SaveChanges.
• Avoid TransactionScope cross-DB — use outbox.
• Avoid Serializable unless needed — locking cost.
• Avoid swallowing concurrency exceptions — surface to caller / user.

Interview Q&A

Q1. Optimistic vs pessimistic concurrency? Optimistic: assume no conflict; check on save. Pessimistic: lock the row immediately. Optimistic scales better but pushes conflict to the caller.

Q2. What's [Timestamp]? EF maps to rowversion (SQL Server) — auto-incremented on UPDATE. EF generates WHERE Id=X AND RowVersion=Y; conflict throws DbUpdateConcurrencyException.

Q3. Is SaveChanges transactional? Yes — implicitly. All changes within a SaveChanges call commit or roll back together.

Q4. When use explicit transactions? Spanning multiple SaveChanges or mixing EF with raw SQL.

Q5. What's wrong with TransactionScope for cross-DB? Escalates to MSDTC (two-phase commit). Slow, complex, often unsupported in cloud. Use outbox instead.

Q6. What's EnableRetryOnFailure? Configures the ExecutionStrategy to retry on transient errors (deadlock, network blip).

Q7. Why must you wrap explicit transactions in ExecutionStrategy.ExecuteAsync? Because retry rolls back; the entire transaction block must be the retry unit.

Q8. What's snapshot isolation? Readers see a consistent snapshot from transaction start; no read locks. Writers may conflict at commit. Requires DB-level option in SQL Server.

Q9. How do you handle a DbUpdateConcurrencyException? Read database values; choose strategy (client-wins, server-wins, ask user). Reload + retry SaveChanges.

Q10. Why is last-writer-wins dangerous? User A reads, User B reads, A saves, B saves overwriting A's changes — silent data loss.

Gotchas / common mistakes

• ⚠️ No [Timestamp] on user-editable entities — silent data loss.
• ⚠️ TransactionScope cross-DB — MSDTC headaches.
• ⚠️ Forgetting ExecutionStrategy with explicit transactions and retry — partial retries.
• ⚠️ Catching DbUpdateException generically — masks distinct concurrency exception.
• ⚠️ Long transactions — block other writers; deadlock risk.

Further reading

• Concurrency in EF Core
• Transactions
• Connection resiliency
• Outbox pattern