Isolation Levels: How the Last Item in Stock Gets Sold Twice

Two customers buy the last item in stock at the same second. Both orders go through. Every line of your code ran inside a transaction, and SQL Server did exactly what its default setting promises.

That setting is the isolation level. There are four of them. This post walks through each one, the anomaly it stops, and the .NET class that quietly picks the strictest one for you.

A transaction isn't a freeze

A transaction promises all or nothing: your changes land together, or not at all. What it doesn't promise is that the world stops while you work. Other transactions keep reading and writing while yours runs.

How much of their work you can see is a dial, and that dial is the isolation level. It has four steps:

1. Read uncommitted
2. Read committed
3. Repeatable read
4. Serializable

Each step up protects you from one more anomaly. Each step up also makes somebody wait longer.

The three anomalies are:

• Dirty read: you read data another transaction hasn't committed yet.
• Non-repeatable read: you read the same row twice and get two values.
• Phantom read: you run the same query twice and get a different set of rows.

Read uncommitted: a price that never existed

Transaction 1 sets a price to zero and hasn't committed. Transaction 2 reads the price and sees zero. Then transaction 1 rolls back. Transaction 2 just used a price that never existed. That's a dirty read.

You've probably seen this level in production code without its name on it:

SELECT SUM(Total) FROM Orders WITH (NOLOCK);

People add NOLOCK so a report stops waiting on writers. But the SQL Server docs are direct about it: read uncommitted has the same effect as NOLOCK on every table in every SELECT. Dirty reads are included, and rows can appear or disappear in the data set while you read. NOLOCK isn't a speed setting. It's agreeing to read data that might never exist.

Read committed: same SELECT, two answers

One step up is read committed, the default on both SQL Server and PostgreSQL. You only see committed data, so dirty reads are gone.

But data can still change between two statements of your transaction:

T1	T2
SELECT balance → 100
	UPDATE → 50, COMMIT
SELECT balance → 50

Same query, same transaction, two answers. That's a non-repeatable read.

The lost update: where the two customers come from

Here's the stock bug in ordinary EF Core code:

var p = await db.Products.SingleAsync(x => x.Id == id);   // Stock = 1
if (p.Stock > 0)
    p.Stock -= 1;
await db.SaveChangesAsync();                              // SET Stock = 0

Run it twice at the same time under read committed. Both requests read Stock = 1. Both pass the check. Both send UPDATE ... SET Stock = 0. Two orders, one item, and nobody gets an error. That's a lost update.

You can fix it without touching the isolation level.

Option 1: one atomic statement. Put the check in the WHERE and let the database do the subtraction with ExecuteUpdateAsync:

var changed = await db.Products
    .Where(x => x.Id == id && x.Stock > 0)
    .ExecuteUpdateAsync(s => s.SetProperty(x => x.Stock, x => x.Stock - 1));

if (changed == 0)
{
    // sold out
}

ExecuteUpdateAsync returns the number of rows it changed. Zero means somebody else got the last one.

Option 2: a concurrency token. Add a rowversion column with [Timestamp]. EF Core adds the original version to the WHERE of the update, so the second save matches zero rows and throws DbUpdateConcurrencyException.

Either way, the second buyer sees "sold out", not a free item.

Repeatable read: rows you read stay locked

At repeatable read, SQL Server places shared locks on every row you read and holds them until the transaction ends. Nobody can change those rows, so your second SELECT gets the same answer.

Run the stock code at this level and the lost update is gone, but something else shows up. Both buyers read the stock and keep their shared locks. Both then try to update, and each one waits for the other. SQL Server detects the deadlock, picks one transaction as the victim, rolls it back, and raises error 1205: "chosen as the deadlock victim. Rerun the transaction."

The bug is fixed. The price is that you now need a retry.

Phantoms: 10 seats, 11 people

Repeatable read has a hole. It locks rows that exist. It can't lock a row that isn't there yet.

A workshop has 10 seats. Request A counts the sign-ups: 9. There's room, so it inserts one. Request B runs the same count at the same time, also sees 9, and inserts too. Eleven people, ten seats. The new row that appeared under request B's feet is a phantom.

Serializable: lock the whole range

Serializable places range locks on the key range your query read. "Sign-ups for workshop 7" becomes a range nobody can insert into until you commit. The result behaves as if the transactions ran one at a time.

The cost: more waiting, more deadlocks, more retries. It's the safest level, and the slowest.

The whole dial

This is the SQL standard's version (PostgreSQL's docs have the clearest table):

Level	Dirty read	Non-repeatable read	Phantom
Read uncommitted	possible	possible	possible
Read committed	no	possible	possible
Repeatable read	no	no	possible
Serializable	no	no	no

Your database might not follow it exactly.

Same name, different behavior

SQL Server. The default read committed uses locks: a reader waits for a writer to finish. Turn on the READ_COMMITTED_SNAPSHOT database option and readers get the last committed version of the row instead, with no waiting. Same level name, different behavior. On Azure SQL Database that option is on by default, so the same code can block on your laptop and not in the cloud. You can check yours:

SELECT name, is_read_committed_snapshot_on FROM sys.databases;

PostgreSQL. Read uncommitted behaves like read committed, so you can't get a dirty read at all. Its repeatable read doesn't allow phantoms either. But the workshop bug still gets through, because both transactions count from their own snapshot and both insert: that's a serialization anomaly, and only serializable stops it, by failing one transaction with SQLSTATE 40001 that you retry.

MySQL (InnoDB). The default is repeatable read, not read committed.

What EF Core and .NET pick for you

EF Core picks nothing. It uses your database's default, and SaveChanges applies its changes in a transaction at that level. To choose a level, pass it when you begin the transaction:

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

TransactionScope picks the strictest one. Write new TransactionScope() with no options and, by default, the transaction runs at Serializable. That's range locks, more waiting and more deadlocks on every query inside it, and you never asked for any of it. Pass TransactionOptions and pick the level yourself:

using var scope = new TransactionScope(TransactionScopeOption.Required,
    new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted },
    TransactionScopeAsyncFlowOption.Enabled);

(TransactionScopeAsyncFlowOption.Enabled is what lets the ambient transaction flow across await, per the EF Core docs.)

A checklist for your own app

• Know your default. One query on sys.databases tells you whether read committed snapshot is on.
• Treat NOLOCK as a bug, not a speed fix.
• Read, then write? Use one UPDATE with the check in the WHERE, or a concurrency token.
• Find every new TransactionScope() and give it a level.
• If you move up the dial, write the retry (error 1205 on SQL Server, SQLSTATE 40001 on PostgreSQL).

A transaction doesn't stop others from touching your data. The isolation level decides how much they can, so pick it on purpose, before your users find the default for you.

Which isolation level does your app run at right now? Do you know it without checking? Tell me in the comments.

---

I make Vlad's Stack — short explainers on how the tools you use every day actually work, for people who write code (.NET-flavored). The video version of this post is on the channel: https://www.youtube.com/@VladsStack