Creating a Deadlock

Previously we have looked at how to capture a deadlock, but how do we go about creating a deadlock?

I think the following is the simplest method:

1. Create two tables with a single row in each and update a row in the first table (note transaction is started with BEGIN TRAN, but not committed.)

USE [tempdb]
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J1')
DROP TABLE [J1];
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J2')
DROP TABLE [J2];
CREATE TABLE [J1]([ID] INT);
CREATE TABLE [J2]([ID] INT);
INSERT INTO [J1]VALUES (1);
INSERT INTO [J2]VALUES(1);
GO
BEGIN TRAN
UPDATE [J1] SET [ID] = 1
--UPDATE [J2] SET [ID] = 1

2. Open a query in a new window and run:

USE [tempdb]
BEGIN TRAN
UPDATE [J2] SET [ID] = 1
UPDATE [J1] SET [ID] = 1

3. Finally in the query window opened in step 1. run the commented code by highlighting everything after the comment marks then pressing F5.

--UPDATE [J2] SET [ID] = 1

This will result in our first window being the victim of a deadlock:

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

Note that the second query will have an open transaction, which you can either ROLLBACK or COMMIT.

In step 1 (spid 53) a lock was taken on the row of table J1
In step 2 (spid 54) a lock was taken on the row in table J2, and then it tries to take a lock on J1 but has to wait as the first query already has it locked.
In step 3 (spid 53) the first query attempts to lock the row that the second query has already locked, while holding a lock on the row that the second query wants.

ExampleDeadlock.xdl save this file to your desktop stripping off the .txt extension to leave .xdl which will allow opening in SSMS.

SQL Server Locks

Here is a simple script to get some more detailed information about locking, which I have added to over the last few years.

--http://jongurgul.com/blog/sql-server-locks
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
 dtl.[request_session_id] [SessionID]
,DB_NAME(dtl.[resource_database_id])  [DatabaseName]
,dtl.[request_status] [Status]
,dtl.[resource_type] [Resource]
,CASE 
WHEN [resource_type] =   'OBJECT'      THEN OBJECT_NAME(dtl.[resource_associated_entity_id],dtl.[resource_database_id])
WHEN [resource_type] =   'DATABASE'    THEN DB_NAME(dtl.[resource_database_id])
WHEN [resource_type] IN ('KEY','PAGE','RID') THEN pin.[ObjectName]
ELSE CAST(dtl.[resource_associated_entity_id] AS VARCHAR(MAX))
END [ResourceName]
,dtl.[resource_description] [ResourceDescription]
,dtl.[request_mode] [Mode]
,pin.[type_desc] [Type]
,QUOTENAME(pin.[ObjectSchemaName]) [ObjectSchemaName]
,QUOTENAME(pin.[ObjectName]) [ObjectName]
,QUOTENAME(pin.[IndexName]) COLLATE DATABASE_DEFAULT [IndexName]
,dtl.[resource_description] [ResourceDetail]
,CASE 
WHEN [resource_type] IN ('KEY','RID')
THEN N'SELECT * FROM '+QUOTENAME(DB_NAME(dtl.[resource_database_id]))
+'.'+QUOTENAME(pin.[ObjectSchemaName])
+'.'+QUOTENAME(pin.[ObjectName])
+N' WITH(NOLOCK'+
+COALESCE (',INDEX('+QUOTENAME(pin.[IndexName]) COLLATE DATABASE_DEFAULT +')','')
+') WHERE %%LOCKRES%% = '''
+RTRIM(dtl.[resource_description])+''''
WHEN [resource_type] IN ('PAGE') THEN 'DBCC PAGE('''+DB_NAME(dtl.[resource_database_id])+''','+RTRIM(REPLACE(dtl.[resource_description],':',','))+',3) WITH TABLERESULTS'
ELSE NULL
END COLLATE DATABASE_DEFAULT [Row/Page]--Performance will be poor if the table is large.
--,es.[original_login_name],es.[login_name]
FROM   
(
	SELECT i.[object_id],d.[name] [DatabaseName],d.[database_id],p.[hobt_id],i.[name] [IndexName],i.[type_desc]
	,OBJECT_SCHEMA_NAME(i.[object_id],d.[database_id]) [ObjectSchemaName]
	,OBJECT_NAME(i.[object_id],d.[database_id]) [ObjectName]
	FROM sys.partitions p 
	INNER JOIN sys.indexes i
	ON p.[object_id] = i.[object_id]
	AND p.[index_id] = i.[index_id]
	CROSS APPLY (SELECT * FROM sys.databases WHERE database_id = DB_ID()) d
		UNION ALL
		SELECT i.[object_id],d.[name] [DatabaseName],d.[database_id],p.[hobt_id],i.[name] [IndexName],i.[type_desc]
		,OBJECT_SCHEMA_NAME(i.[object_id],d.[database_id]) [ObjectSchemaName]
		,OBJECT_NAME(i.[object_id],d.[database_id]) [ObjectName]
		FROM tempdb.sys.partitions p 
		INNER JOIN tempdb.sys.indexes i
		ON p.[object_id] = i.[object_id]
		AND p.[index_id] = i.[index_id]
		CROSS APPLY (SELECT * FROM sys.databases WHERE database_id = DB_ID('tempdb')) d
) pin
RIGHT OUTER JOIN sys.dm_tran_locks dtl ON pin.[database_id] = dtl.[resource_database_id]
AND
((pin.[hobt_id] = dtl.[resource_associated_entity_id] OR pin.[object_id] = dtl.[resource_associated_entity_id]))
--LEFT OUTER JOIN sys.dm_exec_sessions es ON dtl.[request_session_id] = es.[session_id]
WHERE 1=1
AND dtl.[request_mode] <> 'Sch-S'
AND dtl.[request_mode] <> 'S' 
ORDER BY pin.[type_desc],dtl.[request_mode]

Capturing Deadlocks

So, deadlocks.

“A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.”

http://msdn.microsoft.com/en-us/library/ms178104.aspx

How to capture deadlocks?

How many ways can you capture a deadlock? Lots! Luckily I do not see a lot of deadlocks in my environment, but that does not mean that I am not still looking for them. Remember it is always important to understand why they are happening and try to eliminate the root cause(s).

1. Trace flags

If we enable Trace Flags 1204 and/or 1222 we can get the output from the deadlock in our sql log. I prefer a nice image of the deadlock so I do not tend to use these trace flags, but they are there if you ever need to troubleshoot a new environment in a hurry. Although I tend to have to remind myself to read bottom to top when looking at them in the log.

DBCC TRACEON (1204,1222,-1)
GO
DBCC TRACESTATUS(-1);--Show global trace flags
GO

2a. Profiler Trace

Profiler Trace can easily be set up using the the gui to capture the deadlocks and write them out to files, and as long as we are quite selective then a trace can be a good way to capture them.

2b. Manual Profiler Trace with t-sql

Not really different to the above, just that we can set up a trace programmatically as I have shown previously:

--http://jongurgul.com/blog/capturing-deadlocks/
DECLARE @on BIT
SET @on=1
DECLARE @maxsize BIGINT,@tracefile NVARCHAR(256);
SET @maxsize = 20
SET @tracefile = (SELECT LEFT([path],LEN([path])-CHARINDEX('\',REVERSE([path])))+ '\DeadLock' FROM sys.traces WHERE [is_default] = 1) -- We shall use the path of the default trace
SELECT @tracefile
DECLARE @trace_id INT
EXEC sp_trace_create @trace_id output,2,@tracefile ,@maxsize --The 2 means the file will roll over

EXEC sp_trace_setevent @trace_id,148,1,@on --Deadlock graph: TextData
EXEC sp_trace_setevent @trace_id,148,4,@on --Deadlock graph: TransactionID
EXEC sp_trace_setevent @trace_id,148,11,@on --Deadlock graph: LoginName
EXEC sp_trace_setevent @trace_id,148,12,@on --Deadlock graph: SPID
EXEC sp_trace_setevent @trace_id,148,14,@on --Deadlock graph: StartTime
EXEC sp_trace_setevent @trace_id,148,26,@on --Deadlock graph: ServerName
EXEC sp_trace_setevent @trace_id,148,41,@on --Deadlock graph: LoginSid
EXEC sp_trace_setevent @trace_id,148,51,@on --Deadlock graph: EventSequence
EXEC sp_trace_setevent @trace_id,148,60,@on --Deadlock graph: IsSystem
EXEC sp_trace_setevent @trace_id,148,64,@on --Deadlock graph: SessionLoginName

EXEC sp_trace_setstatus  @trace_id,1 --Start the Trace

 3a. WMI with a SQL Alert

Easy enough to set this up and Microsoft have given a good example of how to use WMI and SQL Alerts. Worth a mention that this is reliant on the SQL job agent.

3b. WMI with a event watcher (Powershell)

Now I doubt you will ever use this next snippet, but I thought I would write it just to show how easy it is. Note that I am only waiting for the next deadlock, but you can easily change this if you wanted. Also the path needs to reflect the instance you wish to set this watcher up on. e.g. MSSQLSERVER is the default instance.

Depending on OS and your security requirements you may need to tweak permissions or run this as Admin.

See file Deadlock_WMISQLAlert.ps1

4. Using Service Broker and Event notifications

This is a good general solution as you can wait for the event you want and respond exactly how you would like.

One of the first solutions I found using this method even sends you an email with the deadlock as an attachment. Which I have in the past implemented on SQL 2005 instances and it works well, although I did have some problems with truncation of the deadlock xml.

http://www.resquel.com/ssb/2009/03/02/DeadlockAlertsThroughEventNotification.aspx

5. Using the default extended event system_health on SQL2008 onwards

My preferred solution with SQL 2008 onwards; and one which provides the detail without the extra overhead of wmi/trace is the default extended event “system health”, which can be queried via:

--http://jongurgul.com/blog/capturing-deadlocks/
DECLARE @target_data XML
SELECT @target_data = CAST([target_data] as XML)
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'

SELECT
 x.y.query('./data/value/deadlock') [Deadlock_SaveAs_File.xdl]
,x.y.value('(@timestamp)[1]', 'datetime') [DateTime]
FROM (SELECT @target_data) [deadlock]([target_data])
CROSS APPLY [target_data].nodes('/RingBufferTarget/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="xml_deadlock_report"]') = 1
ORDER BY x.y.value('(@timestamp)[1]', 'datetime')

 

All I have to do is copy the xml in to a filename.xdl file and ssms will open it up for me. It is worth a mention that the target for this default extended event is the ring_buffer which will cycle out the older events, as new ones come in.

If you want to know more about extended events, or how to easily set new ones up then go and have a read of Jonathan Kehayias @sqlpoolboy article(s) here XEvent.

Also go and have a look at his great tool while you are at it  SQL Server 2008 Extended Events SSMS Addin

Which way will you choose?

Well if you are on a new SQL2008+ environment, then extended events is the way to go as it solves the problem and does so with practically no effort on your part.

If extended events are not available then you have to make a choice, which is basically do I want to run a trace or spend time writing something to intercept them. A trace is obviously easy to set up and if you have older environments it works no matter which version of SQL you are using.

Writing something to intercept them will require that you add dependencies to either service broker and event notifications, or sql alerts which relies on sql job agent and WMI. Plus WMI carries with it additional performance implications.