Locking, Isolation Levels and Deadlocks – SQL Server | Lesson 25
Introduction
At 8:45 AM, forty class teachers save attendance at the same moment, the office prints fee receipts, and the principal opens the dashboard. They all touch the same tables.
SQL Server keeps them from damaging each other's work with locks. Locks are what make the "I" in ACID, isolation, work. They also cause the two problems this lesson is about: blocking, where one session waits for another, and deadlocks, where two sessions wait for each other forever.
By the end of this lesson, you will be able to:
- Explain shared, exclusive, and update locks.
- Reproduce blocking with two sessions and find who is blocking whom.
- Compare the isolation levels and the problems each one prevents.
- Explain why
NOLOCKreads can be wrong. - Reproduce a deadlock, read error 1205, and prevent and retry deadlocks.
Why this matters
"The page hangs every morning at 8:45" is almost never a slow server. It is usually one long transaction holding locks while dozens of others wait behind it.
Deadlocks look worse: users see "Transaction was deadlocked ... Rerun the transaction."
Without understanding locks, teams add NOLOCK everywhere and start showing wrong numbers
instead. With understanding, the fix is usually small.
The concept
Example: the main kinds of lock
| Lock | Taken by | Allows others to | Blocks others from |
|---|---|---|---|
| Shared (S) | Reading | Read too | Changing |
| Exclusive (X) | Changing (INSERT, UPDATE, DELETE) | Nothing | Reading (by default) and changing |
| Update (U) | Reading with intent to change (UPDLOCK) | Read | Taking another U or X lock |
Think of a reference book in the library. Many students can read it at the table (shared). A teacher who takes it to correct an error holds it alone (exclusive), and students wait until it is back.
Exclusive locks are held until the transaction ends. That is why Lesson 24 said: keep transactions short.
Example: the isolation levels
An isolation level decides how much a reading session sees of other sessions' unfinished work, and how long its own shared locks last.
| Level | Dirty read | Non-repeatable read | Phantom | Notes |
|---|---|---|---|---|
READ UNCOMMITTED | Possible | Possible | Possible | Same as NOLOCK |
READ COMMITTED (default) | Prevented | Possible | Possible | Readers wait for writers |
REPEATABLE READ | Prevented | Prevented | Possible | Holds read locks to the end |
SERIALIZABLE | Prevented | Prevented | Prevented | Most blocking |
SNAPSHOT | Prevented | Prevented | Prevented | Reads a saved version; no waiting |
- A dirty read sees a change that is later rolled back.
- A non-repeatable read gets a different value when reading the same row twice.
- A phantom is a new row that appears when the same query runs twice.
Read Committed Snapshot Isolation (RCSI) is a database setting that makes normal READ COMMITTED reads use saved versions, so readers stop waiting for writers. Many teams turn
it on for busy systems; it is not on by default in SQL Server.
Apply it to the SMS
These demos need two query windows in SSMS, both connected to SchoolDb. Every demo
rolls back, so no data changes.
Example: blocking and a dirty read
Window 1 updates Rajesh's phone and holds the lock for 15 seconds:
-- Lesson 25, window 1 of 2: hold a lock for 15 seconds.
-- Run this first, then run 025-blocking-session-2.sql in a second window within 15 seconds.
USE SchoolDb;
GO
BEGIN TRANSACTION;
UPDATE dbo.Guardians
SET Phone = '9876549999'
WHERE Id = 1; -- Rajesh's row is now locked until this transaction ends
WAITFOR DELAY '00:00:15'; -- pretend the transaction is slow
ROLLBACK TRANSACTION; -- undo the change; the lock is released
GO
Window 2, started within 15 seconds, reads the same row twice:
-- Lesson 25, window 2 of 2: read the locked row two ways.
-- Run this while 025-blocking-session-1.sql is still running.
USE SchoolDb;
GO
-- 1. READ UNCOMMITTED: does not wait, and reads a change that may never be saved (a dirty read).
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Phone AS DirtyPhone
FROM dbo.Guardians
WHERE Id = 1;
-- 2. READ COMMITTED (the default): waits until window 1 finishes, then reads the real value.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE @Started DATETIME2 = SYSDATETIME();
SELECT Phone AS CommittedPhone
FROM dbo.Guardians
WHERE Id = 1;
SELECT DATEDIFF(SECOND, @Started, SYSDATETIME()) AS SecondsWaited;
GO
The results in window 2:
| DirtyPhone |
|---|
| 9876549999 |
| CommittedPhone |
|---|
| 9876540101 |
| SecondsWaited |
|---|
| 13 |
The READ UNCOMMITTED read returned at once, with a phone number that was never saved:
window 1 rolled it back. The READ COMMITTED read waited 13 seconds, until window 1
finished, then returned the real number. Your wait depends on how fast you switched windows.
Example: who is blocking whom
While window 2 waits, run this in a third window:
-- Lesson 25: run in a third window while window 1 holds its lock.
-- Shows which session is waiting, and which session it is waiting for.
SELECT
r.session_id AS WaitingSession,
r.blocking_session_id AS BlockedBy,
r.wait_type AS WaitType,
r.wait_time / 1000 AS SecondsWaiting,
t.text AS WaitingQuery
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;
GO
| WaitingSession | BlockedBy | WaitType | SecondsWaiting | WaitingQuery |
|---|---|---|---|---|
| 86 | 85 | LCK_M_S | 3 | SELECT [Phone] [CommittedPhone] FROM [dbo].[Guardians] WHERE [Id]=@1 |
Session 86 waits for a shared lock (LCK_M_S) that session 85 blocks. Your session numbers
will differ. In production, this query is the first thing to run when users say "it hangs".
Example: a real deadlock
Window 1 locks guardian 1 and then wants guardian 2. Window 2 locks guardian 2 and then wants guardian 1:
-- Lesson 25, deadlock window 1 of 2: lock guardian 1, then ask for guardian 2.
-- Run this, then run 025-deadlock-session-2.sql in a second window within 5 seconds.
USE SchoolDb;
GO
BEGIN TRANSACTION;
UPDATE dbo.Guardians SET Phone = Phone WHERE Id = 1; -- lock A
WAITFOR DELAY '00:00:05';
UPDATE dbo.Guardians SET Phone = Phone WHERE Id = 2; -- wants lock B
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
GO
-- Lesson 25, deadlock window 2 of 2: lock guardian 2, then ask for guardian 1.
-- Each window now waits for the other: SQL Server picks one as the deadlock victim.
USE SchoolDb;
GO
BEGIN TRANSACTION;
UPDATE dbo.Guardians SET Phone = Phone WHERE Id = 2; -- lock B
WAITFOR DELAY '00:00:05';
UPDATE dbo.Guardians SET Phone = Phone WHERE Id = 1; -- wants lock A
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
GO
Start window 1, then window 2 within 5 seconds. Each now waits for a lock the other holds, so neither can ever finish. SQL Server's deadlock monitor notices within a few seconds, picks one session as the victim, and rolls it back:
Msg 1205, Level 13, State 51, Line 8
Transaction (Process ID 86) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The other window finishes normally. Which one is chosen can change between runs. Afterwards, both phones are unchanged: the demos update a column to its own value and roll back.
Example: preventing deadlocks
| Technique | Why it helps |
|---|---|
| Touch tables and rows in the same order everywhere | Two sessions can then only queue, never cross |
| Keep transactions short | Locks are held for less time, so fewer overlaps |
| Add the right indexes | Fewer rows are read and locked (Lesson 26) |
| Use RCSI for read-heavy systems | Readers stop taking shared locks |
Lock what you will change early, with UPDLOCK | Two sessions cannot both read, then both try to write |
usp_RecordFeePayment reads the invoice WITH (UPDLOCK) for the last reason. Two
payments for one invoice queue one behind the other instead of deadlocking.
Example: retrying a deadlock victim
Deadlocks can be made rare, but not impossible. The standard answer is to retry the whole transaction a few times when error 1205 appears:
DECLARE @Attempt INT = 1;
WHILE @Attempt <= 3
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.Guardians SET Phone = Phone WHERE Id = 1;
UPDATE dbo.Guardians SET Phone = Phone WHERE Id = 2;
COMMIT TRANSACTION;
BREAK; -- success: leave the loop
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
IF ERROR_NUMBER() = 1205 AND @Attempt < 3
BEGIN
SET @Attempt += 1;
WAITFOR DELAY '00:00:00.200'; -- a short pause, then try again
END
ELSE
BEGIN
THROW; -- not a deadlock, or out of attempts
END;
END CATCH;
END;
In C#, a retry is the same small loop around the whole operation: catch error 1205, wait a moment, and run it again, two or three times at most. For one known hot spot, Lesson 83 shows another answer: an application lock that makes the second run wait instead of deadlocking. Retry only whole transactions, and only for errors that are safe to repeat.
Common mistakes
NOLOCKeverywhere "for speed". Reports show rows that were rolled back, or count rows twice. Fix: fix the long transaction or use RCSI.- Long transactions with user waits inside. Everyone queues. Fix: no pauses inside transactions.
- Different code paths updating tables in different orders. Deadlocks follow. Fix: agree one order, such as invoice first, then payments.
- Not retrying error 1205. Users see a scary message for a temporary problem. Fix: retry the whole transaction a few times.
- Choosing
SERIALIZABLE"to be safe". Blocking explodes. Fix: use the lowest level that gives correct results.
In real projects
The most common performance "fix" I remove in code reviews is WITH (NOLOCK). A fee
report with NOLOCK once showed a payment that had been rolled back a second later, and
the accountant issued a refund for money that never arrived.
On busy school systems, I usually turn on Read Committed Snapshot Isolation after
testing. Teachers saving attendance stop blocking the principal's dashboard, and nobody
reads uncommitted data. The cost is some extra space in tempdb for row versions, which
is a good trade on almost every system I have seen.
Best practices
- Keep transactions short, and never wait for people inside them.
- Access shared tables in the same order in every procedure.
- Avoid
NOLOCKfor anything that must be correct. - Consider RCSI for systems with many readers and writers.
- Retry deadlock victims (error 1205) a few times, with a short pause.
Recap
- Shared locks are for reading, exclusive locks for changing; exclusive locks last until commit.
- Blocking is one session waiting for another's lock;
sys.dm_exec_requestsshows who. - Isolation levels trade correctness against waiting;
READ COMMITTEDis the default. READ UNCOMMITTEDandNOLOCKcan return data that is never saved.- A deadlock is two sessions waiting for each other; SQL Server kills one with error 1205.
- Prevent deadlocks with consistent order, short transactions, and indexes; retry the rest.
Homework
Library module task: Two librarians issue the last available copy of a popular book
at the same moment. Using usp_IssueBook from Lesson 22, explain in four or five
sentences what happens, and why the UPDLOCK hint matters.
A correct result looks like: an explanation that one session gets the lock and issues the copy, the other waits, then sees the copy is no longer available and gets error 50020.
Show a sample answer
Both sessions start a transaction and read the copy with UPDLOCK. Only one can hold the
update lock, so the second waits.
The first inserts the issue; the Lesson 23 trigger sets the copy to Issued, and the
transaction commits. The second session's read now sees Issued, so it throws error 50020.
Without UPDLOCK, both could read Available at the same moment and both would insert an
issue. The filtered unique index from Lesson 12 would then refuse the second insert, but
with a less friendly error.
Quiz
Which isolation level is SQL Server's default?
Show answer
Answer: B. READ COMMITTED READ COMMITTED is the default; readers wait for writers' changes to commit.
In the demo, why did the READ UNCOMMITTED read return 9876549999?
Show answer
Answer: B. It read a change that was later rolled back: a dirty read READ UNCOMMITTED reads uncommitted changes, which may never be saved.
What is a deadlock?
Show answer
Answer: B. Two sessions each waiting for a lock the other holds Neither can continue, so SQL Server ends one of them.
Which error number tells you a session was chosen as the deadlock victim?
Show answer
Answer: B. 1205 Error 1205 means: rerun the transaction.
Which change most directly prevents deadlocks between two procedures?
Show answer
Answer: B. Updating tables in the same order in both procedures With the same order, the second session simply waits instead of crossing.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.
Fresher (0-2 yrs)
F What is the difference between a shared lock and an exclusive lock? concept
A shared lock is taken for reading; many sessions can hold it at once. An exclusive lock is taken for changing data; only one session can hold it, and it blocks other writers and, by default, readers. Exclusive locks last until the transaction ends.
F What is SQL Server's default isolation level? concept
READ COMMITTED. Readers see only committed data, and wait for writers that hold locks on
the rows they need. It prevents dirty reads but still allows non-repeatable reads and phantoms.
F What is a deadlock? concept
Two sessions each hold a lock the other needs, so neither can continue. SQL Server detects it within seconds, chooses one session as the victim, rolls it back, and returns error 1205, letting the other finish.
Mid (3-5 yrs)
M What is a dirty read, and which setting allows it? concept
A dirty read sees a change that another transaction has not committed and may roll back.
READ UNCOMMITTED and the NOLOCK hint allow it. In the SMS demo, a NOLOCK read returned a
phone number that was rolled back a moment later.
M How do you find which session is blocking another? coding
Query sys.dm_exec_requests for rows where blocking_session_id <> 0, joined to
sys.dm_exec_sql_text for the waiting query. It shows who waits, who blocks, the wait type
such as LCK_M_S, and for how long.
M What is Read Committed Snapshot Isolation (RCSI)? concept
A database setting that makes normal READ COMMITTED reads use the last committed version
of each row instead of waiting for writers' locks. Readers stop blocking on writers, and
still never see uncommitted data. The cost is row versions kept in tempdb.
Senior (6-9 yrs)
S Users get deadlock errors when saving attendance and fees. How do you reduce them? scenario
Capture the deadlock details (from the system_health session) to see which statements and resources are involved. Then make every code path touch tables and rows in the same order, keep transactions short, add indexes so fewer rows are locked, and retry error 1205 a few times.
How to answer: Start from evidence (the deadlock graph), then apply the prevention techniques in order of
impact. Explain why retrying is still needed, and why NOLOCK is not a fix.
S A developer added WITH (NOLOCK) to every report query to stop blocking. What is your response? scenario
NOLOCK can return uncommitted rows, skip rows, or read rows twice when pages move, so reports can show wrong numbers. Find the real cause of the blocking, usually long transactions or missing indexes, and consider RCSI so reports read committed versions without waiting.
How to answer: Acknowledge the pain, explain the correctness risks with a concrete example, and offer better options. Mention where NOLOCK is acceptable: rough monitoring queries where accuracy does not matter.
Architect / Lead (10+ yrs)
A How would you choose isolation settings for an SMS serving 1,000 schools? design
Turn on RCSI so reads do not block on writes, keep READ COMMITTED as the default, and use
stronger locking only where needed, such as UPDLOCK on an invoice during payment. Test
under realistic concurrency, and monitor blocking and deadlocks in production.
How to answer: Weigh correctness, concurrency, and tempdb cost. Explain why SERIALIZABLE everywhere does not scale, and why targeted locking for money operations does. Mention load testing at the 8:45 AM peak and monitoring.
Resources
- Code for this lesson: git tag
lesson-25-isolation-deadlocks(code repository coming soon) - Files:
queries/025-blocking-session-1.sql,queries/025-blocking-session-2.sql,queries/025-who-is-blocking.sql,queries/025-deadlock-session-1.sql,queries/025-deadlock-session-2.sql - Video: coming soon
What's next
Fewer rows read means fewer locks and faster queries. Next, indexes, measured on the 600,000-row attendance table. Lesson 26: Indexes
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.