在不修改現有的代碼情況下實現SQL Server死結即時通知

Post by:Mladen Prajdić : Immediate deadlock notifications without changing existing code

In my previous post about immediate deadlock notifications in SQL Server 2005 I've shown a way to use a try catch block to get the deadlock error.

The con of this method, although it uses best practice for error handling in SQL Server 2005, is that you have to change existing code and

it doesn't work for non stored procedure code. And that IS a pretty BIG con! As is customary in this blog there is a solution to this.

SQL Server 2005 Event notifications

Event notifications are a special kind of database object that sen:d information about server and database events to a Service Broker service.

They execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information

about these events to a Service Broker service. There are three scopes for event notifications: Server, Database and Queue.

We of course want a Server wide deadlock notification so that we can be notified of all deadlocks on the entire server

I have to point out that event notification are an awesome use of Service Broker functionality.

Setup

For the purpose of this post I've used tempdb to hold our deadlock event info. Of course this should go into an administrative database if you have one.

Also an email is sent to notify the DBA that the deadlock happened. Thus the Immediate part

USE tempdbGO-- this procedure will write our event data into the table and send the notification emailCREATE PROCEDURE usp_ProcessNotification AS    DECLARE @msgBody XML        DECLARE @dlgId uniqueidentifier    -- you can change this to get all messages at once    WHILE(1=1)    BEGIN        BEGIN TRANSACTION            BEGIN TRY                    -- receive messages from the queue one by one                        ;RECEIVE TOP(1)                     @msgBody    = message_body,                    @dlgId        = conversation_handle            FROM    dbo.DeadLockNotificationsQueue                        -- exit when the whole queue has been processed            IF @@ROWCOUNT = 0            BEGIN                IF @@TRANCOUNT > 0                BEGIN                     ROLLBACK;                END                  BREAK;            END             -- insert event data into our table             INSERT INTO TestEventNotification(eventMsg)            SELECT @msgBody                        DECLARE @MailBody NVARCHAR(MAX)                SELECT @MailBody = CAST(@msgBody AS NVARCHAR(MAX));            -- 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', -- your defined email profile             --         @recipients = 'dba@yourCompany.com', -- your email            --         @subject = 'Deadlock occured notification',            --         @body = @MailBody;                        IF @@TRANCOUNT > 0            BEGIN                 COMMIT;            END        END TRY        BEGIN CATCH            IF @@TRANCOUNT > 0            BEGIN                 ROLLBACK;            END            -- write any error in to the event log            DECLARE @errorNumber BIGINT, @errorMessage nvarchar(2048), @dbName nvarchar(128)            SELECT  @errorNumber = ERROR_NUMBER(), @errorMessage = ERROR_MESSAGE(), @dbName = DB_NAME()            RAISERROR (N'Error WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue.                        DATABASE Name: %s; Error number: %I64d; Error Message: %s',                         16, 1, @dbName, @errorNumber, @errorMessage) WITH LOG;        END CATCH;    ENDGO-- create the notification queue that will receive the event notification messages-- add the activation stored procedure that will process the messages in the queue-- as they arriveCREATE QUEUE DeadLockNotificationsQueue    WITH STATUS = ON,    ACTIVATION (        PROCEDURE_NAME = usp_ProcessNotification,        MAX_QUEUE_READERS = 1,        EXECUTE AS 'dbo' );GO-- crete the notofication service for our queue with the pre-defined message typeCREATE SERVICE DeadLockNotificationsService    ON QUEUE DeadLockNotificationsQueue                 ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);GO-- create the route for the serviceCREATE ROUTE DeadLockNotificationsRoute    WITH SERVICE_NAME = 'DeadLockNotificationsService',    ADDRESS = 'LOCAL';GO-- create the event notification for the DEADLOCK_GRAPH event. -- other lock events can be added CREATE EVENT NOTIFICATION DeadLockNotificationEventON SERVER FOR DEADLOCK_GRAPH -- , LOCK_DEADLOCK_CHAIN, LOCK_DEADLOCK, LOCK_ESCALATION -- ANY OF these can be SETTO SERVICE 'DeadLockNotificationsService',            'current database' -- CASE sensitive string that specifies USE OF server broker IN CURRENT dbGO-- check to see if our event notification has been created okSELECT * FROM sys.server_event_notifications WHERE name = 'DeadLockNotificationEvent';GO-- create the table that will hold our deadlock infoCREATE TABLE TestEventNotification(Id INT IDENTITY(1,1), EventMsg xml, EventDate datetime default(GETDATE()))GO-- clean up/*DROP TABLE TestEventNotificationDROP PROCEDURE usp_ProcessNotificationDROP EVENT NOTIFICATION DeadLockNotificationEvent ON SERVER DROP ROUTE DeadLockNotificationsRouteDROP SERVICE DeadLockNotificationsServiceDROP QUEUE DeadLockNotificationsQueue*/

Testing

For testing you'll need to open 2 windows in SQL Server Management Studio

-- tun this first to create the test table USE AdventureWorksIF object_id('DeadlockTest') IS NOT NULL     DROP TABLE DeadlockTest GOCREATE TABLE DeadlockTest ( id INT)INSERT INTO DeadlockTestSELECT 1 UNION ALLSELECT 2GO---------------------------------------------------------------------------------------------------------------------------------- run this in query window 1BEGIN TRANUPDATE DeadlockTest SET id = 12WHERE id = 2-- wait 5 secs to set up deadlock condition in other windowWAITFOR DELAY '00:00:05'UPDATE DeadlockTest SET id = 11WHERE id = 1COMMIT---------------------------------------------------------------------------------------------------------------------------------- run this in query window 2 a second or two -- after you've run the script in query window 1BEGIN TRANUPDATE DeadlockTest SET id = 11WHERE id = 1-- wait 5 secs to set up deadlock condition in other windowWAITFOR DELAY '00:00:05'UPDATE DeadlockTest SET id = 12WHERE id = 2COMMIT---------------------------------------------------------------------------------------------------------------------------------- run this after the test to see that we have our deadlock event notification savedUSE tempdbSELECT * FROM TestEventNotificationORDER BY id

We can see that this setup works great. Because we have subscribed to the DEADLOCK_GRAPH event we can see the same information

as if we had traced it with the SQL Profiler. Of course this kind of setup can be used for any kind of event that is supported.