Complete Guide to Transaction Isolation Levels in SQL

A bank passbook story to begin

Imagine two brothers, Aarav and Rohan, who share one bank account. They each have their own phone app to check the balance and send money.

One afternoon the account has 1000 rupees. At the same moment:

• Aarav opens the app to send 800 rupees to a shop.
• Rohan opens the app to send 700 rupees to a friend.

Both apps read the balance as 1000 at the same time. Both think there is enough money. If the bank is careless, both payments go through, and the account ends up at minus 500 rupees. That should never happen.

The bank stops this mess with transactions and isolation levels. A transaction is one complete piece of work that must finish fully or not at all. An isolation level is the rule that decides how much Aarav's app and Rohan's app are allowed to see of each other while they both work.

That is the whole idea of this article. We will learn what can go wrong when many people touch the same data at once, and how each isolation level protects you, step by step, with simple tables and C# code.

What a transaction is

A transaction groups several database steps into one unit. The classic example is moving money: take 100 from account A, add 100 to account B. Both steps must happen together. If only the first runs, money disappears.

Databases promise four things about transactions, known by the word ACID:

This article is all about the I — Isolation. Isolation is a dial. You can turn it low for speed, or high for safety. The isolation level is how you set that dial.

The three problems isolation protects you from

When two transactions run at the same time, three classic problems can appear. Learn these three names well, because every isolation level is just a different answer to "which of these do I allow?"

1. Dirty read

A dirty read happens when transaction B reads a change that transaction A made but has not committed yet. If A then rolls back, B has read data that never really existed.

Think of Aarav writing "balance = 200" in pencil but not yet confirming. Rohan peeks, sees 200, and acts on it. Then Aarav rubs out his pencil mark. Rohan acted on a ghost number.

2. Non-repeatable read

A non-repeatable read happens when transaction B reads the same row twice and gets two different values, because transaction A changed and committed that row in between.

Rohan checks the balance, sees 1000, looks away, checks again, and now it says 300. Same question, two different answers, inside one transaction.

3. Phantom read

A phantom read is about a set of rows, not a single row. Transaction B runs a search like "all orders above 500 rupees" and gets 3 rows. It runs the same search again and now gets 4 rows, because transaction A inserted a new matching row. The new row is a "phantom" that appeared from nowhere.

The four standard isolation levels

The SQL standard defines four levels. Each one blocks more problems than the one before it. Here is the master table you will keep coming back to.

SQL Server adds a fifth special level called SNAPSHOT, which we cover later. It uses a different trick (row versions) to give strong safety without heavy locking.

Read the table from top to bottom like a ladder. Each step up trades a little speed for a little more safety.

How to set the level in SQL

In T-SQL you choose the level for your session with one statement before you begin the transaction.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
BEGIN TRANSACTION;
  SELECT Balance FROM Accounts WHERE AccountId = 1;
  -- do some work
  UPDATE Accounts SET Balance = Balance - 800 WHERE AccountId = 1;
COMMIT;

The level stays set for the whole session until you change it again. The default in SQL Server is READ COMMITTED, so if you never call SET TRANSACTION ISOLATION LEVEL, that is what you get.

READ UNCOMMITTED: the wild west

This is the lowest level. A reader can see other transactions' uncommitted changes. It does not take shared locks while reading. In SQL Server, the famous WITH (NOLOCK) hint is the same as reading at this level.

It is fast because it almost never waits. But it allows all three problems, including dirty reads. You can read numbers that get rolled back a second later.

Use it only for rough, throwaway reads where a small mistake does not matter, like a live "approximate count" on a dashboard. Never use it for money, stock counts, or anything a decision depends on.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(*) FROM Orders;   -- may include rows that get rolled back

READ COMMITTED: the sensible default

This is the default in SQL Server, and the level most apps should use most of the time. It guarantees one thing clearly: you never read uncommitted data. Dirty reads are gone.

By default it does this with short-lived locks. When you read a row, it takes a shared lock just long enough to read, then lets go. So another transaction's half-finished change is invisible to you.

But it still allows non-repeatable reads and phantoms. Read the same row twice and the value can change between reads, because the lock was released after the first read.

READ COMMITTED SNAPSHOT (RCSI): readers do not block

There is a smarter version of READ COMMITTED that uses row versioning instead of locks. You turn it on at the database level:

ALTER DATABASE MyAppDb SET READ_COMMITTED_SNAPSHOT ON;

Once this is on, every statement reading at READ COMMITTED sees a consistent picture of the data as it was at the moment the statement started. Old versions of rows are kept in tempdb. The big win is that readers do not block writers and writers do not block readers. You still avoid dirty reads, but without the waiting.

Azure SQL Database has this turned on by default, which is why new databases there feel less "blocky" than an old on-premises SQL Server.

REPEATABLE READ: rows hold still

At REPEATABLE READ, every row you read is locked until your transaction ends. So if you read a row, no one else can change or delete it until you finish. Read it again, get the same value. Non-repeatable reads are gone.

But phantoms can still happen. You locked the rows you read, but you did not lock rows that do not exist yet. Someone can still insert a brand new row that matches your search.

SERIALIZABLE: as if one at a time

This is the strongest standard level. It locks not only the rows you read, but the ranges around them, so no one can insert a new matching row either. Phantoms are gone. All three problems are blocked.

The promise of SERIALIZABLE is simple and strong: the result is as if every transaction ran one after another, never overlapping. It is the safest choice for critical logic like "is this seat already booked?"

The cost is the most blocking and the highest chance of deadlocks, because it holds wide locks for the whole transaction. Use it only where correctness truly demands it.

SNAPSHOT: a frozen photo of the data

SNAPSHOT isolation is SQL Server's special row-versioning level. You enable it once on the database:

ALTER DATABASE MyAppDb SET ALLOW_SNAPSHOT_ISOLATION ON;

Then a transaction can ask for it:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
  SELECT * FROM Orders WHERE Total > 500;  -- sees data as of transaction start
COMMIT;

The difference from REPEATABLE READ and SERIALIZABLE is that SNAPSHOT does not block. It hands the transaction a frozen photo of the whole database as it looked when the transaction began. Every read in that transaction sees the same photo, so you get no dirty reads, no non-repeatable reads, and no phantoms — all without making readers wait on writers.

The trade-offs: it uses space in tempdb to keep old row versions, and if two transactions try to update the same row, the second one fails with an update conflict that you must retry. SNAPSHOT shines for read-heavy reporting that must be consistent but must not slow down the writers.

A side-by-side summary

Here is how the levels compare on what they block and how they work under the hood.

Using isolation levels from C#

In .NET you control the isolation level when you start a transaction. With raw ADO.NET you pass it to BeginTransaction.

using Microsoft.Data.SqlClient;
using System.Data;
 
await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
 
// Ask for the strongest level for a critical booking check.
await using var tran = await connection.BeginTransactionAsync(
    IsolationLevel.Serializable);
 
try
{
    var command = connection.CreateCommand();
    command.Transaction = (SqlTransaction)tran;
    command.CommandText =
        "SELECT COUNT(*) FROM Bookings WHERE SeatId = @seat";
    command.Parameters.AddWithValue("@seat", 42);
 
    var taken = (int)(await command.ExecuteScalarAsync())!;
    if (taken == 0)
    {
        // safe to insert the booking here
    }
 
    await tran.CommitAsync();
}
catch
{
    await tran.RollbackAsync();
    throw;
}

With Entity Framework Core you pass the level to BeginTransaction on the database facade. EF Core then runs your SaveChanges work inside that transaction.

using Microsoft.EntityFrameworkCore;
using System.Data;
 
await using var db = new AppDbContext();
 
await using var tran = await db.Database.BeginTransactionAsync(
    IsolationLevel.Snapshot);
 
try
{
    var report = await db.Orders
        .Where(o => o.Total > 500)
        .ToListAsync();
 
    // every read inside this block sees one consistent snapshot
    await tran.CommitAsync();
}
catch
{
    await tran.RollbackAsync();
    throw;
}

A small but important note for EF Core users: if you set SERIALIZABLE or SNAPSHOT, be ready to catch failures and retry. Higher levels mean more deadlocks and update conflicts, and the correct response is usually to roll back and try the whole transaction again, not to crash.

A practical rule of thumb

You do not need to overthink this every day. Most teams follow a simple rule:

• Use READ COMMITTED (ideally with RCSI on) for almost everything.
• Step up to SERIALIZABLE only for the small number of operations where a phantom would cause a real bug, like booking the last seat or selling the last item in stock.
• Use SNAPSHOT for long, consistent reports that must not block your writers.
• Avoid READ UNCOMMITTED unless the read is genuinely throwaway.

The goal is to use the lowest level that is still correct for the task. Lower means less blocking, fewer deadlocks, and faster apps.

Common mistakes to avoid

A few traps catch many developers. Watch out for these.

First, sprinkling WITH (NOLOCK) everywhere "to make queries fast." That is READ UNCOMMITTED in disguise. It can return rows twice, skip rows, or show data that gets rolled back. Speed is not worth wrong numbers in a financial report.

Second, jumping straight to SERIALIZABLE "to be safe." That often causes blocking and deadlocks under load. Safety is good, but use it only where the task needs it.

Third, forgetting that SNAPSHOT and RCSI must be turned on at the database level first. If they are off, asking for SNAPSHOT in code will fail.

Fourth, holding transactions open too long. The longer a transaction lives, the longer its locks (or row versions) stay around, and the more you hurt everyone else. Keep transactions short and focused.

Quick recap

• A transaction is one unit of work that fully succeeds or fully rolls back. The I in ACID is isolation, which controls how transactions see each other.
• There are three classic problems: dirty read (uncommitted data), non-repeatable read (a row's value changes between reads), and phantom read (new rows appear in a repeated search).
• The four standard levels are a ladder: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Each step up blocks one more problem but adds more locking.
• READ COMMITTED is the SQL Server default and the right choice for most work. Turning on READ_COMMITTED_SNAPSHOT (RCSI) lets readers and writers stop blocking each other.
• SNAPSHOT gives each transaction a frozen, consistent photo of the data using row versions in tempdb, with no blocking but a chance of update conflicts.
• In C#, pass the level to BeginTransaction (ADO.NET) or db.Database.BeginTransactionAsync (EF Core), and be ready to retry at higher levels.
• The golden rule: use the lowest level that is still correct for the task.

References and further reading

• SET TRANSACTION ISOLATION LEVEL (Transact-SQL) — Microsoft Learn
• Transaction Isolation Levels — Microsoft Learn
• Snapshot Isolation in SQL Server (ADO.NET) — Microsoft Learn
• Understanding isolation levels (JDBC) — Microsoft Learn
• Transaction Isolation Levels in DBMS — GeeksforGeeks