SQL Server Deadlocks

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)

How to capture deadlock graph in SQL Trace?
DeadlockGraph-HowToCaptureInSQLTrace

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 

How the Deadlock graph looks like in SQL Profiler:
DeadlockGraph-CapturedInSQLTrace_Sample

How the deadlock looks when 1204 Traceflg is enabled?
DeadlockLog_1204Traceflag

How the deadlock looks when 1222 Traceflg is enabled?
DeadlockLog_1222Traceflag

Note:
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!

Reference:
http://msdn.microsoft.com/en-us/library/ms178104.aspx
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)
http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

-Vijred

Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s