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.




Array

No comments yet... Be the first to leave a reply!