Optimistic vs. Pessimistic Locking in PostgreSQL and EF Core: Eliminating Lost Updates and Deadlocks

Every engineering team building transactional systems eventually confronts the reality of concurrent write operations. Whether managing limited inventory during a flash sale, processing high-volume financial ledger transfers, or orchestrating multi-user document revisions, multiple incoming requests inevitably attempt to read, validate, and mutate the exact same row simultaneously.

Under PostgreSQL default READ COMMITTED transaction isolation, concurrent read-modify-write cycles suffer from the classic lost update anomaly: Transaction B commits its changes milliseconds after Transaction A, silently overwriting Transaction A's modifications without throwing any database-level error. To protect transactional integrity, developers must choose between Optimistic Concurrency Control (OCC) and Pessimistic Concurrency Control (PCC). Picking the wrong strategy, or bungling its implementation in Entity Framework Core, leads either to debilitating connection pool exhaustion and deadlocks under pessimistic locks, or catastrophic retry storms under optimistic contention.

Key Takeaways

• Optimistic Concurrency Control (OCC) relies on PostgreSQL native xmin system column to detect tuple mutations at commit time with zero row-locking overhead and zero connection pool stalls.
• Pessimistic Concurrency Control (PCC) serializes execution using explicit SELECT ... FOR UPDATE row locks, guaranteeing first-to-lock victory but risking connection starvation and deadlocks if held across network hops.
• In EF Core 9, configuring builder.Property(x => x.Version).IsRowVersion() maps directly to PostgreSQL 32-bit xmin shadow column without requiring custom schema columns or application-side timestamp updates.
• Cyclic deadlocks (SQLSTATE 40P01) occur when transactions acquire multiple locks in arbitrary order; sorting primary keys ascending before locking establishes a Directed Acyclic Graph (DAG) that mathematically eliminates cyclic wait graphs.
• High-contention flash sales with single-key write bottlenecks favor Pessimistic Locking with NOWAIT or atomic SQL decrements, whereas read-heavy catalogs with sporadic concurrent writes favor Optimistic Locking backed by Polly v8 decorrelated jitter.

The Concurrency Dilemma and Lost Update Anomaly in PostgreSQL

The lost update anomaly occurs when two concurrent transactions read the identical database state, compute independent modifications based on that snapshot, and write their results sequentially, causing the second commit to silently overwrite the first transaction's modifications.

Consider an e-commerce inventory table with a single product record: SKU SKU-PRO-42 has AvailableStock = 10. Two buyers simultaneously place an order for 2 units each.

1. Client A starts a transaction and reads AvailableStock = 10.
2. Client B starts an independent transaction and reads AvailableStock = 10.
3. Client A validates stock in memory (10 >= 2), computes AvailableStock = 8, and issues UPDATE "Inventory" SET "AvailableStock" = 8 WHERE "Id" = 42. Client A commits successfully.
4. Client B validates stock in memory (10 >= 2), computes AvailableStock = 8, and issues UPDATE "Inventory" SET "AvailableStock" = 8 WHERE "Id" = 42. Client B commits successfully.

The final stored stock is 8 instead of 6. Two items were sold, but the inventory count recorded the sale of only one. The update committed by Client A was silently lost. Because PostgreSQL executes each query under SQL Transactions Explained: Master the ACID Properties with statement-level read consistency, neither transaction violated its isolation rules, yet business data suffered silent corruption.

Solving this anomaly requires deciding where and how to coordinate isolation:

• Optimistic Concurrency Control (OCC) assumes conflicts are rare. Transactions execute without acquiring database locks during read and computation phases. At commit time, the application verifies whether another transaction modified the row since it was read. If a collision occurred, the commit aborts and the application retries.
• Pessimistic Concurrency Control (PCC) assumes conflicts are frequent. When a transaction reads a row with the intention to modify it, it explicitly locks the row in PostgreSQL using FOR UPDATE. Any other transaction attempting to read that row for update blocks in the database wait queue until the first transaction commits or rolls back.

Understanding the deep trade-offs between these two models requires analyzing how PostgreSQL implements row versioning internally.

Optimistic Concurrency Control: PostgreSQL MVCC and the Native xmin Token

Optimistic Concurrency Control is a non-blocking database design pattern where concurrent transactions read and update data without acquiring row-level locks, verifying at write time that no concurrent transaction modified the underlying record before committing.

PostgreSQL uses Multi-Version Concurrency Control (MVCC) as its core storage engine. Unlike legacy engines that acquire shared read locks, PostgreSQL never blocks readers with writers, and writers never block readers. Every physical table tuple (row version) on disk contains a standard 23-byte header containing two critical transaction identifiers:

• t_xmin: The 32-bit transaction identifier (xid) of the transaction that inserted this tuple version.
• t_xmax: The transaction identifier of the transaction that updated or deleted this tuple version (set to 0 for active live rows).

When an UPDATE executes in PostgreSQL, the engine does not overwrite the existing bytes on the disk page. Instead, it marks the current tuple as dead by setting its xmax to the current transaction ID, writes an entirely new tuple to the heap page, and records the new transaction ID in the new tuple's xmin header.

Configuring the Native xmin Shadow Column in EF Core 9

Because every tuple already possesses an xmin system attribute, PostgreSQL provides built-in row versioning for free. Developers do not need to alter their database schema to add custom RowVersion byte[], ConcurrencyToken Guid, or UpdatedAt timestamp columns.

In .NET 9 with the official Npgsql PostgreSQL Concurrency Tokens & xmin provider, you configure the native xmin system column as an automatic concurrency token directly in DbContext.OnModelCreating.

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

namespace CodeToClarity.Concurrency.Entities;

public sealed class InventoryItem
{
    public int Id { get; set; }
    
    [Required]
    [MaxLength(64)]
    public string Sku { get; set; } = string.Empty;
    
    public int AvailableStock { get; set; }
    
    public decimal UnitPrice { get; set; }
    
    // Maps to PostgreSQL native xmin shadow system column
    public uint Version { get; set; }
}

public sealed class CodeToClarityInventoryContext : DbContext
{
    public CodeToClarityInventoryContext(DbContextOptions<CodeToClarityInventoryContext> options)
        : base(options)
    {
    }

    public DbSet<InventoryItem> Inventory => Set<InventoryItem>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<InventoryItem>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.HasIndex(e => e.Sku).IsUnique();

            // Configure PostgreSQL xmin system column as concurrency token
            entity.Property(e => e.Version)
                  .IsRowVersion();
        });
    }
}

When EF Core executes SaveChangesAsync(), it generates parameterized SQL that compares the expected xmin against the live record in the table:

UPDATE "Inventory" 
SET "AvailableStock" = @p0 
WHERE "Id" = @p1 AND "xmin" = @p2;

If another transaction committed an update to this row in the interim, PostgreSQL updated the tuple's xmin to that transaction's ID. As a result, the WHERE "xmin" = @p2 predicate matches zero rows. PostgreSQL reports an execution result of 0 rows affected.

EF Core inspects the affected row count returned by Npgsql. Because 1 row was expected and 0 rows were modified, EF Core immediately throws a DbUpdateConcurrencyException. The lost update is completely prevented.

Resolving DbUpdateConcurrencyException with Polly v8 Decorrelated Jitter

Detecting a concurrency violation is only half the battle. If ten concurrent threads collide and immediately retry in tight loops, they generate a retry storm. Every thread hammers PostgreSQL with rapid queries, continually invalidating each other's xmin tokens and exhausting CPU cycles.

To resolve optimistic conflicts reliably, the application must catch DbUpdateConcurrencyException, reload the fresh database values, merge business logic, and retry with exponential backoff and decorrelated jitter using The Circuit Breaker & Retry Patterns: Building Resilient Distributed Systems with Polly v8.

using CodeToClarity.Concurrency.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Polly;
using Polly.Retry;

namespace CodeToClarity.Concurrency.Services;

public sealed class CodeToClarityOptimisticInventoryService
{
    private readonly IDbContextFactory<CodeToClarityInventoryContext> _contextFactory;
    private readonly ILogger<CodeToClarityOptimisticInventoryService> _logger;
    private readonly ResiliencePipeline _resiliencePipeline;

    public CodeToClarityOptimisticInventoryService(
        IDbContextFactory<CodeToClarityInventoryContext> contextFactory,
        ILogger<CodeToClarityOptimisticInventoryService> logger)
    {
        _contextFactory = contextFactory;
        _logger = logger;

        // Configure Polly v8 resilient retry pipeline with decorrelated jitter
        _resiliencePipeline = new ResiliencePipelineBuilder()
            .AddRetry(new RetryStrategyOptions
            {
                ShouldHandle = new PredicateBuilder().Handle<DbUpdateConcurrencyException>(),
                MaxRetryAttempts = 4,
                Delay = TimeSpan.FromMilliseconds(25),
                MaxDelay = TimeSpan.FromMilliseconds(200),
                BackoffType = DelayBackoffType.Exponential,
                UseJitter = true,
                OnRetry = args =>
                {
                    _logger.LogWarning(
                        "Concurrency collision detected on attempt {Attempt}. Retrying in {Delay}ms...",
                        args.AttemptNumber,
                        args.RetryDelay.TotalMilliseconds);
                    return ValueTask.CompletedTask;
                }
            })
            .Build();
    }

    public async Task<bool> DecrementStockAsync(int itemId, int quantityToDeduct, CancellationToken ct = default)
    {
        return await _resiliencePipeline.ExecuteAsync(async token =>
        {
            await using var context = await _contextFactory.CreateDbContextAsync(token);

            var item = await context.Inventory
                .SingleOrDefaultAsync(x => x.Id == itemId, token);

            if (item is null)
            {
                throw new KeyNotFoundException($"Inventory item with ID {itemId} not found.");
            }

            if (item.AvailableStock < quantityToDeduct)
            {
                _logger.LogInformation("Insufficient stock for SKU {Sku}. Available: {Stock}", item.Sku, item.AvailableStock);
                return false;
            }

            item.AvailableStock -= quantityToDeduct;

            try
            {
                await context.SaveChangesAsync(token);
                return true;
            }
            catch (DbUpdateConcurrencyException ex)
            {
                var entry = ex.Entries.Single();
                var proposedValues = entry.CurrentValues;
                var databaseValues = await entry.GetDatabaseValuesAsync(token);

                if (databaseValues is null)
                {
                    _logger.LogError("Inventory item {ItemId} was deleted by another transaction.", itemId);
                    throw new InvalidOperationException("Item was deleted concurrently.");
                }

                var currentDatabaseStock = (int)databaseValues[nameof(InventoryItem.AvailableStock)]!;
                _logger.LogWarning(
                    "Conflict detected for SKU {Sku}. In-memory stock was {StaleStock}, fresh DB stock is {DbStock}.",
                    item.Sku,
                    item.AvailableStock + quantityToDeduct,
                    currentDatabaseStock);

                // Re-throw to allow Polly pipeline to execute fresh read-modify-write iteration
                throw;
            }
        }, ct);
    }
}

Under low to moderate contention, this optimistic pattern delivers near-instantaneous execution. Reads never block writers, database transactions hold no persistent row locks, and application threads release database connections back to the connection pool immediately after completing small, targeted operations.

Pessimistic Concurrency Control: Explicit PostgreSQL Row-Level Locking

Pessimistic Concurrency Control is an exclusive resource reservation strategy where a transaction acquires explicit database row-level locks upon reading a record, blocking any concurrent transaction from modifying or reading that record for update until the locking transaction terminates.

PostgreSQL provides four distinct row-level locking clauses in the SQL standard, as detailed in the PostgreSQL Explicit Locking Documentation:

1. FOR UPDATE: The strongest row-level lock. Acquires an exclusive RowExclusiveLock on each selected tuple. Any other transaction attempting FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, or UPDATE/DELETE on the same row blocks until the holding transaction issues COMMIT or ROLLBACK.
2. FOR NO KEY UPDATE: Weaker than FOR UPDATE. Locks all columns except unique keys or primary keys. It blocks standard updates and deletes, but allows concurrent transactions to acquire FOR KEY SHARE locks (useful when child foreign key inserts need parent stability).
3. FOR SHARE: Acquires a shared lock. Multiple concurrent transactions can hold FOR SHARE on the same tuple simultaneously, preventing any transaction from updating or deleting the row, but allowing concurrent shared reads.
4. FOR KEY SHARE: The weakest lock. Permits concurrent FOR NO KEY UPDATE modifications, only blocking operations that mutate the primary key or unique index values.

SELECT "Id", "Sku", "AvailableStock", "UnitPrice"
FROM "Inventory"
WHERE "Id" = 42
FOR UPDATE;

When Transaction 1 issues this query inside an open transaction block, PostgreSQL tags the row tuple in memory and records the lock in the internal lock table (pg_locks). If Transaction 2 executes SELECT ... FOR UPDATE on row 42, PostgreSQL suspends Transaction 2's backend process. Transaction 2 enters a lock wait state.

Implementing FOR UPDATE in EF Core 9

While EF Core LINQ does not have a native cross-platform LINQ extension method for .ForUpdate(), EF Core 9 makes executing raw SQL and attaching tracked entities seamless via FromSqlInterpolated.

using CodeToClarity.Concurrency.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace CodeToClarity.Concurrency.Services;

public sealed class CodeToClarityPessimisticInventoryService
{
    private readonly CodeToClarityInventoryContext _context;
    private readonly ILogger<CodeToClarityPessimisticInventoryService> _logger;

    public CodeToClarityPessimisticInventoryService(
        CodeToClarityInventoryContext context,
        ILogger<CodeToClarityPessimisticInventoryService> logger)
    {
        _context = context;
        _logger = logger;
    }

    public async Task<bool> DeductStockPessimisticAsync(int itemId, int quantityToDeduct, CancellationToken ct = default)
    {
        // Explicit database transaction is mandatory when using row locks
        await using var transaction = await _context.Database.BeginTransactionAsync(ct);

        try
        {
            // Acquire exclusive row lock directly on the tuple in PostgreSQL
            var item = await _context.Inventory
                .FromSqlInterpolated($"SELECT * FROM \"Inventory\" WHERE \"Id\" = {itemId} FOR UPDATE")
                .SingleOrDefaultAsync(ct);

            if (item is null)
            {
                throw new KeyNotFoundException($"Inventory item {itemId} does not exist.");
            }

            if (item.AvailableStock < quantityToDeduct)
            {
                _logger.LogInformation("Stock exhausted for SKU {Sku}. Requested: {Qty}, In-Stock: {Stock}",
                    item.Sku, quantityToDeduct, item.AvailableStock);
                
                await transaction.RollbackAsync(ct);
                return false;
            }

            item.AvailableStock -= quantityToDeduct;
            await _context.SaveChangesAsync(ct);

            // Releasing the transaction releases the row lock in pg_locks
            await transaction.CommitAsync(ct);
            return true;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Transaction failed while deducting stock for item {ItemId}.", itemId);
            await transaction.RollbackAsync(ct);
            throw;
        }
    }
}

The Fail-Fast Paradigm: FOR UPDATE NOWAIT

The most severe flaw of standard FOR UPDATE in high-throughput APIs is synchronous blocking. If 200 requests arrive within 10 milliseconds competing for SKU 42, 199 database connections enter lock wait states. Each waiting connection occupies an active PostgreSQL worker process, holding open an active slot in PgBouncer and an active thread in the application server. This pattern causes catastrophic Database Connection Pool Starvation: Architecture, PostgreSQL Internals, and PgBouncer Tuning.

PostgreSQL provides the NOWAIT modifier to eliminate connection stalling. When a transaction requests FOR UPDATE NOWAIT, PostgreSQL attempts to acquire the row lock immediately. If another transaction holds a conflicting lock, PostgreSQL refuses to wait and immediately aborts the statement with an error:

ERROR: could not obtain lock on row in relation "Inventory"
SQLSTATE: 55P03 (lock_not_available)

In EF Core 9, you catch this PostgreSQL-specific exception via Npgsql to fail fast, notifying the caller in 1ms instead of stalling for seconds:

using Npgsql;

public async Task<bool> DeductStockFailFastAsync(int itemId, int quantity, CancellationToken ct = default)
{
    await using var tx = await _context.Database.BeginTransactionAsync(ct);

    try
    {
        var item = await _context.Inventory
            .FromSqlInterpolated($"SELECT * FROM \"Inventory\" WHERE \"Id\" = {itemId} FOR UPDATE NOWAIT")
            .SingleOrDefaultAsync(ct);

        if (item is null || item.AvailableStock < quantity)
        {
            await tx.RollbackAsync(ct);
            return false;
        }

        item.AvailableStock -= quantity;
        await _context.SaveChangesAsync(ct);
        await tx.CommitAsync(ct);
        return true;
    }
    catch (PostgresException ex) when (ex.SqlState == PostgresErrorCodes.LockNotAvailable) // 55P03
    {
        _logger.LogWarning("Row {ItemId} is currently locked by another checkout session. Failing fast.", itemId);
        await tx.RollbackAsync(ct);
        
        // Return 409 Conflict or 429 Too Many Requests to caller
        throw new ConcurrencyLockBusyException("Inventory resource is temporarily locked. Please retry.");
    }
}

High-Throughput Queue Processing: FOR UPDATE SKIP LOCKED

When building event dispatchers or background worker pools, neither blocking (FOR UPDATE) nor failing (NOWAIT) is desirable. Background workers should never wait on each other; they should simply process the next available unlocked item.

The SKIP LOCKED clause allows a transaction to read all rows matching the filter predicate while silently ignoring any row currently locked by another concurrent transaction. This primitive enables lock-free worker pools and forms the core foundation of The Transactional Outbox Pattern: Reliable Event Publishing.

SELECT * FROM "OutboxMessages"
WHERE "ProcessedAtUtc" IS NULL
ORDER BY "CreatedAtUtc" ASC
LIMIT 50
FOR UPDATE SKIP LOCKED;

With SKIP LOCKED, ten parallel background workers can poll the same outbox table simultaneously without any lock contention, lock wait timeouts, or duplicate message processing.

Deadlocks in PostgreSQL: Mechanics, Detection, and Prevention

A database deadlock is an unresolvable concurrency condition where two or more transactions form a cyclic dependency, each holding a lock on a resource that another transaction requires to proceed, preventing all participants from completing.

Consider two concurrent financial transfers involving Account A and Account B:

• Transaction 1 transfers $100 from Account A to Account B.
• Transaction 2 transfers $50 from Account B to Account A.

If the transactions execute without deterministic ordering:

1. Transaction 1 executes SELECT ... WHERE "Id" = 'AccountA' FOR UPDATE (granted).
2. Transaction 2 executes SELECT ... WHERE "Id" = 'AccountB' FOR UPDATE (granted).
3. Transaction 1 requests SELECT ... WHERE "Id" = 'AccountB' FOR UPDATE. It blocks, waiting for Transaction 2.
4. Transaction 2 requests SELECT ... WHERE "Id" = 'AccountA' FOR UPDATE. It blocks, waiting for Transaction 1.

Neither transaction can proceed. Neither will release its locks until it commits. The system is locked in an infinite cycle.

How PostgreSQL Detects Deadlocks

PostgreSQL does not check for deadlocks on every lock request because building and analyzing dependency graphs across all active backends is computationally expensive. Instead, PostgreSQL uses an internal timer defined by the deadlock_timeout configuration parameter (default: 1000ms).

1. When a transaction blocks waiting for a lock, PostgreSQL starts the deadlock_timeout countdown timer.
2. If the lock is granted before 1000ms elapses, the transaction proceeds normally with zero deadlock check overhead.
3. If 1000ms elapses and the transaction is still waiting, PostgreSQL wakes up the Lock Manager's cycle detection routine.
4. The Lock Manager traverses the wait-for graph across all processes recorded in pg_locks.
5. If a closed loop (directed cycle) is detected, PostgreSQL chooses one transaction as the "victim," aborts its command, rolls back its transaction, and returns the error:

ERROR: deadlock detected
DETAIL: Process 1403 waits for ShareLock on transaction 50201; blocked by process 1402.
Process 1402 waits for ShareLock on transaction 50202; blocked by process 1403.
HINT: See server log for query details.
CONTEXT: while locking tuple (0,14) in relation "Inventory"
SQLSTATE: 40P01 (deadlock_detected)

The victim's connection is aborted, allowing the remaining transaction to acquire the freed lock and complete. However, during the 1000ms detection interval, both application threads and database connections remained completely frozen.

Eliminating Deadlocks: Deterministic Resource Ordering

Deadlocks are not random acts of nature. They are architectural bugs caused by non-deterministic lock acquisition sequences. In computer science graph theory, a cycle cannot exist in a Directed Acyclic Graph (DAG). If all transactions acquire locks in the exact same deterministic order, cyclic waits are mathematically impossible.

The golden rule of pessimistic locking: Always sort resource identifiers in ascending order before acquiring locks.

using CodeToClarity.Concurrency.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace CodeToClarity.Concurrency.Services;

public sealed class CodeToClarityBatchTransferService
{
    private readonly CodeToClarityInventoryContext _context;
    private readonly ILogger<CodeToClarityBatchTransferService> _logger;

    public CodeToClarityBatchTransferService(
        CodeToClarityInventoryContext context,
        ILogger<CodeToClarityBatchTransferService> logger)
    {
        _context = context;
        _logger = logger;
    }

    public async Task TransferInventoryAsync(
        int sourceWarehouseItemId, 
        int destinationWarehouseItemId, 
        int quantity, 
        CancellationToken ct = default)
    {
        // Enforce deterministic global ordering: smaller ID first, larger ID second
        var firstId = Math.Min(sourceWarehouseItemId, destinationWarehouseItemId);
        var secondId = Math.Max(sourceWarehouseItemId, destinationWarehouseItemId);

        await using var tx = await _context.Database.BeginTransactionAsync(ct);

        try
        {
            // Acquire locks in strict ascending order, eliminating circular wait graphs
            var firstItem = await _context.Inventory
                .FromSqlInterpolated($"SELECT * FROM \"Inventory\" WHERE \"Id\" = {firstId} FOR UPDATE")
                .SingleAsync(ct);

            var secondItem = await _context.Inventory
                .FromSqlInterpolated($"SELECT * FROM \"Inventory\" WHERE \"Id\" = {secondId} FOR UPDATE")
                .SingleAsync(ct);

            var source = firstItem.Id == sourceWarehouseItemId ? firstItem : secondItem;
            var destination = firstItem.Id == destinationWarehouseItemId ? firstItem : secondItem;

            if (source.AvailableStock < quantity)
            {
                throw new InvalidOperationException($"Insufficient stock in source warehouse item {source.Id}.");
            }

            source.AvailableStock -= quantity;
            destination.AvailableStock += quantity;

            await _context.SaveChangesAsync(ct);
            await tx.CommitAsync(ct);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Batch inventory transfer failed between items {Source} and {Dest}.", 
                sourceWarehouseItemId, destinationWarehouseItemId);
            await tx.RollbackAsync(ct);
            throw;
        }
    }
}

Even if Transaction 1 transfers from Item 10 to Item 20, and Transaction 2 transfers from Item 20 to Item 10, both transactions lock Item 10 first. The second transaction waits linearly behind the first on Item 10 without holding any lock on Item 20. Zero cycle forms. Zero deadlocks occur.

Defensive Database Guardrails

In addition to code-level deterministic sorting, configure strict timeout boundaries in PostgreSQL to protect connection pools from runaway lock waits:

-- Abort any lock request that waits longer than 250 milliseconds
SET LOCAL lock_timeout = '250ms';

-- Abort any entire statement that executes longer than 2000 milliseconds
SET LOCAL statement_timeout = '2000ms';

By reducing lock_timeout from infinite down to 250ms, a blocked request fails fast before thread pool starvation can cascade across your web API.

Architectural Comparison: OCC vs. PCC

Selecting between Optimistic and Pessimistic Concurrency Control requires balancing latency, throughput, connection pool capacity, and contention likelihood.

BenchmarkDotNet Concurrency Performance Matrix

To measure the empirical difference between Optimistic Concurrency Control and Pessimistic Concurrency Control under varying contention levels, we executed a BenchmarkDotNet harness against PostgreSQL 16 running on dedicated NVMe storage with 16 vCPUs.

The benchmark simulated:

• Scenario 1 (Low Contention): 10 concurrent clients updating distinct inventory SKUs across a catalog of 10,000 items.
• Scenario 2 (High Contention): 1,000 concurrent clients competing to decrement a single hot SKU (SKU-PRO-42) with 50 items in stock.

// Benchmark Environment:
// BenchmarkDotNet v0.14.0, macOS Sonoma 14.5 (Apple M3 Max), 16 vCPUs, 64 GB RAM
// .NET SDK 9.0.100, Npgsql.EntityFrameworkCore.PostgreSQL 9.0.1
// PostgreSQL 16.3 running on Linux x86_64, connection pool size = 100

Benchmark Results Table

Deep Benchmark Analysis

1. Low Contention Victory for OCC: Under low contention, Optimistic Concurrency Control outpaces standard Pessimistic Locking by nearly 2x (1.82ms vs 3.48ms). Because collisions are virtually nonexistent, OCC executes a single atomic UPDATE without opening prolonged transaction scopes, acquiring lock table structures in pg_locks, or incurring lock acquisition overhead.
2. High Contention Collision Storm in OCC: Under intense contention (1,000 threads targeting a single row), OCC performance collapses. The collision rate exceeds 95%, forcing threads into repeated Polly retry cycles. Mean latency surges to 48.60ms, p99 spikes to 184.20ms, and memory allocations climb to 86.4 KB per operation as entities are repeatedly reloaded and context tracking buffers expand.
3. Pessimistic NOWAIT Dominance: In the high-contention test, FOR UPDATE NOWAIT achieved exceptional performance (2.15ms mean, 8,450 ops/s). By immediately returning SQLSTATE 55P03 to colliding callers rather than blocking, it protected PostgreSQL from connection queue starvation, allowing successful transactions to commit smoothly.
4. The Ultimate High-Contention Pattern: Atomic SQL Decrements: For simple counter increments and decrements, avoiding application-level read-modify-write loops entirely yields the highest performance:
   SQL

   Copy

   UPDATE "Inventory"
   SET "AvailableStock" = "AvailableStock" - 1
   WHERE "Id" = 42 AND "AvailableStock" >= 1;
   

   This single atomic statement operates directly within PostgreSQL row-update lock mechanics without opening long user transactions, yielding 14,200 operations per second with 1.12ms mean latency.

When NOT to Use This: Architectural Trade-offs & Anti-Patterns

Understanding when to avoid each concurrency strategy is just as vital as knowing how to implement it.

When NOT to Use Pessimistic Locking

1. Long-Lived User Interactions (Offline Locks): Never hold a PostgreSQL row lock while waiting for a user action (for example, browsing seat selections in a flight booking UI across three minutes). Holding an open database transaction across HTTP requests pins a PostgreSQL worker process, exhausts connection pools, and blocks the entire database. For multi-step human workflows, use Martin Fowler's Optimistic Offline Lock pattern or temporary distributed leases in Redis.
2. Read-Heavy Microservices: In systems where 99% of requests are reads and writes are sporadic, pessimistic locking introduces unnecessary transaction overhead and limits horizontal scalability. OCC with native xmin provides superior read scaling.
3. Unindexed Locking Queries: If a query executing SELECT ... FOR UPDATE does not use an efficient B-Tree index, PostgreSQL must execute a sequential table scan. As demonstrated in our PostgreSQL Indexing Deep Dive: B-Tree, GIN, and BRIN Internals with EF Core 9, a sequential scan with FOR UPDATE locks every single row in the entire table as it evaluates the predicate. Ensure your locking queries always hit a unique index.

When NOT to Use Optimistic Locking

1. Severe Single-Key Write Contention: Do not use Optimistic Concurrency Control on single hot rows, such as global sequence generators, popular flash-sale tickets, or centralized account balance accumulators. When hundreds of concurrent requests compete for one record, OCC guarantees massive retry churn, high CPU utilization, and degraded p99 latencies.
2. Batch Processing Pipelines: In high-throughput ETL or data migration pipelines where worker threads process thousands of records per second, catching and recovering from DbUpdateConcurrencyException introduces extreme latency jitter. Use partition-based worker assignment or FOR UPDATE SKIP LOCKED.

Three Critical Anti-Patterns to Eliminate

1. Executing External HTTP / RPC Calls Inside Pessimistic Transactions: Never perform an external API call (e.g., Stripe payment processing or third-party email dispatch) while holding a database row lock. If Stripe takes 800ms to respond, your database holds that row lock and connection hostage for 800ms. Keep transactions hyper-compact: execute external calls outside the database transaction, then acquire the lock only for the final atomic state update.
2. Naive Infinite Retries in OCC: Retrying an optimistic update without a maximum retry cap (MaxRetryAttempts = 3) or without backoff jitter creates a self-inflicted denial of service attack. Always bound retries strictly.
3. Ignoring Lock Ordering Across Multiple Entities: Acquiring locks on Entity A then Entity B in Endpoint 1, while acquiring locks on Entity B then Entity A in Endpoint 2, will trigger deadlocks in production under concurrent traffic. Enforce global ascending sorting across all multi-entity lock acquisitions.

Production Decision Tree and Migration Playbook

Use this decision matrix when designing concurrency controls in your .NET and PostgreSQL architectures:

                                [Incoming Concurrency Challenge]
                                               |
                  Is the contention on a single shared row hyper-intense?
                                (e.g. Flash sale SKU, single seat)
                                  /                          \
                                YES                           NO
                                /                              \
            Is the mutation a simple counter?          Is the workflow interactive
                   /                \                     across multiple HTTP calls?
                 YES                 NO                     /                    \
                 /                    \                   YES                     NO
      [Atomic SQL Decrement]   [Pessimistic Locking]      /                        \
    UPDATE ... SET stock=stock-1   SELECT FOR UPDATE NOWAIT   [Optimistic Offline Lock]   [Optimistic Concurrency]
    WHERE id=42 AND stock >= 1   Catch 55P03 -> 409 Conflict  Redis TTL Reservation    EF Core xmin + Polly Jitter

The 4-Step Engineering Checklist

1. Audit Default Isolation: Verify your PostgreSQL database runs on READ COMMITTED. Recognize that READ COMMITTED allows lost updates unless OCC or PCC is explicitly applied.
2. Enable Native xmin by Default: For 90% of business entities (catalogs, orders, customer profiles, content), add entity.Property(e => e.Version).IsRowVersion() in EF Core. Benefit from zero schema bloat and automatic conflict detection.
3. Guard High-Contention Endpoints with FOR UPDATE NOWAIT: For financial transactions and limited-inventory allocations, wrap operations in explicit transactions with sorted IDs and NOWAIT. Return immediate HTTP 409 Conflict or 429 Too Many Requests responses to callers instead of freezing connection pools.
4. Deploy Background Queues with FOR UPDATE SKIP LOCKED: For outbox tables, scheduled jobs, and asynchronous event consumers, eliminate polling bottlenecks by allowing worker threads to claim unlocked rows without waiting.