A Clever Way to Implement Pessimistic Locking in EF Core

A bank passbook at the village counter

Imagine a small bank in your town. There is only one paper passbook for an account, kept at the counter.

Two people walk in at the same time to update the same account. If both grab the passbook together and write new balances, the numbers get messed up. One person's entry erases the other's.

So the clerk uses a simple rule. When you come to update the passbook, the clerk hands it to you and to nobody else. Everyone else has to wait in line. When you finish and return the book, the next person gets it.

That waiting rule is pessimistic locking. We assume a clash will happen, so we stop it before it starts by giving the row to one person at a time.

The opposite idea, optimistic locking, is more relaxed. It lets everyone read and write, and only at the end it checks "did someone change this while I was busy?" If yes, it asks you to try again.

This article is about the pessimistic style, and a clean, reusable way to do it in EF Core.

Why we even need this

Let us look at a real bug. Say you sell concert tickets. Only 1 ticket is left. Two buyers click "Buy" at the exact same moment.

Without any lock, both requests do this:

1. Read the row: stock is 1. Good, looks available.
2. Subtract one: 1 - 1 = 0.
3. Save.

Both think they got the last ticket. You just sold the same seat twice. This is called a race condition.

A pessimistic lock fixes this. Buyer A locks the row first. Buyer B has to wait. By the time B reads, stock is already 0, so B is correctly told "sold out".

The two flavours of concurrency control

Before we code, here is a quick side-by-side so you pick the right tool.

Neither one is "better". They solve the same problem from opposite directions. Use optimistic when clashes are rare (editing your own profile). Use pessimistic when the same row is hot (last ticket, wallet balance, shared counter).

The core idea in EF Core

Here is the key fact: EF Core has no built-in pessimistic lock method. There is no .Lock() you can call.

But the database already knows how to lock rows. EF Core lets you send raw SQL, and SQL has lock hints. So the trick is simple:

1. Start an explicit transaction.
2. Read the row with a raw SQL query that includes a lock hint.
3. The lock is now held for the whole transaction.
4. Do your work (change the entity).
5. Save changes and commit. The lock is released.

The lock hint differs by database:

Step 1: the raw SQL read with a lock

Let us lock a single ticket row on SQL Server. We use FromSql so EF Core still tracks the entity, which means we can change it and save normally.

using var transaction = await dbContext.Database
    .BeginTransactionAsync(cancellationToken);
 
// UPDLOCK + ROWLOCK locks just this row until the transaction ends.
Ticket? ticket = await dbContext.Tickets
    .FromSql(
        $"""
        SELECT * FROM Tickets WITH (UPDLOCK, ROWLOCK)
        WHERE Id = {ticketId}
        """)
    .FirstOrDefaultAsync(cancellationToken);
 
if (ticket is null)
{
    return Results.NotFound();
}
 
// From here, no other transaction can change this row.
ticket.Status = TicketStatus.Sold;
 
await dbContext.SaveChangesAsync(cancellationToken);
await transaction.CommitAsync(cancellationToken);

A few important points:

• FromSql (with an interpolated string) is safe from SQL injection. EF Core turns {ticketId} into a real SQL parameter, not glued text.
• Because we read through DbSet<Ticket>, the entity is tracked. So SaveChangesAsync writes our change.
• The lock holds from the FromSql line until CommitAsync. Keep that gap short.

Step 2: the PostgreSQL version

On PostgreSQL the same idea uses FOR UPDATE. You can also choose how to behave when the row is already locked.

using var transaction = await dbContext.Database
    .BeginTransactionAsync(cancellationToken);
 
// FOR UPDATE locks the row. NOWAIT fails fast instead of waiting.
Ticket? ticket = await dbContext.Tickets
    .FromSql(
        $"""
        SELECT * FROM "Tickets"
        WHERE "Id" = {ticketId}
        FOR UPDATE NOWAIT
        """)
    .FirstOrDefaultAsync(cancellationToken);
 
if (ticket is null)
{
    return Results.NotFound();
}
 
ticket.Status = TicketStatus.Sold;
 
await dbContext.SaveChangesAsync(cancellationToken);
await transaction.CommitAsync(cancellationToken);

The choice of waiting style matters a lot:

SKIP LOCKED is the secret behind many job-queue systems. Ten workers can each grab a different free job without stepping on each other.

Step 3: the clever, reusable helper

Writing that raw SQL everywhere is messy and easy to get wrong. The clever part is to hide it behind one small extension method. Then your business code stays clean and the database detail lives in one place.

public static class LockingExtensions
{
    // One place that knows the lock SQL. Callers just ask for a locked row.
    public static Task<TEntity?> FirstWithLockAsync<TEntity>(
        this DbSet<TEntity> dbSet,
        int id,
        string tableName,
        CancellationToken ct = default)
        where TEntity : class
    {
        // SQL Server flavour. Swap the hint for FOR UPDATE on PostgreSQL.
        FormattableString sql =
            $"SELECT * FROM {tableName} WITH (UPDLOCK, ROWLOCK) WHERE Id = {id}";
 
        return dbSet.FromSql(sql).FirstOrDefaultAsync(ct);
    }
}

Now the buying code reads almost like plain English:

using var transaction = await dbContext.Database
    .BeginTransactionAsync(ct);
 
Ticket? ticket = await dbContext.Tickets
    .FirstWithLockAsync(ticketId, "Tickets", ct);
 
if (ticket is null || ticket.Status == TicketStatus.Sold)
{
    await transaction.RollbackAsync(ct);
    return Results.Conflict("Ticket not available.");
}
 
ticket.Status = TicketStatus.Sold;
 
await dbContext.SaveChangesAsync(ct);
await transaction.CommitAsync(ct);

The "clever" idea is not magic SQL. It is putting the ugly part in one tested helper so the rest of your app never has to think about lock hints again.

What is really happening under the hood

When you ask for UPDLOCK or FOR UPDATE, the database places a lock record on that row. Any other transaction that tries to take a conflicting lock on the same row is put to sleep. The database wakes it up only when your transaction commits or rolls back.

This is why two rules matter so much:

1. Always use a transaction. A lock with no transaction has nothing to attach to and gets released too soon (or never the way you expect).
2. Keep the locked section short. While you hold the lock, everyone else who wants that row is waiting. Do not call slow web APIs or send emails while holding a lock.

Watch out for deadlocks

A deadlock is when two transactions wait on each other and neither can move.

Picture two clerks. Clerk A holds passbook 1 and now wants passbook 2. Clerk B holds passbook 2 and now wants passbook 1. Both wait forever. The bank manager (the database) notices, picks a loser, and throws one out.

The cure is calm and simple:

• Lock rows in a fixed order. If everyone always locks the lower Id first, the circle can never form.
• Keep transactions short. Less time holding locks means less chance to overlap.
• Use NOWAIT or SKIP LOCKED where waiting is not worth it, so a request fails fast instead of hanging.
• Handle the failure. Catch the deadlock or timeout error, roll back, and either retry or tell the user politely.

When NOT to use pessimistic locking

Pessimistic locking is powerful, but it is not free. Avoid it when:

• Conflicts are rare. Optimistic locking with a RowVersion column is lighter.
• You hold the lock for a long time. Long locks create long queues and angry users.
• The work spans many rows or services. Then a queue or a distributed lock may serve you better.

A good habit: reach for optimistic locking first. Move to pessimistic only when the same row is genuinely hot and retries become painful, such as a flash sale on one product.

A note on libraries and licensing

You do not need any paid library for this. Pessimistic locking here uses only EF Core itself plus your database. That is worth saying because some popular .NET libraries changed their terms recently. For example, MediatR and MassTransit moved to commercial licensing for newer versions. None of that touches this technique. Plain EF Core FromSql inside a transaction is all you need, and it is fully open and free.

Putting it all together

Here is the mental checklist for a safe pessimistic lock:

1. Begin an explicit transaction.
2. Read the target row with FromSql and a lock hint.
3. Check the row is still valid (not sold, still in stock).
4. Make your change on the tracked entity.
5. SaveChangesAsync, then CommitAsync.
6. On any error, RollbackAsync so the lock is freed.

Follow those six steps and you turn a dangerous race condition into a calm, orderly line, just like the village clerk handing out one passbook at a time.

Quick recap

• Pessimistic locking means "lock the row first, let others wait." It stops race conditions on hot rows.
• EF Core has no built-in lock API. You add a lock hint through FromSql inside an explicit transaction.
• Use WITH (UPDLOCK, ROWLOCK) on SQL Server and FOR UPDATE on PostgreSQL or MySQL.
• FromSql with an interpolated string is parameterised and safe from SQL injection, and keeps the entity tracked so you can save.
• The "clever" part is wrapping the SQL in one reusable helper so business code stays clean.
• The lock is tied to the transaction. Commit or roll back to release it, and keep that window short.
• Watch for deadlocks: lock in a fixed order, keep it short, and consider NOWAIT or SKIP LOCKED.
• Prefer optimistic locking first; switch to pessimistic only when a single row is truly contested.

References and further reading

• Handling Concurrency Conflicts — EF Core (Microsoft Learn)
• A Clever Way To Implement Pessimistic Locking in EF Core — Milan Jovanović
• Solving Race Conditions With EF Core Optimistic Locking — Milan Jovanović
• Support SELECT FOR UPDATE / UPDLOCK — dotnet/efcore Issue #26042
• Optimistic vs. Pessimistic Concurrency in EF Core (with Table Hints) — DEV Community