SQL for .NET developers Advanced

Isolation levels, locking and deadlocks

When many users read and write at once, isolation levels decide what one transaction can see of another's unfinished work. This lesson explains locks, the common isolation levels, read committed snapshot, and how to find and prevent deadlocks.

How a deadlock happens
  1. StartTransaction ALocks the Orders row
  2. Step 2Transaction BLocks the Customers row
  3. Step 3A wants CustomersWaits for B
  4. Step 4B wants OrdersWaits for A: neither can continue
  5. EndSQL Server steps inKills one as the victim with error 1205; retry it

Locks and blocking

To keep data consistent, SQL Server locks rows (or pages, or whole tables) while a transaction uses them. Readers take shared locks and writers take exclusive locks. When one transaction holds a lock another needs, the second waits: this is blocking.

Short transactions mean short locks. The biggest cause of blocking is a transaction left open while the app does something slow, such as calling an external API.

Isolation levels

  • READ UNCOMMITTED (NOLOCK): sees uncommitted changes. Can return rows twice, skip rows or show data that is later rolled back.
  • READ COMMITTED (the default): sees only committed data; readers wait for writers.
  • REPEATABLE READ: rows you read cannot change until you finish.
  • SERIALIZABLE: also stops new rows appearing in ranges you read. Safest and most blocking.
  • SNAPSHOT: reads a consistent version of data as it was when the transaction started, without blocking writers.

Read committed snapshot (RCSI)

With RCSI on, READ COMMITTED reads the last committed version of each row from the version store instead of waiting for writers. Readers stop blocking writers and writers stop blocking readers. It is the default in Azure SQL Database and a good choice for most web apps.

SQL
-- Turn on RCSI (needs a moment with no other connections)
ALTER DATABASE Shop SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Or use SNAPSHOT for one transaction
ALTER DATABASE Shop SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    SELECT SUM(Total) FROM Orders;   -- consistent, no blocking
COMMIT;

Deadlocks

A deadlock is two transactions each waiting for a lock the other holds. SQL Server picks one as the victim and fails it with error 1205. You cannot prevent every deadlock, but you can make them rare and recover from them.

  • Access tables in the same order in every transaction.
  • Keep transactions short and add indexes so statements touch fewer rows.
  • Use RCSI so plain reads do not take shared locks.
  • Retry on error 1205; EF Core's retrying execution strategy does this for transient errors.
  • Find deadlock graphs in the system_health Extended Events session.

Interview question

Why is WITH (NOLOCK) considered dangerous?

NOLOCK reads uncommitted data and does not respect locks, so a query can see changes that are later rolled back, miss rows or read the same row twice while pages split. Results can be plain wrong. If the goal is to stop reads blocking writes, turn on read committed snapshot isolation, which gives consistent committed data without blocking.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.