Transaction Isolation and Concurrency | Guided by A Guide to Cloud

When transactions collide

Simple explanation

Imagine two people editing the same Google Doc at the same time.

If both type in the same paragraph, whose changes win? Databases face this problem constantly — hundreds of users reading and writing simultaneously. Isolation levels define the rules: “Can you see someone else’s uncommitted changes?” “Can new rows appear mid-query?” The stricter the rules, the safer the data — but the slower the system.

Blocking happens when one transaction waits for another’s lock. Deadlocks happen when two transactions each hold a lock the other needs — and neither can proceed. Both are common exam topics.

The five isolation levels

Five isolation levels — trade consistency for concurrency
Isolation Level	Dirty Reads?	Non-Repeatable Reads?	Phantoms?	Blocking Writers?
READ UNCOMMITTED	Yes	Yes	Yes	No — reads do not take locks
READ COMMITTED (default)	No	Yes	Yes	Briefly — shared locks released after read
REPEATABLE READ	No	No	Yes	Yes — shared locks held until commit
SERIALIZABLE	No	No	No	Yes — range locks prevent new rows
SNAPSHOT	No	No	No	No — uses row versioning, not locks

Concurrency phenomena explained

Dirty read: reading uncommitted data from another transaction (that might roll back)
Non-repeatable read: reading the same row twice and getting different values (another transaction committed a change between reads)
Phantom: re-running a query and getting new rows that were not there before (another transaction inserted matching rows)

-- Set isolation level for a session
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

-- Or per-query hint
SELECT * FROM Accounts WITH (NOLOCK);  -- READ UNCOMMITTED hint
SELECT * FROM Accounts WITH (HOLDLOCK); -- SERIALIZABLE hint

Exam tip: SNAPSHOT vs READ COMMITTED SNAPSHOT

These are different features:

SNAPSHOT isolation is a session-level setting. The transaction sees a consistent snapshot from the moment it starts. Must be enabled with ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON.
READ COMMITTED SNAPSHOT (RCSI) changes the default READ COMMITTED behaviour to use row versioning instead of locks. Each statement sees data as of statement start (not transaction start). Enable with ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON.

RCSI is the most common choice for Azure SQL Database (it is the default). It reduces blocking without requiring application changes.

Blocking and deadlocks

Detecting blocking

-- Find blocked sessions
SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Resolving deadlocks

Deadlocks occur when two transactions each hold a lock the other needs. SQL Server automatically detects deadlocks and kills one transaction (the “victim”).

Prevention strategies:

Access objects in the same order across all transactions
Keep transactions short — do not hold locks during user interaction
Use SNAPSHOT isolation to eliminate read-write blocking
Add appropriate indexes — more targeted locks mean fewer conflicts

Scenario: Priya's deadlock at Vault Bank

Two concurrent transactions at Vault Bank:

Transaction A: UPDATE Accounts WHERE AccountId = 1, then UPDATE Accounts WHERE AccountId = 2
Transaction B: UPDATE Accounts WHERE AccountId = 2, then UPDATE Accounts WHERE AccountId = 1

Both run simultaneously. A locks Account 1 and waits for Account 2. B locks Account 2 and waits for Account 1. Deadlock.

Priya’s fix: ensure all transactions access accounts in ascending AccountId order. Now both transactions lock Account 1 first, then Account 2. No deadlock possible.

Question

What is the default isolation level in SQL Server?

Click or press Enter to reveal answer

Answer

READ COMMITTED. It prevents dirty reads (cannot see uncommitted changes) but allows non-repeatable reads and phantoms. In Azure SQL Database, READ COMMITTED SNAPSHOT (RCSI) is the default, which uses row versioning instead of locks.

Click to flip back

Question

How does SNAPSHOT isolation prevent blocking?

Click or press Enter to reveal answer

Answer

SNAPSHOT uses row versioning instead of locks. When a transaction reads data, it sees the version from when the transaction started — even if another transaction is actively modifying the same rows. Readers never block writers and writers never block readers.

Click to flip back

Question

What is the simplest way to prevent deadlocks?

Click or press Enter to reveal answer

Answer

Access tables and rows in the same consistent order across all transactions. If all code updates Account 1 before Account 2, no circular wait can occur. Also keep transactions as short as possible to minimise lock duration.

Click to flip back

Knowledge Check

Ingrid at Nordic Shield runs a reporting query that takes 3 minutes. During that time, it blocks data-entry staff from inserting new claims. What isolation level change would eliminate the blocking without risking dirty reads?

🎬 Video coming soon

Next up: Query Performance: Plans, DMVs, and Query Store — find and fix slow queries.