Capture SQL Server Deadlocks using Extended Events

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.

]]>

Leave a comment

Your email address will not be published.