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.
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.
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
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
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'
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent);
@SQLWAREWOLF Deadlock Reporting
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 1.1, 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.
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.
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.