Deadlocking – Deciding which child gives up what they want

As we have spoken about before, parents of siblings or the siblings themselves understand what it is like for a parent to have to resolve a squabble.  Recently, my daughters were having burgers for dinner.  My youngest was sitting at the table with the ketchup and my eldest had both hers and her sister’s burger.  The youngest said she wanted her burger, the eldest wanted the ketchup.  Neither would give the other what they wanted.

Of course, being the Dad that I am, I attempted to teach a little DBA deadlocking to them.  They both told me that I was speaking Martian.

This is essentially a deadlock in real life.  In SQL Server, or any RDBMS, deadlocks are NOT normal and should not be accepted as such.

Deadlock Defined

A deadlock occurs when there are two separate processes with separate transactions.  Each transaction is holding a resource the other needs.  Lets look at this scenario.  Sister_One has a lock on Tablet_One and has requested a lock on Headphone_Two.  Sister_Two has a lock on Headphone_Two and has requested a lock on Tablet_One.  Both processes are waiting for the other one.  The SQL Server Database Engine(aka. The Parent) must choose which process to be the deadlock victim as neither task can continue.

The Database Engine will check for deadlocks in intervals of 5 seconds as a default.  The transaction that is chosen as the victim first by deadlock priority.  The DEADLOCK_PRIORITY may be set on the session explicity.  In this case, the Database Engine will choose the victim with the lowest DEADLOCK_PRIORITY.  Alternatively, the victim is the one that SQL Server determines is the least expensive to rollback by default if the deadlock priority was not set or both objects have the same priority.

Reporting on Deadlocks

There are various ways to report on deadlocks.  The simplest is the using the trace flag 1222.  This will record the deadlock in the SQL Server Error Log in XML format.  To turn the trace on or off, use the following.  1222 is a global only flag.

DBCC TRACEON(1222,-1);
DBCC TRACEOFF(1222,-1);

Once the trace flag is set, when a deadlock occurs, you can read from the SQL Server Error Log using the following:

if	object_id('tempdb..#tmpErrorLog') is not null
	drop table #tmpErrorLog

create	table #tmpErrorLog
	(
	logDate		datetime	,
	processInfo	varchar(56)	,
	logTxt		varchar(max)	,
	elID		int identity(1,1)
	)
	
create	clustered index ix_tmpErrorLog_logDate on #tmpErrorLog(logDate)

insert	into #tmpErrorLog(logDate, processInfo, logTxt)
exec	sp_readerrorlog 0

select	*
from	#tmpErrorLog
order	by 1 desc

Another way to report on deadlocks is to use the default extended event from the ring buffer.  I tend to avoid the ring buffer as I find it less than dependable.

SELECT	XEvent.query('.') XEvent
FROM	(
	SELECT	CAST([target_data] AS XML) TargetData
	FROM	sys.dm_xe_session_targets st
		INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
	WHERE	s.[name] = N'system_health'
		AND st.[target_name] = N'ring_buffer'
	) Data
	CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent);

@SQLWAREWOLF Deadlock Reporting

I have created a full deadlock reporting structure that I would like to share.  This page and script have had some feedback by Mr. Wayne Sheffield(b|t).  Thank you so much my friend.

This process will use a defined extended event to record deadlocks to a configurable disk location.  First of all, the script uses a database called DBA.  In any instance, the DBA should have a database for which they can store data/objects specifically for database administration.

Three tables are created; general_configuration, deadlock_report_main and deadlock_report_detail.  A function is created to check for the existence of the extended events files that are configured in general configuration.

An explicit extented event is created to track and store deadlock information into an xml file structure.  This is defined and the location of the files is based on the value configured in the general_configuration table.

There are a few stored procedures created:

• uspRecordDeadlocks – Reads and parses the xml into Deadlock_Report_Main and Deadlock_Report_Detail.  This will record the deadlock winner and victim.  This will also record the procedure, query, line numbers and various other information.
• uspAnalyzeDeadlocks – This reports on the recorded data.  Paring the winner and loser combinations.
• uspAnalyzeDeadlocks_Obfuscate – within uspAnalyzeDeadlocks, there is an optional parameter to obfuscate parameters and values within where clauses, join statements, or parameters passed to a stored procedure.
• uspCleanDeadlockHistory – This is used to clean the reporting tables so that the data is kept trim.

The Record_Deadlocks job will read the events and parse the XML into a reporting structure by calling uspRecordDeadlocks.  This will also trim the history by calling uspCleanDeadlockHistory.

The final report example is below:

exec	uspAnalyzeDeadlocks @reportOption = 2, @obfuscate = 1

 

THIS IS HAS NOW BEEN UPDATED TO VERSION 2.0,  BUT STILL MAY BE BUGGY! To be clear, these scripts and procedures are written by myself and may be shared with that copyright knowledge.  They are AS IS and no warranty or support is included.  I am NOT liable for ANY damages using these scripts may cause.  Use at your own risk and always test THOROUGHLY on a development system before implementing in production.

Resolving Deadlocks

What do you do now that you’ve found your deadlocks parings?  You resolve them.  The best way to make deadlocks “not happen” is to make the query faster.  In my experience(this may not always be true), most deadlocks occur when there is inefficient indexing, if any at all, or poorly written queries.

• Make sure that your queries are properly tuned.
• Use the appropriate indexes as necessary.
• Use efficient code(See my common coding mistakes series).
• Run transactions in small batches.
• Use a lower isolation level such as Read_Committed which holds locks for a shorter duration.
• Use Snapshot Read Committed Isolation for row versioning.
  • Remember that this comes at the cost of high tempdb usage.

 

Conclusion

Applying an efficient thought process to SQL Server and processing data quickly will hopefully avoid deadlocks.  Unfortunately, deadlocks do occur.  Now we understand what deadlocks are and how to report on them.  This is the first step in resolving deadlocks.

As a parent of siblings or as a sibling, you are more experienced than you may realize in resolving deadlocks.  You’ve had much practice.

Resources:

https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx