I had been talking about deadlocks few hundreds of times, worked on it few tens of times, questioned in most of the interviews. I will give an attempt to document Deadlock basics in this Blog! By Wiki definition, deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever does.
In SQL server, Deadlocks are not captured in logs by default (Traceflag is not required to capture in Profiler), Traceflags must be enabled to capture deadlock. 1204 is an option and 1222 is other traceflag which was introduced from SQL 2005 with better information!
How to Check, Enable, Disable Traceflags?
--How to verify which traces are already enabled on your SQL Server? DBCC TRACESTATUS() -- Traces enabled on specific session DBCC TRACESTATUS(-1) -- Global Traces -- How to Switch ON and OFF the traces – Traces can be enabled only on specific Session or globaly (-1 option indicates Global): -- Enable Traces DBCC TRACEON (1204, -1) DBCC TRACEON (1222, -1) -- Disable Traces DBCC TRACEOFF (1204, -1) DBCC TRACEOFF (1222, -1)
Simple Example to create a deadlock:
--First Session USE TEMPDB GO CREATE TABLE t1 (t1c1 INT) INSERT t1 VALUES(1) CREATE TABLE t2 (t2c1 INT) INSERT t2 VALUES(1) BEGIN TRAN UPDATE t1 SET t1c1 = 2 --Run in second connection USE tempdb BEGIN TRAN UPDATE t2 SET t2c1 = 2 select @@SPID GO UPDATE t1 SET t1c1 = 2 --Run in first connection select @@SPID GO UPDATE t2 SET t2c1 = 2 -- You will see the deadlock
From SQL 2012, Deadlock information can be captured from events into XML Format, save it in .xdl format to open in graphical format (in SSMS)
To my experience, the impact is minimal due to enabling these traces. I normally enable 1222 traceflag only!
Detailed information about Deadlocks: https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
I liked this blog which talks about analyzing deadlocks (Parallel thread Deadlocks)