Oct
4
2012

How are deadlocks created? (video)

Deadlocks occur when two processes both hold locks on resources, and also try to take locks on resources from the other process. This results in neither process being able to continue.

SQL Server resolves these situations automatically by terminating one of the processes, thus allowing the other(s) to proceed. This is done by an internal process called the Deadlock Monitor.

In this video demo, I demonstrate a basic sequence of events needed to create a deadlock. I also look at the locks that are held and requested by each process, and introduce you to reading a deadlock graph (which is what's captured from the Extended Events deadlock capturing system I presented last week).

(Sorry my cursor isn't visible in the video -- I blame the capture program I used. It won't happen again.)

 

If you want to reproduce the situation yourself, you can download the scripts here.

Sep
27
2012

Capture deadlocks 24/7 with Extended Events

Deadlocks can be notoriously difficult to reproduce in our development environments.

Most of the time:

  • There are multiple actual users involved with the problem.
  • The production workload, both in terms of volume and/or composition, can't be realistically reproduced in development either.
  • The tools we used to need to use to capture deadlocks (server-side trace, SQL Server Profiler) are prohibitively expensive to run all the time, or even for a short time, or we had to mess around with trace flags.

Here's where Extended Events come to the rescue, and really makes us able to be proactive to deal with deadlocks.

Extended Events allows us to capture deadlock graphs continuously, as they happen, 24 hours/day, 7 days/week, with very little performance overhead. Not only that, but the system we're going to set up is able to survive an instance restart! This is huge in terms of ease of management, and being able to have this information available when it's needed can be way more than half the battle.

Here is the meat of the script that sets up our event session for a SQL Server 2012 instance:

CREATE EVENT SESSION [DeadlockMonitor] ON SERVER 
	ADD EVENT sqlserver.xml_deadlock_report 
	ADD TARGET package0.event_file
	(
		SET
			FILENAME = N'\\CentralFileShare\MyTargetFile.xel',
			MAX_ROLLOVER_FILES = 0
	)
	WITH
	(
		EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
		MAX_DISPATCH_LATENCY = 15 SECONDS,
		STARTUP_STATE = ON
	);

ALTER EVENT SESSION [DeadlockMonitor] ON SERVER
	STATE = START;

Note that the database engine service account needs to be granted write access to the target location.

For ease of multi-server management, I recommend setting up a centralized file share to store all the output files. The full script was written with this in mind, and uses the server name as the file name automatically.

Security note: Deadlock graphs may contain sensitive information, such as data values in DML statements. The target location should be secured appropriately -- consider using subfolders in the target path to separate by server if a single folder isn't okay for your environment.

 

So, now that we've captured some deadlocks (yay?), how do we figure out what happened? Here is a first start. It reads the output and returns the event data (in this case, a deadlock graph) as XML. This script can be used for all types of Extended Event file targets, not just for deadlocks.

SELECT
	object_name,
	CAST(event_data AS xml) AS EventXml
	FROM
		sys.fn_xe_file_target_read_file
		(
			'.xel',
			'.xem', /* Required for 2008, optional for 2012 */
			NULL, NULL
		)

XPath can be used to further break things down, or the event data can be copied as text and saved as a .xdl file, which we can then open up in Management Studio to see the pretty deadlock graph. If you have a centralized management box, this script could be the basis of an Extended Events aggregation and reporting system. (Hopefully, you aren't dealing with that many deadlocks, but this type of system could be used for other purposes.)

In a future post, we'll look at how to analyze deadlock graphs.