Deadlocks in SQL Server usually occur when users try to place exclusive locks on each other’s objects. For example, a database user Bob first acquires an exclusive lock on Sales table and then attempts to place an exclusive lock on Orders table. Another database user Joe already has an exclusive lock on Orders table, and he tries to acquire an exclusive lock on Sales table. This creates a deadlock situation because both Bob and Joe processes are waiting for each other’s objects. In SQL Server, the exclusive locks held by users will not clear until one user process completes or times out.
Luckily, SQL Server provides a variety of different options to monitor deadlocks. This includes Trace flags 1204 and 1222, SQL Profiler trace events, Extended Events and Event Notifications. Of these options Extended Events are the most efficient, lowest impact method for capturing deadlock information. Extended Events infrastructure is built directly into SQL Server and so can easily be managed with T-SQL. For more information, see SQL Server Extended Events.
In this article, we will look at the procedure, to capture deadlocks on SQL Server using Extended Events.
Setup
Note: For the purpose of this article demo, we will use OUTLANDER database.
We can use Extended Events “sqlserver.lock_deadlock” event to monitor deadlocks. This event occurs when an attempt to acquire a lock is cancelled for the victim of a deadlock. For more information about sqlserver.lock_deadlock event, query the sys.dm.xe_objects and dm_xe_object_columns dynamic views as follow:
--Displays information about lock_deadlock event SELECT * FROM [sys].[dm_xe_objects] WHERE [name] = 'lock_deadlock' --Displays the schema information about the lock_deadlock event. SELECT * FROM [sys].[dm_xe_object_columns] WHERE [object_name] = 'lock_deadlock';
Let’s create an EVENT SESSION that will listen for a sqlserver.lock_deadlock event within the OUTLANDER database. To do so, execute the following script:
--Creating event session CREATE EVENT SESSION CaptureDeadlocks_On_OUTLANDER_Database ON SERVER ADD EVENT sqlserver.lock_deadlock (ACTION (sqlserver.sql_text ,sqlserver.database_id ,sqlserver.client_app_name) WHERE sqlserver.database_id = 10) ADD TARGET package0.asynchronous_file_target (SET filename = N'D:\Deadlocks\CaptureDeadlocks_On_OUTLANDER_Database.xel' ,metadatafile = N'D:\Deadlocks\CaptureDeadlocks_On_OUTLANDER_Database.mta' ,max_file_size = 10 ,max_rollover_files = 10); GO --Starting event session ALTER EVENT SESSION CaptureDeadlocks_On_OUTLANDER_Database ON SERVER STATE = START GO
For more on the extended events syntax, please refer to CREATE EVENT SESSION (Transact-SQL).
Note: For the purpose of this example we are using asynchronous security audit file target. To view information of about supported targets, query the sys.dm_xe_session_targets dynamic management view.
Next, we should verify the creation of the EVENT SESSION by navigating in Object Explorer as follows:
SQL Server > Management > Extended Events
Testing
To test the “CaptureDeadlocks_On_OUTLANDER_Database” EVENT SESSION, we will create the deadlock situation using two user connections. To do that, open two query windows in SQL Server Management Studio and use the following Transact-SQL code as per instructions below:
In the first query window, execute the following code against OUTLANDER database:
USE [OUTLANDER] GO BEGIN TRAN UPDATE [OUTLANDER].[Sales].[Contact] SET [Address2] = 'Flat 1A' WHERE [FirstName] = 'Paul' AND [ContactID] = 1 -- Wait 5 secs to set up deadlock condition in other window WAITFOR DELAY '00:00:05' UPDATE [OUTLANDER].[Sales].[Contact] SET [Address2] = 'Flat 1B' WHERE [FirstName] = 'Sally' AND [ContactID] = 2 COMMIT GO
While the code in first query window is running, switch to second query window and then execute the following code against OUTLANDER database:
USE [OUTLANDER] GO BEGIN TRAN UPDATE [OUTLANDER].[Sales].[Contact] SET [Address2] = 'Apt. 1B' WHERE [FirstName] = 'Sally' AND [ContactID] = 2 -- Wait 5 secs to set up deadlock condition in other window WAITFOR DELAY '00:00:05' UPDATE [OUTLANDER].[Sales].[Contact] SET [Address2] = 'Apt. 1A' WHERE [FirstName] = 'Paul' AND [ContactID] = 1 COMMIT GO
This will create a deadlock situation, SQL Server will choose a deadlock victim. For example, when I executed the above scripts, the script in first query window updated two 2 row(s) whereas the script in the second query window failed with the following error message:
Msg 1205, Level 13, State 51, Line 4 Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
When this is the case, an event will be raised and sent the deadlock info to our “package0.asynchronous_file_target” target.
Viewing Deadlock Data from Event Data file
We can use the fn_xe_file_target_read_file dynamic management function to read the deadlock info from event data file as this function allows us to read files that are created by the extended events asynchronous file target. The following script will read the data from our event data file :
DECLARE @xel_filename varchar(256) ,@mta_filename varchar(256) SET @xel_filename = N'D:\Deadlocks\CaptureDeadlocks_On_OUTLANDER_Database_0_129905706818930000.xel' SET @mta_filename = N'D:\Deadlocks\CaptureDeadlocks_On_OUTLANDER_Database.mta' SELECT CONVERT (xml, [event_data]) AS [Event Data] FROM [sys].[fn_xe_file_target_read_file](@xel_filename ,@mta_filename ,NULL ,NULL)
Below is the XML for the deadlock, which occurred during our testing.
From this XML, we can obtain important information such as lock ID and type, SQL Text, and database id.
<event name="lock_deadlock" package="sqlserver" timestamp="2012-08-27T20:14:08.013Z"> <data name="resource_type"> <value>7</value> <text>KEY</text> </data> <data name="mode"> <value>4</value> <text>U</text> </data> <data name="owner_type"> <value>1</value> <text>Transaction</text> </data> <data name="transaction_id"> <value>391678</value> </data> <data name="database_id"> <value>10</value> </data> <data name="lockspace_workspace_id"> <value>0x0000000265810770</value> </data> <data name="lockspace_sub_id"> <value>1</value> </data> <data name="lockspace_nest_id"> <value>1</value> </data> <data name="resource_0"> <value>64</value> </data> <data name="resource_1"> <value>1805451520</value> </data> <data name="resource_2"> <value>709498360</value> </data> <data name="deadlock_id"> <value>45</value> </data> <data name="object_id"> <value>0</value> </data> <data name="associated_object_id"> <value>72057594042122240</value> </data> <data name="duration"> <value>3759000</value> </data> <data name="resource_description"> <value /> </data> <data name="database_name"> <value /> </data> <action name="client_app_name" package="sqlserver"> <value>Microsoft SQL Server Management Studio - Query</value> </action> <action name="database_id" package="sqlserver"> <value>10</value> </action> <action name="sql_text" package="sqlserver"> <value> BEGIN TRAN UPDATE [OUTLANDER].[Sales].[Contact] SET [Address2] = 'Flat 1A' WHERE [FirstName] = 'Paul' AND [ContactID] = 1 -- Wait 5 secs to set up deadlock condition in other window WAITFOR DELAY '00:00:05' UPDATE [OUTLANDER].[Sales].[Contact] SET [Address2] = 'Flat 1B' WHERE [FirstName] = 'Sally' AND [ContactID] = 2 COMMIT </value> </action> </event>
Conclusion
Extended Events in SQL Server are a powerful, low impact and somewhat complicated real-time event monitoring feature. In this article, we have seen how we can use this SQL Server feature to capture deadlocks inside our database in real-time.
]]>