Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 41 ยท Performance and Concurrency

Pessimistic Locking and Isolation Levels

Control concurrent access in EF Core with transaction isolation levels, row locks through raw SQL, atomic updates and retry strategies, with runnable C# stand-ins.

Optimistic concurrency detects conflicts after the fact. Sometimes that is not enough: a seat can be sold only once, a bank balance must never go negative, a job queue row must be claimed by exactly one worker. For these cases you need the database to prevent two sessions from interfering with each other. Two tools do that: transaction isolation levels and explicit row locks.

EF Core code is read-only on this site. The runnable blocks are stand-ins that model the ideas in plain C#.

What an isolation level controls

Every transaction runs at an isolation level that decides what it can see of other transactions' work. Weaker levels are faster; stronger levels prevent more anomalies:

Level Dirty read Non-repeatable read Phantom rows
Read Uncommitted possible possible possible
Read Committed (default on SQL Server, PostgreSQL) no possible possible
Repeatable Read no no possible (not in PostgreSQL or MySQL InnoDB)
Serializable no no no
Snapshot (SQL Server) no no no

A dirty read sees uncommitted data. A non-repeatable read reads the same row twice and gets different values. A phantom is a new row appearing in a repeated range query.

In EF Core you choose the level when you start a transaction (see SaveChanges and transactions):

C#
await using var tx = await db.Database.BeginTransactionAsync(IsolationLevel.Serializable);

var seat = await db.Seats.SingleAsync(s => s.Id == seatId);
if (seat.IsSold) throw new InvalidOperationException("Already sold");

seat.IsSold = true;
seat.BuyerId = buyerId;
await db.SaveChangesAsync();
await tx.CommitAsync();

Under Serializable, two sessions running this at once cannot both succeed: the database makes one wait or aborts it with a serialization failure or deadlock error (SQL Server error 1205, PostgreSQL SQLSTATE 40001). Your code must be ready to retry the whole transaction.

The classic race: read, check, write

Why is the default level not enough here? Because two sessions can both read IsSold = false before either writes:

C# Stand-in: two buyers race for one seat

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

The first half shows the lost update; the second half shows how a lock turns the check and the write into one indivisible step. Which buyer wins the locked race can vary between runs, but exactly one of them always does.

Explicit row locks (raw SQL)

EF Core has no LINQ API for row locks, so you use raw SQL inside a transaction. The syntax is provider-specific:

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

// SQL Server: UPDLOCK keeps other writers (and other UPDLOCK readers) out until we commit.
var seat = await db.Seats
    .FromSqlInterpolated($"SELECT * FROM Seats WITH (UPDLOCK, ROWLOCK) WHERE Id = {seatId}")
    .SingleAsync();

// PostgreSQL / MySQL 8: SELECT ... FOR UPDATE
//   .FromSqlInterpolated($"SELECT * FROM \"Seats\" WHERE \"Id\" = {seatId} FOR UPDATE")

if (!seat.IsSold)
{
    seat.IsSold = true;
    await db.SaveChangesAsync();
}
await tx.CommitAsync();       // locks are released here

Rules of thumb:

  • A lock only lasts as long as the transaction, so always wrap it in an explicit transaction. Outside one, the lock is released immediately.
  • Keep the transaction short: no user interaction, no HTTP calls while holding locks.
  • Lock rows in a consistent order (for example by ascending Id) to avoid deadlocks.
  • PostgreSQL and MySQL also offer FOR UPDATE SKIP LOCKED, which is ideal for job queues: each worker claims a different unlocked row.

Usually better: make the write atomic

Many cases that look like they need a lock only need a conditional update. The database applies the condition and the change in one statement, so no race can happen:

C#
int rows = await db.Seats
    .Where(s => s.Id == seatId && !s.IsSold)
    .ExecuteUpdateAsync(s => s
        .SetProperty(x => x.IsSold, true)
        .SetProperty(x => x.BuyerId, buyerId));

if (rows == 0) { /* someone else got it first */ }

ExecuteUpdateAsync (see bulk operations and raw SQL) sends a single UPDATE ... WHERE Id = @id AND IsSold = 0, and the row count tells you whether you won. This needs no transaction, holds a lock for microseconds, and scales best.

C# Stand-in: conditional update returns rows affected

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Retrying after failures

High isolation levels and locks can produce deadlocks and serialization failures. These are transient: running the whole transaction again usually works. EF Core provides an execution strategy for this:

C#
var strategy = db.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    await using var tx = await db.Database.BeginTransactionAsync(IsolationLevel.Serializable);
    // ... the whole unit of work, re-runnable ...
    await db.SaveChangesAsync();
    await tx.CommitAsync();
});

With EnableRetryOnFailure() configured on the provider (SQL Server, Npgsql), user-started transactions must run inside ExecuteAsync like this, because the strategy needs to replay the whole block. Make the block idempotent: re-reading data, not relying on state captured before the retry.

C# Stand-in: retry a transaction on a transient failure

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Choosing a tool

flowchart TD
    A["Concurrent writers on the same data?"] --> B{"Is a conflict rare and cheap to redo?"}
    B -->|yes| C["Optimistic concurrency token"]
    B -->|no| D{"Can one conditional UPDATE express it?"}
    D -->|yes| E["ExecuteUpdate with a WHERE guard"]
    D -->|no| F{"Must you read, then decide, then write?"}
    F -->|yes| G["Transaction + row lock or Serializable + retry"]

Pick the lightest tool that is still correct.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is read-check-write unsafe at the default Read Committed level?

    Read Committed does not hold a lock on rows you only read, so both sessions can pass the check.
  2. 2. How do you take a row lock with EF Core?

    EF Core has no LINQ row-lock API. Use FromSql with provider syntax in an explicit transaction.
  3. 3. What does a rows-affected value of 0 from ExecuteUpdate with a guarded WHERE tell you?

    The guard and the change happen in one atomic statement, so 0 rows means the guard failed.
  4. 4. Why must code inside an execution strategy block be re-runnable?

    After a deadlock or connection blip the strategy runs the delegate again, so it must not depend on state from the failed attempt.

Summary

  • Isolation levels decide which anomalies a transaction can see; Serializable prevents all of them at a cost.
  • For read-then-write logic use a short transaction with a row lock (UPDLOCK, FOR UPDATE) via raw SQL.
  • A guarded ExecuteUpdate is often the simplest, fastest, race-free option.
  • Expect deadlocks and serialization failures; retry the whole unit of work with an execution strategy.

Next: Testing EF Core code