I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 226, comments - 2295, trackbacks - 33

My Links



Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter

Users Online: who's online

Article Categories


Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005: Immediate Deadlock notifications

Deadlocks... huh??

Deadlocks can be a pain to debug since they're so rare and unpredictable. The problem lies in repeating them in your dev environment. That's why it's crucial to have as much information about them from the production environment as possible.

There are two ways to monitor deadlocks, about which I'll talk about in the future posts. Those are SQL Server tracing and Error log checking. Unfortunately both of them suffer from the same thing: you don't know immediately when a deadlock occurs. Getting this info as soon as possible is sometimes crucial in production environments. Sure you can always set the trace flag 1222 on, but this still doesn't solve the immediate notification problem.

One problem for some might be that this method is only truly useful if you limit data access to stored procedures. <joke> So all you ORM lovers stop reading since this doesn't apply to you anymore! </joke>

The other problem is that it requires a rewrite of the problematic stored procedures to support it. However since SQL Server 2005 came out my opinion is that every stored procedure should have the try ... catch block implemented. There's no visible performance hit from this and the benefits can be huge. One of those benefits are the instant deadlocking notifications.


Needed "infrastructure"

So let's see how it done.  This must be implemented in the database you wish to monitor of course.

First we need a view that will get lock info about the deadlock that just happened. You can read why this type of query gives info we need in my previous post.

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction AS IsUserTransaction,
        AT.name AS TransactionName    
FROM    sys.dm_tran_locks L
        LEFT JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        LEFT JOIN sys.objects O ON O.object_id = P.object_id
        LEFT JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        LEFT JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        LEFT JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        LEFT JOIN sys.dm_exec_requests ER ON AT.transaction_id = ER.transaction_id
        CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
WHERE   resource_database_id = db_id() 

Next we have to create our stored procedure template:

CREATE PROC <ProcedureName>


    -- check transaction state
    IF XACT_STATE() = -1
      DECLARE @message xml
      -- get our deadlock info FROM the VIEW
      SET @message = '<TransactionLocks>' + (SELECT * FROM vLocks ORDER BY SPID FOR XML PATH('TransactionLock')) + '</TransactionLocks>'

      -- issue ROLLBACK so we don't ROLLBACK mail sending

      -- get our error message and number
      DECLARE @ErrorNumber INT, @ErrorMessage NVARCHAR(2048)
      SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE()

      -- if it's deadlock error send mail notification
      IF @ErrorNumber = 1205
        DECLARE @MailBody NVARCHAR(max)
        -- create out mail body in the xml format. you can change this to your liking.
        SELECT  @MailBody = '<DeadlockNotification>' 
                            (SELECT 'Error number: ' + isnull(CAST(@ErrorNumber AS VARCHAR(5)), '-1') + CHAR(10) +
                                    'Error message: ' + isnull(@ErrorMessage, ' NO error message') + CHAR(10)
                             FOR XML PATH('ErrorMeassage')) 
                            CAST(ISNULL(@message, '') AS NVARCHAR(MAX)) 
        -- for testing purposes
        -- SELECT CAST(@MailBody AS XML)

        -- send an email with the defined email profile. 
        -- since this is async it doesn't halt execution
        EXEC msdb.dbo.sp_send_dbmail 
                       @profile_name = 'your mail profile',
                       @recipients = 'dba@yourCompany.com',
                       @subject = 'Deadlock occured notification',
                       @body = @MailBody;

The main part of this stored procedure is of course the CATCH block. The first line in there is check of the XACT_STATE() value. This is a scalar function that reports the user transaction state. -1 means that the transaction is uncommittable and has to be rolled back. This is the state of the victim transaction in the internal deadlock killing process. Next we read from our vLocks view to get the full info (SPID, both SQL statements text, values, etc...) about both SPIDs that created a deadlock. This is possible since our deadlock victim transaction hasn't been rolled back yet and the locks are still present. We save this data into an XML message. Next we rollback our transaction to release locks. With error message and it's corresponding number we check if the error is 1205 - deadlock and if it is we send our message in an email. How to configure database mail can be seen here.

Both the view and the stored procedures template can and probably should be customized to suit your needs.


Testing the theory

Let's try it out and see how it works with a textbook deadlock example that you can find in every book or tutorial.

-- create our deadlock table with 2 simple rows
CREATE TABLE DeadlockTest ( id INT)
INSERT INTO DeadlockTest

Next create two stored procedures (spProc1 and spProc2) with our template:

For spProc1 replace <SPROC TEXT GOES HERE> in the template with:

UPDATE DeadlockTest 
SET id = 12
WHERE id = 2
-- wait 5 secs TO SET up deadlock condition IN other window
WAITFOR DELAY '00:00:05'

UPDATE DeadlockTest 
SET id = 11
WHERE id = 1


For spProc2 replace <SPROC TEXT GOES HERE> in the template with:

UPDATE DeadlockTest 
SET id = 11
WHERE id = 1

-- wait 5 secs TO SET up deadlock condition IN other window
WAITFOR DELAY '00:00:05'

UPDATE DeadlockTest 
SET id = 12
WHERE id = 2


Next open 2 query windows in SSMS:

In window 1 run put this script:

exec spProc1

In window 2 put this script:

exec spProc2


Run the  script in the first window and after a second or two run the script in the second window. A deadlock will happen and a few moments after the victim transaction fails you should get the notification mail. Mail profile has to be properly configured of course.

The resulting email should contain an XML with full info about the deadlock. You can view it by commenting msdb.dbo.sp_send_dbmail execution and uncommenting the SELECT CAST(@MailBody AS XML) line.


If you fire up the SQL Profiler, reset the table values, rerun both scripts and observe the deadlock graph event you should get a picture similar to this one:



End ? ... for now... but more to come soon!

With the upper technique we can get mostly the same info as with trace flags with the advantage of knowing immediately after happening, plus if we want we can extend this to notify/log about every error which I actually prefer to do.

We can see just how awesome is the new async functionality like database mail built on top of the SQL Server Service Broker. And instead of sending mail you can put the error in an error logging table in the same async way which I've demonstrated here.


kick it on DotNetKicks.com 


Print | posted on Wednesday, May 21, 2008 11:47 AM | Filed Under [ SQL Server ]



# re: SQL Server 2005: Immediate Deadlock notifications

I have been, for long, looking for a solution and finally you have provided me with one. Thanks.
5/25/2008 11:26 PM | Asaf

# re: SQL Server 2005: Immediate Deadlock notifications

Well done.
5/30/2008 3:34 PM | jeff maass

# re: SQL Server 2005: Immediate Deadlock notifications

Hi I can't create the view.

Msg 102, Level 15, State 1, Procedure vLocks, Line 21
Incorrect syntax near '.'.

Line 21 is CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST

Any Clue ?

12/16/2008 9:05 PM | Ariel

# re: SQL Server 2005: Immediate Deadlock notifications

your database is probably on compatibility level 80. change it to 90.
this also works only on sql server 2005
12/17/2008 10:56 AM | Mladen

# re: SQL Server 2005: Immediate Deadlock notifications

this is a great post, would it be a way to run this stored procedure in a job to check the log every 5 minutes if there is a deadlock and if so to send us the deadlock graph information in a email?
8/28/2009 10:07 PM | Sam

# re: SQL Server 2005: Immediate Deadlock notifications

you don't need a job. take a look at this:
9/1/2009 10:56 AM | Mladen

# re: SQL Server 2005: Immediate Deadlock notifications

I know you are joking, but I use an ORM and would like to point out that you can still make exclusive use of stored procedures. ORMs can map sprocs as well, and you still get the benefit of auto generated strongly typed classes.
10/15/2010 2:43 AM | sheva

# re: SQL Server 2005: Immediate Deadlock notifications

Good Post! but the sql_handle in the table sys.dm_exec_requests gives all null values. Even after raising a dead lock i'm not able to get any rows in the view.
10/22/2010 1:05 PM | Viji
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET