To Find Deadlocks, I collect the graph of competing sessions and resources. The cycle distinguishes a deadlock from ordinary blocking.
John Sterrett contributed Notes from the Field episode 86. I introduced it by recalling my early frustration with unexplained deadlocks. John’s explanation remains credited here.
The original two-session example
Session1 changes table1 while session2 changes table2, and both keep their transactions open. Session1 then waits for table2. If session2 also requests the locked table1, the two sessions form a cycle. SQL Server chooses a victim and rolls back its transaction so the other can continue.
Use the available deadlock evidence
On SQL Server, the system_health Extended Events session normally captures xml_deadlock_report events. Inspect the actual session and retained target files rather than assuming every old graph is still available. Read victim, process, and resource details before choosing a correction.
John’s original demonstration used SQL Server 2012/2014-era SSMS. The cycle explains why the graph matters. Current UI details and retained event files depend on the installed build.
A controlled reproduction needs separate sessions, scratch objects and reliable rollback. I keep that experiment away from shared business data.
Reference: System health Extended Events session.
Related reading
A blocking snapshot is not a deadlock graph, it is evidence of a wait at a particular moment.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
