What Is a Deadlock in SQL Server?

A deadlock in SQL Server is two sessions each holding something the other one needs, and neither able to let go. Nobody can move. SQL Server notices, picks one, and kills it. That sounds harsh and it is the only way out.

The Narrow Corridor

Two people meet in a corridor only wide enough for one. Each is carrying a box the other needs. Each will hand theirs over as soon as they get the other. Both wait politely, forever.

That is a deadlock exactly. It is not a bug and nothing is broken. It is two reasonable pieces of work whose order happened to cross.

I Caused One

Two tables, one row each, and two sessions that touch them in opposite order. I ran this on SQL Server 2025.

-- session one
BEGIN TRANSACTION;
UPDATE dbo.A SET v = v + 1 WHERE id = 1;
WAITFOR DELAY '00:00:03';
UPDATE dbo.B SET v = v + 1 WHERE id = 1;
COMMIT;

-- session two, at the same time
BEGIN TRANSACTION;
UPDATE dbo.B SET v = v + 1 WHERE id = 1;
WAITFOR DELAY '00:00:03';
UPDATE dbo.A SET v = v + 1 WHERE id = 1;
COMMIT;

Session one takes A and wants B. Session two takes B and wants A. The delay is only there to make the timing reliable. This came back:

Msg 1205, Level 13, State 51
Transaction (Process ID 73) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.
Rerun the transaction.

Session two finished normally. Session one was rolled back and told to try again. Note the order of the two statements is the only thing that differs between them.

Not the Same as Blocking

People use the words interchangeably and they are different things.

Blocking is one session waiting for another. It resolves by itself when the first one commits. It can be slow and it is not an error.

Deadlock is a circle. It never resolves by itself, so SQL Server breaks it. You always get error 1205, and one transaction is always undone.

Blocking is a queue. A deadlock is a knot.

Who Gets Killed

SQL Server picks whichever transaction is cheapest to roll back, measured by how much log it has written. The one that has done less work loses.

So the small quick statement is the usual victim, not the big report that caused the problem. That is why the complaint always comes from the wrong place.

You can volunteer with SET DEADLOCK_PRIORITY LOW in a batch you would rather lose, which is worth doing in overnight jobs so they lose to real users.

Finding Them

SQL Server records deadlocks by default in the system health session, so you can look at ones that happened while nobody was watching.

SELECT CAST(target_data AS xml) AS system_health
FROM sys.dm_xe_session_targets AS st
JOIN sys.dm_xe_sessions AS s ON s.address = st.event_session_address
WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer';

Search that XML for xml_deadlock_report. Each one names both sessions, what each held, what each wanted, and the statements involved. It is not pretty to read and it tells you everything.

Stopping Them

Touch things in the same order everywhere. This is the big one. My two sessions deadlocked only because one did A then B and the other did B then A. Make every procedure touch tables in an agreed order and most deadlocks vanish.

Keep transactions short. A transaction that holds locks for three seconds has three seconds to collide with something. One that holds them for three milliseconds mostly does not.

Index what you update. Without a useful index, an UPDATE with a WHERE clause may lock far more rows than it changes, which turns an unlikely collision into a regular one.

Retry. Error 1205 literally says rerun the transaction. On a busy system you will never reach zero deadlocks, so the application should catch 1205, wait a moment, and try again. Applications that do this treat deadlocks as weather rather than emergencies.

A deadlock is not a failure of the database, it is two sensible jobs that met going opposite ways down the same corridor.

This post was rewritten from scratch in September 2026. The original, published on 2013-04-04, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.