2017-06-29

Handling SQL Server Deadlocks With Event Notifications

Dave Mason - Event Notifications - SQL Server Deadlock

I've seen a few different methods for handling deadlocks in SQL Server. I don't notice the use of event notifications very often, so I thought I'd share my approach. For my purposes, I want SQL to notify me when a deadlock occurs. I also want an easily accessible deadlock graph that I can inspect and potentially pass along to development or software vendors.

Let's skip ahead to the end result. Here's a glimpse at my Outlook Inbox. The selected email is an alert I received when a deadlock occurred.

Dave Mason -
 SQL Server Deadlock Alert

There's a small amount of info in the email body that provides some basic information (name of the SQL host, time the deadlock occurred, etc.) There's also a file attachment with the *.xdl file extension, which tells us it is a "Microsoft SQL Server Deadlock File". It contains a wealth of information for the SPIDs that were involved in the deadlock. If you open the file in a text editor like Notepad.exe, you'll see the data is stored in XML format. But it's not easy to parse and interpret it as-is. So instead, open the file with SSMS and view the information graphically. Here's what it looks like:

Dave Mason - SQL Server Deadlock Graph


T-SQL Automation

To automatically generate those emails/alerts, I created four objects (you can read more about them in this post):

  • A QUEUE.
  • A SERVICE.
  • An EVENT NOTIFICATION for the DEADLOCK_GRAPH event.
  • An activation PROCEDURE for the QUEUE.


Below is a script that creates the objects listed above--it's in the first tab. It's important to be familiar with the format for the XML data returned by the EVENTDATA() function for the DEADLOCK_GRAPH event--that's in the second tab. There's also some sample EVENTDATA() data in the third tab. I generated it with this deadlock example:

--Create a queue just for Deadlock events.
CREATE QUEUE queDeadlockNotification
 
--Create a service just for Deadlock events.
CREATE SERVICE svcDeadlockNotification
ON QUEUE queDeadlockNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
 
-- Create the event notification for Deadlock events on the service.
CREATE EVENT NOTIFICATION enDeadlock
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE 'svcDeadlockNotification', 'current database';
GO

CREATE PROCEDURE dbo.ReceiveDeadlock
/******************************************************************************
* Name     : dbo.ReceiveDeadlock
* Purpose  : Handles deadlock events (activated by QUEUE queDeadlockNotification)
* Inputs   : None
* Outputs  : None
* Returns  : Nothing
******************************************************************************
* Change History
*      06/26/2017    DMason Created
******************************************************************************/
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @MsgBody XML
 
 WHILE (1 = 1)
 BEGIN
  BEGIN TRANSACTION
 
  -- Receive the next available message FROM the queue
  WAITFOR (
   RECEIVE TOP(1) -- just handle one message at a time
   @MsgBody = CAST(message_body AS XML)
   FROM queDeadlockNotification
  ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
  -- If we didn't get anything, bail out
  IF (@@ROWCOUNT = 0)
  BEGIN
   ROLLBACK TRANSACTION
   BREAK
  END
  ELSE
  BEGIN
   --Do stuff here.
   DECLARE @Login SYSNAME = @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)' );
   DECLARE @IsSystem INT = @MsgBody.value('(/EVENT_INSTANCE/IsSystem)[1]', 'VARCHAR(8)' );
   DECLARE @StartTime VARCHAR(128) = @MsgBody.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(128)' );
   DECLARE @Start DATETIME = @StartTime
   DECLARE @FileName NVARCHAR(255)= 'Deadlock Graph ' +
    REPLACE(CONVERT(VARCHAR, @Start, 121), ':', '.') + '.xdl';
 
   DECLARE @MailBody NVARCHAR(MAX);
   DECLARE @Subject NVARCHAR(255);
   DECLARE @Qry NVARCHAR(MAX);
 
   SET @Qry = CAST(@MsgBody.query('/EVENT_INSTANCE/TextData/deadlock-list') AS NVARCHAR(MAX));
   SET @Qry = REPLACE(@Qry, CHAR(39), CHAR(39) + CHAR(39));
   SET @Qry = 'SET NOCOUNT ON; ' + CHAR(13) + CHAR(10) +
    'SELECT ' + CHAR(39) + @Qry + CHAR(39);
                    
   SET @Subject = @@SERVERNAME + ' -- ' + @MsgBody.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)' )   
   SET @MailBody =
    '<table border="1">' +
    '<tr><td>Server Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(128)' ) + '</td></tr>' +
    '<tr><td>Start Time</td><td>' + @StartTime + '</td></tr>' + 
    '<tr><td>Login Name</td><td>' + @Login + '</td></tr>' +
    '<tr><td>Session Login Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'VARCHAR(128)' ) + '</td></tr>' +
    '<tr><td>Is System</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/IsSystem)[1]', 'VARCHAR(128)' ) + '</td></tr>' +
    '</table><br/>';
 
   EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'You@YourDomain.com',
    @subject = @Subject,
    @body = @MailBody,
    @body_format = 'HTML',
    @query = @Qry,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = @FileName,
    @query_no_truncate = 1,
    @query_result_width = 32767,                                                  
    @exclude_query_output = 1

   /*
    Commit the transaction.  At any point before this, we
    could roll back -- the received message would be back
    on the queue AND the response wouldn't be sent.
   */

   COMMIT TRANSACTION
  END
 END
END
GO

ALTER QUEUE dbo.queDeadlockNotification
WITH
 STATUS = ON,
 ACTIVATION (
  PROCEDURE_NAME = dbo.ReceiveDeadlock,
  STATUS = ON,
  MAX_QUEUE_READERS = 1,
  EXECUTE AS OWNER)
GO
<xs:complexType name="EVENT_INSTANCE_DEADLOCK_GRAPH">
 <xs:sequence>
  <!-- Basic Envelope -->
  <xs:element type="SSWNAMEType" name="EventType"/>
  <xs:element type="xs:string" name="PostTime"/>
  <xs:element type="xs:int" name="SPID"/>
  <!-- Main Body -->
  <xs:element type="xs:anyType" name="TextData"/>
  <xs:element type="emptiableLong" name="TransactionID"/>
  <xs:element type="SqlTraceNameType" name="LoginName"/>
  <xs:element type="xs:string" name="StartTime"/>
  <xs:element type="SqlTraceNameType" name="ServerName"/>
  <xs:element type="xs:base64Binary" name="LoginSid"/>
  <xs:element type="xs:long" name="EventSequence"/>
  <xs:element type="emptiableInt" name="IsSystem"/>
  <xs:element type="SqlTraceNameType" name="SessionLoginName"/>
 </xs:sequence>
</xs:complexType>
<EVENT_INSTANCE>
  <EventType>DEADLOCK_GRAPH</EventType>
  <PostTime>2017-06-26T14:28:15.780</PostTime>
  <SPID>22</SPID>
  <TextData>
    <deadlock-list>
      <deadlock victim="process80574988">
        <process-list>
          <process id="process80574988" taskpriority="0" logused="216" waitresource="RID: 2:1:7360:0" waittime="6790" ownerId="678928045" transactionname="user_transaction" lasttranstarted="2017-06-26T14:28:01.773" XDES="0xb6ce3970" lockMode="U" schedulerid="2" kpid="1640" status="suspended" spid="183" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-26T14:28:08.997" lastbatchcompleted="2017-06-26T14:28:01.773" clientapp="Microsoft SQL Server Management Studio - Query" hostname="DMasonHP" hostpid="12604" loginname="Celtics\CMaxwell" isolationlevel="read committed (2)" xactid="678928045" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
            <executionStack>
              <frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x0200000005db68127d85eb887f1be301338b2f2ebaa91f6e">
UPDATE [##Suppliers] set [Fax] = @1  WHERE [supplierid]=@2     </frame>
              <frame procname="adhoc" line="1" sqlhandle="0x02000000ca059406a0c94311928b17b2b824c0d847e40b7f">
UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1;     </frame>
            </executionStack>
            <inputbuf>
UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1;    </inputbuf>
          </process>
          <process id="process134e43b88" taskpriority="0" logused="224" waitresource="RID: 2:3:4016:0" waittime="2844" ownerId="678928065" transactionname="user_transaction" lasttranstarted="2017-06-26T14:28:05.670" XDES="0x14ff82e90" lockMode="U" schedulerid="1" kpid="4704" status="suspended" spid="182" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-26T14:28:12.930" lastbatchcompleted="2017-06-26T14:28:05.673" clientapp="Microsoft SQL Server Management Studio - Query" hostname="DMasonHP" hostpid="12604" loginname="Celtics\CMaxwell" isolationlevel="read committed (2)" xactid="678928065" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
            <executionStack>
              <frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x020000005f889415278064e941bfe9fe107454d8d8caa4c4">
UPDATE [##Employees] set [phone] = @1  WHERE [empid]=@2     </frame>
              <frame procname="adhoc" line="1" sqlhandle="0x020000008d2d0c2d462424d968b40ec2f7564ea3d34b2790">
UPDATE ##Employees
SET phone = N'555-9999'
WHERE empid = 1;     </frame>
            </executionStack>
            <inputbuf>
UPDATE ##Employees
SET phone = N'555-9999'
WHERE empid = 1;    </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <ridlock fileid="1" pageid="7360" dbid="2" objectname="tempdb.dbo.##Suppliers" id="lock19e1e7b00" mode="X" associatedObjectId="7998393042331172864">
            <owner-list>
              <owner id="process134e43b88" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process80574988" mode="U" requestType="wait" />
            </waiter-list>
          </ridlock>
          <ridlock fileid="3" pageid="4016" dbid="2" objectname="tempdb.dbo.##Employees" id="lockf69fdd80" mode="X" associatedObjectId="7926335448252284928">
            <owner-list>
              <owner id="process80574988" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process134e43b88" mode="U" requestType="wait" />
            </waiter-list>
          </ridlock>
        </resource-list>
      </deadlock>
    </deadlock-list>
  </TextData>
  <TransactionID />
  <LoginName>sa</LoginName>
  <StartTime>2017-06-26T14:28:15.777</StartTime>
  <ServerName>DMasonHP</ServerName>
  <LoginSid>AQ==</LoginSid>
  <EventSequence>189164</EventSequence>
  <IsSystem>1</IsSystem>
  <SessionLoginName />
</EVENT_INSTANCE>


Activation Procedure Notes

The XML data returned by the EVENTDATA() function that was written to the QUEUE is assigned to the @MsgBody variable:

RECEIVE TOP(1) -- just handle one message at a time
   @MsgBody = CAST(message_body AS XML)
   FROM queDeadlockNotification
  


The <deadlock-list> element and all of its data is assigned to the @Qry variable. This data will become the contents of a file attachment for an email message.

SET @Qry = CAST(@MsgBody.query('/EVENT_INSTANCE/TextData/deadlock-list') AS NVARCHAR(MAX));


Next, we create a "query" that does nothing more than select the <deadlock-list> data as a string literal. This "query" will be the @query parameter value for msdb.dbo.sp_send_dbmail. The REPLACE function is used to double up any single quotes that may exist.

SET @Qry = REPLACE(@Qry, CHAR(39), CHAR(39) + CHAR(39));
SET @Qry = 'SET NOCOUNT ON; ' + CHAR(13) + CHAR(10) +
 'SELECT ' + CHAR(39) + @Qry + CHAR(39);


Pros And Cons

As noted, there are other ways to handle deadlocks in SQL Server. The approach presented here may have some drawbacks compared to others. There is an authorization issue for msdb.dbo.sp_send_dbmail that will need to be addressed for logins without elevated permissions. Additionally, you might get hit with an unexpected deluge of emails. (The first time I got deadlock alerts, there were more than 500 of them waiting for me in my Inbox.) Lastly, there's the XML issue: it's not everyone's cup of tea. On the plus side, I really like the proactive nature: an event occurs, I get an email. I think most would agree it's better to know something (bad) happened before the customers start calling. The automated generation of Deadlock Graph (*.xdl) files is convenient. And event notifications have been available since SQL Server 2005. As far as I know, the feature is available in all editions, including Express Edition.


SHARE