Database Transactions & Concurrency: EF Core Production Patterns

Common Questions This Answers

• What isolation level should I use?
• How do I prevent concurrent updates from overwriting each other?
• When should I use transactions explicitly vs rely on SaveChanges?
• How do I handle DbUpdateConcurrencyException?
• Why am I getting deadlocks and how do I fix them?

Definition

Concurrency control ensures data integrity when multiple operations access the same data simultaneously. Database transactions group operations into atomic units that either fully complete or fully rollback. EF Core provides both automatic transaction handling and explicit concurrency tokens for conflict detection.

Terms Used

• ACID: Atomicity, Consistency, Isolation, Durability - transaction guarantees
• Isolation level: Degree of protection from other concurrent transactions
• Optimistic concurrency: Assume conflicts are rare, detect at save time
• Pessimistic concurrency: Lock data upfront, prevent conflicts
• RowVersion/Timestamp: Auto-incrementing token for conflict detection
• Dirty read: Reading uncommitted changes from another transaction
• Phantom read: Rows appearing/disappearing between reads in same transaction
• Deadlock: Two transactions each waiting for locks held by the other

Reader Contract

After reading this article, you will:

1. Choose appropriate isolation levels for your queries
2. Implement optimistic concurrency with RowVersion
3. Handle concurrency conflicts gracefully
4. Prevent and recover from deadlocks
5. Know when explicit transactions are necessary

Prerequisites: Basic EF Core knowledge. See EF Core Performance Mistakes for query optimization.

Time to implement: 1 hour for basic concurrency, 2-4 hours for comprehensive handling.

Quick Start (10 Minutes)

Add a RowVersion column to detect concurrent modifications:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }

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

Handle the concurrency exception:

public async Task<bool> UpdateStockAsync(int productId, int newStock, CancellationToken ct)
{
    var product = await db.Products.FindAsync([productId], ct);
    if (product is null) return false;

    product.Stock = newStock;

    try
    {
        await db.SaveChangesAsync(ct);
        return true;
    }
    catch (DbUpdateConcurrencyException)
    {
        // Another user modified this product - handle conflict
        return false;
    }
}

Transaction Fundamentals

ACID Properties

Every database transaction guarantees:

Property	Meaning
Atomicity	All operations succeed or all rollback
Consistency	Database moves from valid state to valid state
Isolation	Concurrent transactions don't interfere
Durability	Committed changes survive system failures

Default EF Core Behavior

SaveChanges() automatically wraps all changes in a transaction:

// These three operations are atomic - all succeed or all rollback
db.Products.Add(new Product { Name = "Widget" });
db.Categories.Add(new Category { Name = "Tools" });
db.Inventory.Add(new Inventory { ProductId = 1, Quantity = 100 });

await db.SaveChangesAsync(ct);  // Single transaction

For most applications, this default behavior is sufficient. You only need explicit transactions when coordinating operations across multiple SaveChanges calls or mixing EF Core with raw SQL.

Isolation Levels Explained

Isolation levels control what concurrent transactions can see. Higher isolation means more protection but less concurrency.

The Concurrency Problems

Problem	Description
Dirty read	Read uncommitted data from another transaction
Non-repeatable read	Same query returns different values within transaction
Phantom read	Rows appear/disappear between queries in same transaction

Isolation Level Comparison

Level	Dirty Reads	Non-Repeatable	Phantoms	Concurrency
READ UNCOMMITTED	Allowed	Allowed	Allowed	Highest
READ COMMITTED	Prevented	Allowed	Allowed	High
REPEATABLE READ	Prevented	Prevented	Allowed	Medium
SNAPSHOT	Prevented	Prevented	Prevented	High
SERIALIZABLE	Prevented	Prevented	Prevented	Lowest

When to Use Each Level

READ COMMITTED (Default): Use for most OLTP operations. Good balance of consistency and concurrency.

// Default - no configuration needed
await db.SaveChangesAsync(ct);

READ UNCOMMITTED: Only for reporting queries where dirty reads are acceptable.

await using var transaction = await db.Database
    .BeginTransactionAsync(IsolationLevel.ReadUncommitted, ct);

var report = await db.Orders
    .Where(o => o.Date >= startDate)
    .SumAsync(o => o.Total, ct);

await transaction.CommitAsync(ct);

SNAPSHOT: High concurrency without blocking, but requires database configuration.

-- Enable on database first (one-time setup)
ALTER DATABASE YourDb SET ALLOW_SNAPSHOT_ISOLATION ON;

await using var transaction = await db.Database
    .BeginTransactionAsync(IsolationLevel.Snapshot, ct);

SERIALIZABLE: Only when absolute consistency is critical. Causes significant blocking.

// Use sparingly - causes range locks
await using var transaction = await db.Database
    .BeginTransactionAsync(IsolationLevel.Serializable, ct);

Azure SQL Default

Azure SQL Database enables READ_COMMITTED_SNAPSHOT by default, providing better concurrency than traditional READ COMMITTED by using row versioning instead of locks.

EF Core Transaction API

Explicit Transactions

When you need to coordinate multiple SaveChanges calls:

await using var transaction = await db.Database.BeginTransactionAsync(ct);

try
{
    // First operation
    var order = new Order { CustomerId = customerId };
    db.Orders.Add(order);
    await db.SaveChangesAsync(ct);

    // Second operation depends on first
    foreach (var item in cartItems)
    {
        db.OrderItems.Add(new OrderItem
        {
            OrderId = order.Id,
            ProductId = item.ProductId,
            Quantity = item.Quantity
        });
    }
    await db.SaveChangesAsync(ct);

    // Update inventory
    foreach (var item in cartItems)
    {
        await db.Products
            .Where(p => p.Id == item.ProductId)
            .ExecuteUpdateAsync(s => s
                .SetProperty(p => p.Stock, p => p.Stock - item.Quantity), ct);
    }

    await transaction.CommitAsync(ct);
}
catch
{
    // Transaction auto-rolls back on exception
    throw;
}

Savepoints

EF Core creates savepoints automatically within transactions:

await using var transaction = await db.Database.BeginTransactionAsync(ct);

db.Products.Add(product1);
await db.SaveChangesAsync(ct);  // Creates savepoint

try
{
    db.Products.Add(product2);
    await db.SaveChangesAsync(ct);  // Creates another savepoint
}
catch (DbUpdateException)
{
    // Rolls back to previous savepoint, product1 still pending
}

await transaction.CommitAsync(ct);  // Commits product1

Warning: Savepoints don't work with SQL Server MARS (Multiple Active Result Sets). If using MARS, disable it or manage transactions carefully.

Cross-Context Transactions

Share a transaction between multiple DbContext instances:

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync(ct);
await using var transaction = await connection.BeginTransactionAsync(ct);

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseSqlServer(connection)
    .Options;

await using var context1 = new AppDbContext(options);
await context1.Database.UseTransactionAsync(transaction, ct);

await using var context2 = new AppDbContext(options);
await context2.Database.UseTransactionAsync(transaction, ct);

// Both contexts share the same transaction
context1.Products.Add(product);
context2.Inventory.Add(inventory);

await context1.SaveChangesAsync(ct);
await context2.SaveChangesAsync(ct);

await transaction.CommitAsync(ct);

Optimistic Concurrency

Optimistic concurrency assumes conflicts are rare. Instead of locking data, it detects conflicts at save time.

RowVersion (SQL Server)

SQL Server's rowversion type auto-increments on every row modification:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }

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

// Or with Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .Property(p => p.RowVersion)
        .IsRowVersion();
}

EF Core generates UPDATE statements that include the RowVersion:

UPDATE Products
SET Name = @p0, Price = @p1
WHERE Id = @p2 AND RowVersion = @p3

If RowVersion changed (another user modified the row), zero rows are affected and DbUpdateConcurrencyException is thrown.

ConcurrencyCheck (Any Database)

For databases without rowversion, use application-managed tokens:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";

    [ConcurrencyCheck]
    public Guid Version { get; set; }
}

You must update the token manually:

product.Name = "New Name";
product.Version = Guid.NewGuid();  // Must update manually
await db.SaveChangesAsync(ct);

Protecting Specific Properties

Use ConcurrencyCheck on critical fields without a dedicated token:

public class Account
{
    public int Id { get; set; }

    [ConcurrencyCheck]
    public decimal Balance { get; set; }  // Detect balance changes
}

Handling DbUpdateConcurrencyException

When a concurrency conflict occurs, choose a resolution strategy.

Client Wins (Force Overwrite)

Discard the database changes, apply client changes:

public async Task<bool> UpdateProductAsync(Product product, CancellationToken ct)
{
    try
    {
        await db.SaveChangesAsync(ct);
        return true;
    }
    catch (DbUpdateConcurrencyException ex)
    {
        foreach (var entry in ex.Entries)
        {
            // Reload original values from database
            var databaseValues = await entry.GetDatabaseValuesAsync(ct);
            if (databaseValues is null)
            {
                // Entity was deleted
                return false;
            }

            // Update original values to bypass concurrency check
            entry.OriginalValues.SetValues(databaseValues);
        }

        // Retry save with refreshed token
        await db.SaveChangesAsync(ct);
        return true;
    }
}

Database Wins (Discard Client Changes)

Reload entity from database, losing client modifications:

catch (DbUpdateConcurrencyException ex)
{
    foreach (var entry in ex.Entries)
    {
        // Reload entity completely from database
        await entry.ReloadAsync(ct);
    }

    // Entity now has database values - inform user
    return false;
}

Merge (Business Logic Decides)

Compare values and apply business rules:

catch (DbUpdateConcurrencyException ex)
{
    foreach (var entry in ex.Entries)
    {
        if (entry.Entity is Product product)
        {
            var proposed = entry.CurrentValues;
            var database = await entry.GetDatabaseValuesAsync(ct);

            if (database is null)
            {
                throw new InvalidOperationException("Product was deleted");
            }

            // Merge strategy: keep higher stock value
            var proposedStock = proposed.GetValue<int>(nameof(Product.Stock));
            var databaseStock = database.GetValue<int>(nameof(Product.Stock));

            proposed.SetValues(database);  // Start with database values
            proposed[nameof(Product.Stock)] = Math.Max(proposedStock, databaseStock);

            entry.OriginalValues.SetValues(database);
        }
    }

    await db.SaveChangesAsync(ct);
}

Retry with User Notification

For web applications, show the user both versions:

public record ConflictResult<T>(T CurrentValues, T DatabaseValues);

public async Task<OneOf<T, ConflictResult<T>>> UpdateAsync<T>(
    T entity,
    CancellationToken ct) where T : class
{
    try
    {
        await db.SaveChangesAsync(ct);
        return entity;
    }
    catch (DbUpdateConcurrencyException ex)
    {
        var entry = ex.Entries.Single();
        var databaseValues = await entry.GetDatabaseValuesAsync(ct);

        if (databaseValues is null)
        {
            throw new InvalidOperationException("Entity was deleted");
        }

        var databaseEntity = (T)databaseValues.ToObject();
        return new ConflictResult<T>(entity, databaseEntity);
    }
}

Deadlock Prevention

Deadlocks occur when two transactions each wait for locks held by the other.

Consistent Lock Order

Always access tables in the same order:

// Good: Always Products then Inventory
await using var transaction = await db.Database.BeginTransactionAsync(ct);

var product = await db.Products.FindAsync([productId], ct);
var inventory = await db.Inventory
    .FirstOrDefaultAsync(i => i.ProductId == productId, ct);

// Bad: Random order based on business logic
// This causes deadlocks when concurrent transactions access in opposite order

Short Transactions

Keep transactions as brief as possible:

// Bad: Long transaction with external call
await using var transaction = await db.Database.BeginTransactionAsync(ct);
var order = await CreateOrderAsync(ct);
await ExternalPaymentService.ChargeAsync(order.Total);  // Slow!
await db.SaveChangesAsync(ct);
await transaction.CommitAsync(ct);

// Good: External call outside transaction
var paymentResult = await ExternalPaymentService.ChargeAsync(total);
if (!paymentResult.Success) return;

await using var transaction = await db.Database.BeginTransactionAsync(ct);
await CreateOrderAsync(paymentResult.TransactionId, ct);
await transaction.CommitAsync(ct);

Optimistic Over Pessimistic

Prefer optimistic concurrency (RowVersion) over pessimistic locking (UPDLOCK hints):

// Avoid: Pessimistic lock holds resources
var product = await db.Products
    .FromSqlRaw("SELECT * FROM Products WITH (UPDLOCK) WHERE Id = {0}", id)
    .FirstOrDefaultAsync(ct);

// Prefer: Optimistic concurrency
var product = await db.Products.FindAsync([id], ct);
// RowVersion protects against conflicts

Retry Strategies

Simple Retry for Deadlocks

SQL Server deadlocks return error 1205. Retry with exponential backoff:

public async Task<T> ExecuteWithRetryAsync<T>(
    Func<CancellationToken, Task<T>> operation,
    CancellationToken ct,
    int maxRetries = 3)
{
    var delay = TimeSpan.FromMilliseconds(100);

    for (var attempt = 1; attempt <= maxRetries; attempt++)
    {
        try
        {
            return await operation(ct);
        }
        catch (SqlException ex) when (ex.Number == 1205 && attempt < maxRetries)
        {
            // Deadlock - retry with backoff
            await Task.Delay(delay, ct);
            delay *= 2;  // Exponential backoff
        }
    }

    throw new InvalidOperationException("Max retries exceeded");
}

EF Core Execution Strategy

Configure automatic retry for transient failures:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString, sql =>
        sql.EnableRetryOnFailure(
            maxRetryCount: 3,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: [1205])));  // Include deadlocks

Warning: Execution strategies are incompatible with manual transactions. Wrap manual transactions in the execution strategy:

var strategy = db.Database.CreateExecutionStrategy();

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

    // Operations here
    await db.SaveChangesAsync(ct);
    await transaction.CommitAsync(ct);
});

Decision Framework

Scenario	Solution
Simple CRUD	Default SaveChanges (auto-transaction)
Multiple SaveChanges needed	Explicit transaction
Concurrent entity updates	RowVersion + handle exception
High-concurrency reads	READ COMMITTED or SNAPSHOT
Financial transactions	SERIALIZABLE + retry strategy
Reporting queries	READ UNCOMMITTED acceptable
Cross-service coordination	Saga pattern (out of scope)

Copy/Paste Artifact: Production Concurrency Handler

public class ConcurrencyHandler<TContext>(TContext db, ILogger logger)
    where TContext : DbContext
{
    public async Task<ConcurrencyResult<T>> SaveWithConcurrencyAsync<T>(
        T entity,
        CancellationToken ct) where T : class
    {
        const int maxRetries = 3;
        var delay = TimeSpan.FromMilliseconds(50);

        for (var attempt = 1; attempt <= maxRetries; attempt++)
        {
            try
            {
                await db.SaveChangesAsync(ct);
                return ConcurrencyResult<T>.Success(entity);
            }
            catch (DbUpdateConcurrencyException ex)
            {
                var entry = ex.Entries.SingleOrDefault(e => e.Entity is T);
                if (entry is null)
                {
                    throw;
                }

                var databaseValues = await entry.GetDatabaseValuesAsync(ct);
                if (databaseValues is null)
                {
                    return ConcurrencyResult<T>.Deleted();
                }

                if (attempt == maxRetries)
                {
                    var dbEntity = (T)databaseValues.ToObject();
                    return ConcurrencyResult<T>.Conflict(entity, dbEntity);
                }

                // Retry with database values as baseline
                entry.OriginalValues.SetValues(databaseValues);
                await Task.Delay(delay, ct);
                delay *= 2;
            }
            catch (SqlException ex) when (ex.Number == 1205 && attempt < maxRetries)
            {
                logger.LogWarning("Deadlock detected, retry {Attempt}/{Max}", attempt, maxRetries);
                await Task.Delay(delay, ct);
                delay *= 2;
            }
        }

        throw new InvalidOperationException("Unexpected state");
    }
}

public record ConcurrencyResult<T>
{
    public bool IsSuccess { get; init; }
    public bool WasDeleted { get; init; }
    public bool HasConflict { get; init; }
    public T? Entity { get; init; }
    public T? DatabaseEntity { get; init; }

    public static ConcurrencyResult<T> Success(T entity) =>
        new() { IsSuccess = true, Entity = entity };

    public static ConcurrencyResult<T> Deleted() =>
        new() { WasDeleted = true };

    public static ConcurrencyResult<T> Conflict(T current, T database) =>
        new() { HasConflict = true, Entity = current, DatabaseEntity = database };
}

Common Failure Modes

Lost Updates

Two users read entity, both modify, second write overwrites first:

User A reads Product (Stock = 100)
User B reads Product (Stock = 100)
User A writes Product (Stock = 90)   -- 10 sold
User B writes Product (Stock = 95)   -- 5 sold, but overwrites A's change!

Solution: Always use RowVersion on entities that multiple users can modify.

Transaction Timeout

Long transactions exceed database timeout:

Solution: Keep transactions short, move external calls outside transactions.

Isolation Level Mismatch

Different parts of code use different isolation levels inconsistently:

Solution: Establish team conventions, default to READ COMMITTED, document exceptions.

Retry Without Idempotency

Retrying a non-idempotent operation causes duplicate effects:

// Dangerous: Retry may insert duplicate
await retryPolicy.ExecuteAsync(() => CreateOrderAsync());

// Safe: Check for existence or use idempotency key
await retryPolicy.ExecuteAsync(() => CreateOrderIfNotExistsAsync(idempotencyKey));

Checklist

Before deploying concurrency-sensitive code:

• Entities with concurrent updates have RowVersion
• DbUpdateConcurrencyException handled appropriately
• Transactions kept short (no external calls inside)
• Consistent table access order to prevent deadlocks
• Retry strategy configured for transient failures
• Isolation level appropriate for use case
• Retry operations are idempotent
• Deadlock error (1205) logged and monitored

FAQ

Should I use transactions for every operation?

No. SaveChanges already wraps changes in a transaction. Use explicit transactions only when coordinating multiple SaveChanges calls or mixing with raw SQL.

RowVersion vs ConcurrencyCheck?

Use RowVersion (Timestamp) for SQL Server - it auto-updates. Use ConcurrencyCheck for other databases or when you need manual control.

When is SERIALIZABLE appropriate?

Rarely. Use only when you need absolute consistency and can accept significant performance impact. Financial calculations or inventory adjustments might justify it.

How do I test concurrency handling?

Use parallel tasks in integration tests:

var tasks = Enumerable.Range(0, 10)
    .Select(_ => UpdateProductAsync(productId, ct));
await Task.WhenAll(tasks);
// Verify only expected number succeeded

What about distributed transactions?

EF Core supports System.Transactions for distributed transactions, but they're complex and have limited support. Consider saga patterns for cross-service coordination instead.

What to do next

Audit your entities for concurrency tokens. Any entity that can be edited by multiple users or processes should have a RowVersion column.

For more on EF Core performance, read EF Core Performance Mistakes That Ship to Production.

If you want help reviewing transaction patterns in your codebase, reach out via Contact.

References

• Handling Concurrency Conflicts
• Using Transactions
• Transaction Isolation Levels
• Connection Resiliency
• Optimistic Concurrency Patterns
• SQL Server Deadlocks

Author notes

Decisions:

• Use optimistic concurrency with RowVersion for most scenarios. Rationale: scales better than pessimistic locking and handles conflicts gracefully.
• Prefer READ COMMITTED as the default isolation level. Rationale: balances consistency with concurrency for typical web application workloads.
• Implement retry logic for deadlock handling. Rationale: deadlocks are inevitable under load; graceful retry is better than failure.

Observations:

• Deadlocks often appear only under production load patterns, not in development.
• Missing RowVersion columns cause silent last-write-wins data corruption.
• Long-running transactions cause blocking cascades during peak traffic.