Transaction Isolation and Concurrency
Understand how transactions interact — choose the right isolation level, prevent dirty reads and phantom rows, and resolve blocking and deadlocks.
When transactions collide
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
| 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 hintExam 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.
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?
Next up: Query Performance: Plans, DMVs, and Query Store — find and fix slow queries.