Pessimistic locking in EF Core means going around the change tracker into raw SQL: FromSql with a FOR UPDATE clause, inside an explicit transaction, on PostgreSQL via the Npgsql provider. EF Core has no native API for this. Its built-in concurrency model is optimistic and opt-in; pessimistic locking is database state the change tracker doesn’t model. This post walks the Postgres mechanics, the EF Core boundary where most of the footguns live, and when to reach for something else. A companion ASP.NET Core API on .NET 10 demonstrates the FOR UPDATE and FOR UPDATE SKIP LOCKED patterns end-to-end.

1. The race condition pessimistic locking is for

A lost update is a concurrency bug where two callers read the same value, both modify it, and both write it back. The second write silently overwrites the first, and the row ends up in a state that neither caller intended.

Two-timeline trace, with both callers reading a coupon that has one redemption remaining:

See the problem?

Both callers got past the application-level check, and both succeeded. The coupon shows zero redemptions remaining, but two redemptions actually fired. The database never raised any errors, because both were valid updates.

Single-threaded tests don’t catch this. Wrapping the work in a transaction at the default isolation level (Read Committed in Postgres) doesn’t fix it either. Each transaction’s reads are consistent within itself, but two concurrent transactions can still read the same row, both decide it’s safe to decrement and both write. Higher isolation levels (Repeatable Read, Serializable) help, with their own trade-offs we’ll come back to in §7.

Now imagine A’s SELECT had taken a row-level lock. B’s SELECT at T2 would block until A’s transaction terminates. When B unblocks, it reads the freshly-committed remaining = 0 and returns “exhausted” instead of firing a second redemption against an exhausted coupon. The race is gone.

1.1. The atomic-UPDATE shortcut, when it fits

Row-level locking fixes the §1 race, as the previous paragraph shows. For this specific operation (a numeric decrement guarded by a value check), an even simpler tool fixes it without a transaction the application has to manage. Express the whole decision in one UPDATE:

UPDATE coupons
SET redemptions_remaining = redemptions_remaining - 1
WHERE id = $id AND redemptions_remaining > 0;

You should combine the WHERE check (redemptions_remaining > 0) with an actual CHECK constraint that would sit underneath as a backstop for code paths that omit the guard.

Postgres takes a row-level lock for the duration of the statement, evaluates the WHERE against the post-commit row value, and reports the affected-row count. Concurrent callers serialize at the storage layer: the second waits behind the first’s lock, then re-evaluates against remaining = 0, sees the guard fail, and returns zero rows affected. The application reads 1 as Ok and 0 as Exhausted. No race. No application-side transaction, no FOR UPDATE.

From C#, ExecuteSqlInterpolatedAsync sends the statement directly without going through FromSql or the change tracker:

var affected = await db.Database.ExecuteSqlInterpolatedAsync($"""
    UPDATE coupons
    SET redemptions_remaining = redemptions_remaining - 1
    WHERE id = {couponId} AND redemptions_remaining > 0
    """, ct);

return affected == 1 ? RedeemResult.Ok : RedeemResult.Exhausted;

This works because the decision is arithmetic: the database can express “subtract one, but only if the value is greater than zero” in one statement and resolve everything atomically. The lock is held for milliseconds, not for the lifetime of an application transaction.

It doesn’t generalize. A form submission that PUTs { description: "new", expiresAt: "..." } to overwrite a row isn’t arithmetic; the new values come from the client, not from the row, and the database can’t evaluate “should this overwrite land” without an application-side check. That’s where pessimistic locking earns its place, and it’s what the rest of this post is about.

2. Why EF Core has no pessimistic-locking API

EF Core ships exactly one built-in concurrency model, and it’s optimistic. It’s also opt-in. Without configuration, the change tracker emits UPDATE ... WHERE id = $pk; no version check, no exception on conflict, last write silently wins. Opt in by marking a property: IsConcurrencyToken() (or [ConcurrencyCheck]) on a value you manage yourself; [Timestamp] / IsRowVersion() for a database-managed row version (Postgres has no native rowversion type, so this needs a trigger); or, on Npgsql, UseXminAsConcurrencyToken() to use Postgres’s hidden xmin system column, which advances on every row update for free. Whichever you pick, the tracker includes the property in the WHERE of every UPDATE, and a zero-row update result throws DbUpdateConcurrencyException. That detects the conflict at write time. It doesn’t prevent the read-modify-write race; it tells you, after the fact, that you lost.

To prevent the race rather than detect it, you have to ask the database to hold the row until you’re done. EF Core hands you FromSql for exactly this kind of escape hatch: the read goes through raw SQL with a locking clause, the returned entity is tracked normally, and the rest of your code looks like ordinary EF.

3. How FOR UPDATE works in PostgreSQL

SELECT ... FOR UPDATE acquires a row-level lock on every row the query returns. The lock lives inside the current transaction and is released at COMMIT or ROLLBACK. Other transactions trying to lock the same row wait, fail, or skip depending on the modifier you attach.

3.1. MVCC, snapshot reads, locking reads

Postgres uses MVCC (multi-version concurrency control); every write produces a new row version, and a regular SELECT reads the version visible to your transaction’s snapshot. Plain reads don’t block and aren’t blocked. Great for read-heavy workloads, useless when you want to mutate based on what you read: by the time you act on the value, it may have changed.

SELECT ... FOR UPDATE does two things a plain SELECT does not. First, it bypasses the snapshot for the locked rows: even in REPEATABLE READ, you read the latest committed version, not the snapshot version. Second, it acquires a row-level exclusive lock that other transactions must wait on.

3.2. Lock modes: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE

Postgres has four row-lock strengths, ordered from strongest to weakest:

The default for “I’ll update this row” is FOR UPDATE. Reach for FOR NO KEY UPDATE only when FK contention against this row matters — for example when you’re updating non-key columns while children are being inserted concurrently. FOR SHARE is the right choice for “ensure this still exists and is unchanged” guarantees that don’t serialize writers against each other.

3.3. Wait, NOWAIT, SKIP LOCKED, lock_timeout

The default is to wait until the lock holder’s transaction terminates. There’s no automatic lock-specific timeout, only the statement timeout, which is usually too coarse for a user-facing endpoint. Three modifiers and one GUC give you finer control:

• NOWAIT: raise SQLSTATE 55P03 (lock_not_available) immediately if the row is already locked. Use when you’d rather tell the caller “try again” than have a request thread block on a queue.
• SKIP LOCKED: silently exclude any rows currently locked by other transactions. The query returns whatever’s free. This is the canonical idiom for queue-like tables: each worker fetches a different unlocked job, no thundering herd, no coordination. The trade-off is that your reads are intentionally incomplete.
• lock_timeout: a Postgres runtime setting that bounds how long any lock acquisition waits. SET LOCAL lock_timeout = '500ms' inside a transaction caps the wait without baking NOWAIT into your SQL, and raises 55P03 when it fires.

SKIP LOCKED is worth a closer look because it changes the shape of the query, not just its timing. A worker pulling a job runs:

SELECT *
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Each worker gets a different row. No worker waits. The price is that “the next pending job” no longer means “the strictly oldest one” if older jobs are locked by busy workers. For job queues and outbox pollers that’s a fine trade. For anything that needs strict ordering, it isn’t.

3.4. No transaction, no lock

The most common cause of “I added FOR UPDATE and nothing changed” is running the query without an explicit transaction. Postgres autocommits each statement by default, so the lock is acquired and released in the same call. By the time your C# code reads the result, the row is unlocked and any concurrent caller can race ahead.

EF Core does not open an ambient transaction across SaveChanges and your reads. You have to call BeginTransactionAsync yourself, and the locking read has to happen inside that transaction.

4. Implementation in EF Core with Npgsql

Pessimistic locking in EF Core comes down to four moves: open a transaction, run FromSql with FOR UPDATE, mutate the tracked entity, commit.

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

var coupon = await db.Coupons
    .FromSql($"""
        SELECT *
        FROM coupons
        WHERE id = {couponId}
        FOR UPDATE
        """)
    .SingleAsync(ct);

coupon.Redeem();
await db.SaveChangesAsync(ct);

await tx.CommitAsync(ct);

BeginTransactionAsync opens a transaction on the context’s connection, which is what gives the lock somewhere to live. FromSql issues the locking read; the {couponId} interpolation is captured as a SQL parameter, not concatenated, so there’s no injection risk. The returned Coupon is a fully tracked entity, and coupon.Redeem() mutates it the same way any other tracked entity is mutated. SaveChangesAsync issues a parameterized UPDATE, and CommitAsync releases the lock.

Two things are easy to miss. The lock is held from the FromSql call until CommitAsync (or until the transaction is rolled back on dispose if you don’t commit). The UPDATE that SaveChangesAsync issues is just an UPDATE; there’s no second FOR UPDATE. The lock you care about is the one acquired at read time.

5. The EF-Core / raw-SQL boundary

Pessimistic locking sits at the edge of EF Core’s abstractions. Most of EF Core works fine across that boundary; a handful of things don’t, and they tend to fail quietly.

5.1. IExecutionStrategy and manual transactions

If you’ve called EnableRetryOnFailure() on the Npgsql provider, EF Core registers an execution strategy that retries on transient failures. The retry replays the operation the strategy wraps. A manually-managed BeginTransactionAsync is invisible to the strategy: if a retry fires mid-block, the transaction has already been opened on the original attempt and the strategy can’t put things back the way they were. EF Core actively detects this and throws InvalidOperationException to stop you from shipping a transaction the retry strategy can’t safely re-execute.

The fix is to wrap the whole transactional block in CreateExecutionStrategy().ExecuteAsync(...) so the strategy owns the boundary, and re-BeginTransactionAsync happens per attempt:

var strategy = db.Database.CreateExecutionStrategy();

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

    var coupon = await db.Coupons
        .FromSql($"""
            SELECT *
            FROM coupons
            WHERE id = {couponId}
            FOR UPDATE
            """)
        .SingleAsync(innerCt);

    coupon.Redeem();
    await db.SaveChangesAsync(innerCt);

    await tx.CommitAsync(innerCt);
}, ct);

The delegate is what gets retried. State you mutate outside the delegate is not rolled back between attempts. Idempotent side effects (cache invalidations, recomputable derived state) belong inside the delegate so a retry replays them. Non-idempotent ones (publishing events, sending email, charging cards) belong after ExecuteAsync returns successfully, so they fire exactly once.

5.2. FromSql and the change tracker

The Coupon returned by a FromSql query is fully tracked. Setting coupon.RedemptionsRemaining = 0 flips the tracker state to Modified, and SaveChangesAsync issues the same parameterized UPDATE it would have issued for any tracked entity. Include works on the result (FromSql(...).Include(c => c.Codes)). LINQ composition works too (FromSql(...).Where(c => !c.IsExpired)).

What you have to give up: the row shape has to match the entity. FromSql materializes a full entity, so the SQL must return all the columns the entity model expects. A projection like SELECT id, redemptions_remaining FROM coupons ... won’t materialize as a tracked Coupon, and any mutation you make on a partial projection won’t round-trip through SaveChangesAsync. When in doubt, SELECT * from the entity’s mapped table; let the model do the column work.

5.3. Connection pinning and DbContext lifetime

Row locks live on the connection that opened the transaction. EF Core’s DbContext pins its connection while a transaction is open, so as long as you don’t manually swap connections (which you usually shouldn’t), the lock and the subsequent UPDATE happen on the same physical connection. You don’t have to configure this. You just have to not break it.

The practical rules:

• One DbContext per logical operation. The default request-scoped lifetime in ASP.NET Core matches this.
• Don’t share a DbContext across requests, threads, or background work. Each scope gets its own.
• Don’t dispose the context (or the transaction) until you’ve committed or rolled back. The lock release happens at transaction termination, not when SaveChangesAsync returns. A premature using block exit before commit silently rolls back and releases the lock.

5.4. Surfacing Postgres SQLSTATEs through Npgsql

Backend errors from Postgres surface as PostgresException (a subclass of NpgsqlException) with a .SqlState string. The codes that matter for locking:

• 55P03 lock_not_available: fired by NOWAIT or by lock_timeout expiring.
• 40P01 deadlock_detected: Postgres broke a deadlock by aborting one of the participants. Almost always retryable; the next attempt usually finds a clean lock graph.
• 40001 serialization_failure: only relevant if you’ve moved to Serializable isolation. Same retry shape.

Catch on SqlState, not on exception type:

catch (PostgresException ex) when (ex.SqlState == "55P03")
{
    return RedeemResult.Busy;
}

PostgresException is the backend-error case; NpgsqlException includes transport failures (connection drops, broken pipes) that don’t carry a SQL state. If you only catch NpgsqlException, you’ll match too broadly; if you only catch PostgresException, you’ll miss the transient transport faults the retry strategy is there to handle. Catch the specific cases you can act on, and let the rest bubble.

6. A worked example: redeeming a coupon with limited uses

The example is a coupon with a finite number of redemptions: read the count, check it’s positive, decrement, save. The schema fits on one screen:

CREATE TABLE coupons (
    id                    UUID PRIMARY KEY,
    code                  TEXT NOT NULL UNIQUE,
    redemptions_remaining INTEGER NOT NULL CHECK (redemptions_remaining >= 0),
    expires_at            TIMESTAMPTZ NOT NULL
);

The CHECK (redemptions_remaining >= 0) is a backstop, not a substitute for locking. If two callers race past the application-level check and both write 0, the constraint is satisfied. The constraint catches data corruption, not concurrency bugs.

Here’s the bug-shaped version, the kind that passes every single-threaded test you write for it:

public async Task<RedeemResult> RedeemAsync(string code, CancellationToken ct)
{
    var coupon = await db.Coupons.SingleOrDefaultAsync(c => c.Code == code, ct);
    if (coupon is null) return RedeemResult.NotFound;
    if (coupon.RedemptionsRemaining <= 0) return RedeemResult.Exhausted;

    coupon.RedemptionsRemaining -= 1;
    await db.SaveChangesAsync(ct);
    return RedeemResult.Ok;
}

Under load, two requests for the same code can both read RedemptionsRemaining = 1, both decrement to 0, both save. Two redemptions fired against a coupon that allowed one. The unit tests pass. The bug only shows up under concurrency.

The locked version puts the read inside a transaction, takes FOR UPDATE, wraps the whole thing in the execution strategy, and translates 55P03 into a result the caller can act on:

public async Task<RedeemResult> RedeemAsync(string code, CancellationToken ct)
{
    var strategy = db.Database.CreateExecutionStrategy();

    return await strategy.ExecuteAsync<RedeemResult>(async innerCt =>
    {
        await using var tx = await db.Database.BeginTransactionAsync(innerCt);

        await db.Database.ExecuteSqlRawAsync(
            "SET LOCAL lock_timeout = '500ms'", innerCt);

        Coupon? coupon;
        try
        {
            coupon = await db.Coupons
                .FromSql($"""
                    SELECT *
                    FROM coupons
                    WHERE code = {code}
                    FOR UPDATE
                    """)
                .SingleOrDefaultAsync(innerCt);
        }
        catch (PostgresException ex) when (ex.SqlState == "55P03")
        {
            return RedeemResult.Busy;
        }

        if (coupon is null) return RedeemResult.NotFound;
        if (coupon.RedemptionsRemaining <= 0) return RedeemResult.Exhausted;

        coupon.RedemptionsRemaining -= 1;
        await db.SaveChangesAsync(innerCt);
        await tx.CommitAsync(innerCt);
        return RedeemResult.Ok;
    }, ct);
}

What changes under contention: when caller B’s FromSql runs while A holds the lock, B blocks at the SQL layer. If A commits within 500ms, B wakes up, reads the freshly-committed RedemptionsRemaining = 0, and returns Exhausted. If A takes longer, B’s lock_timeout fires, 55P03 is thrown, and B returns Busy. The application chooses how to handle each case (a 429-style “try again” response, a queued retry, a user-facing message); the invariant is preserved either way.

SET LOCAL lock_timeout is the gentle option for user-facing endpoints. SET LOCAL ties the value to the transaction, so it doesn’t leak to the next thing that uses this connection from the pool. The alternative, FOR UPDATE NOWAIT, fails immediately, which is harsher than necessary when you’re happy to wait half a second.

For this specific operation, §1.1’s atomic UPDATE pattern reaches the same correctness more cheaply. The pessimistic version above is the right tool when the operation needs application logic between read and write — writing an audit row in the same transaction, calling out to an external service before committing or branching on data outside the row.

Testing this is straightforward: spin up two Tasks that call RedeemAsync for the same code with one redemption left, await both, and assert exactly one Ok and one Exhausted. Run it a few hundred times in a loop; if the locking is missing or wrong, the test fails inside the first dozen iterations.

The companion project (Pessimistic-Concurrency-in-EF-Core-With-PostgreSQL) wires this end-to-end: the redeem endpoint uses the CreateExecutionStrategy + BeginTransactionAsync + SET LOCAL lock_timeout = '500ms' + 55P03 → 503 shape against a PostgreSQL 17 container via docker-compose. It also includes a second endpoint, POST /api/jobs/claim, that demonstrates the FOR UPDATE SKIP LOCKED pattern for queue-style job claiming with no waiting and no 55P03.

7. When pessimistic locking is the wrong tool

FOR UPDATE serializes writers through one row at a time. That’s exactly what you want when contention is real and concentrated on a small set of rows. It’s overkill, or wrong, in three other shapes.

7.1. Optimistic concurrency, when contention is rare

If the same row is rarely written by more than one caller, paying for a database lock on every read is a tax for nothing. EF Core’s optimistic concurrency model (an xmin column on Postgres, or a [Timestamp] byte[] RowVersion, marked with IsConcurrencyToken()) costs nothing on the happy path: the version goes into the WHERE of the UPDATE, the row is found and updated, done. When two writes do collide, one gets DbUpdateConcurrencyException, you decide whether to retry or surface a conflict, and life goes on. The cost only shows up when conflicts happen.

Reach for optimistic when contention is rare and a retry is cheap. Reach for pessimistic when contention is the common case and the retry storm that optimistic produces costs more than serializing through a row lock.

7.2. Serializable isolation, when the invariant spans rows

If the invariant is “no two reservations may overlap in time” or “the sum of all rows in this set must not exceed N”, row locking can’t help: there is no single row to lock. You need the whole transaction set to look serializable. Postgres’s Serializable isolation level uses SSI (serializable snapshot isolation): it tracks read-write dependencies between concurrent transactions and aborts one with 40001 if the set wouldn’t have been serializable. The retry shape is the same as the optimistic case: catch 40001, retry the whole transaction.

The trade is more aborts under contention, in exchange for an invariant that’s hard to express as a row lock.

7.3. Advisory locks, when the thing to lock isn’t a row yet

pg_advisory_xact_lock(key) is a Postgres-level lock keyed on a 64-bit integer (or a pair of 32-bit integers), held for the duration of the transaction. It doesn’t require a row to exist. The classic use case is “claim username alice before any row exists for it”: you can’t take FOR UPDATE on a row that hasn’t been inserted, but you can take an advisory lock on hash('alice') and serialize all callers competing for that name through the same lock. Other uses include gating a periodic job to one runner, and coordinating between transactions that touch unrelated rows but share a logical resource.

Advisory locks are cheap, transaction-scoped (with the _xact_ variant; the session-scoped variant exists too and is harder to use safely), and don’t show up on rows. Worth knowing when row-level locking doesn’t fit.

7.4. Picking one

A short table for the choice:

Pessimistic locking is a precision tool. It’s the right answer for a specific problem: a small set of rows under real write contention where you want the database to do the serialization. Use it there. If your hot path runs every request through the same locked row, you’ve reinvented a single-threaded queue, and the next bottleneck is yours to find.