Event Notification: Strupsaķeres (deadlock) reģistrēšana

Rakstā aprakstīts, kā var reģistrēt visas strupsaceķeres (deadlocks), kas radušās SQL Server instancē un ar to saistīto informāciju reģistrēt datu bāzes tabulā izmantojot Event Notification. Strādā uz SQL Server 2005 un jaunākām versijām.

Internetā ir pilns ar aprakstiem, kas ir strupsaķeres un kā tās var "noķert" izmantojot SQL Server Profiler. Un tas ir ļoti jauki (šajā rakstā to neaprakstīšu). Tomēr, ja Profiler nav bijis palaists attiecīgajā brīdī, informācija par šo notikumu aiziet nebūtībā.

Viens no risinājumiem (un nebūt ne vienīgais veids) ir izmantot Event Notifications (msdn) lai reģistrētu sturpsaķeres informāciju datu bāzes tabulā.

Par un ap
Event Notifications (nav ne jausmas kā to iztulkot latviski) ir speciāls datu bāzes objekts, kas sūta ziņu Service Broker servisam reaģējot uz kādu notikumu. No tā izriet divas lietas:
- Lai saprastu, kas ir Event Notifications manuprāt ir jāsaprot, kas ir Serviece Broker. Pēdējais manī jau kādu krietnu laiku rada visai patīkamas emocijas dēļ iespējām ko tas piedāvā- tādēļ šajā emuārā ir vesela sadaļa veltīta SQL Server Service Broker. Jāatdzīst, ka līdz ar iespējām ir arī gana daudz "suņu jāapēd". Bet to var izdarīt tikai strādājot ar to.. Jebkurā gadījumā iesaku to lietu papētīt.
- Tā kā saņēmējs ir Service Broker serviss, tas nozīmē, ka notikuma apstrāde notiek asinhroni. Un ne tikai tas. Notikums saņēmējs (serviss) var atrasties jebkurā instances datu bāzē, citā SQL Server instancē un galu galā- notikuma apstrādes process var būt ārēja programma.

Ko dara skripts
Pieņemu, ka skripta laidējam ir pietiekamas lietotāja tiesības visām minētajām darbībām.
Zemāk dods skripts kas:
* izveido jaunu datu bāzi "DBTest";
* datu bāzē atļauj izmantot Service Broker;
* izveido Service Broker servisu un tam nepieciešamo rindu;
* izveido tabulu, kurā reģistrēt notikuma datus;
* izveido Service Broker aktvizācijas procedūru (vairāk par to SQL Server Service Broker);
* piešķir izveidotajai rindai aktivizācijas procedūru;
* izveido servera līmeņa Event Notification, kas sūta ziņojumu gadījumā, ja izveidojas strupsaķere.

Skripts
Uzmanību!!! skriptā ir sadalīts divās daļās, tāpēc ka otrā skripta izpildei nepieciešamas Service Broker identifikātors (tas ir globāls unikāls identifikators- uniqueidentifier datu tips)
1.daļa

Create Database DBTest;
Go
-- Atļauj Service Broker:
Alter Database DBTest Set ENABLE_BROKER;
Go
Use DBTest;
Go
-- Rinda, kurā glabāsies notikumi:
CREATE QUEUE dbo.DeadLockInfoQueue;
-- Serviss, kuram tiks sūtīti ziņojumi:
CREATE SERVICE DeadLockInfoService
    ON QUEUE dbo.DeadLockInfoQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
Go
-- Tabula notikumu glabāšanai:
CREATE TABLE dbo.EventsXML (
    EventNumber INT IDENTITY PRIMARY KEY,
    EventType   NVARCHAR(256),
    EventTime   DATETIME,
    EventData   XML
    );
Go
-- Aktivizācijas procedūra notikumu apstrādei:
Create Procedure dbo.sb_LogDeadLockInfo
AS
Begin
    Set NoCount On;
    Declare @message_body xml;
    Declare @message_type_name NVarChar(256);
    Declare @dialog UniqueIdentifier;
    While (1 = 1)
    Begin
        Begin Tran;
        WaitFor (Receive Top(1)
                    @message_type_name = message_type_name,
                    @message_body = message_body,
                    @dialog = conversation_handle
                From dbo.DeadLockInfoQueue), Timeout 3000;
        IF(@@ROWCOUNT = 0)
        Begin
            Rollback;
            Break;
        End
        IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
        Begin
            End Conversation @dialog ;
        End
        Else
        Begin
            Insert Into dbo.EventsXML(EventType, EventTime, EventData)
            Values
                (
                    @message_body.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(256)'),
                    @message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
                    @message_body
                );
        End
        Commit;
    End
End
Go
-- Pieslēdz rindai apstrādes procedūru:
ALTER QUEUE [dbo].[DeadLockInfoQueue]
    WITH ACTIVATION (
        STATUS = ON,
        PROCEDURE_NAME = dbo.sb_LogDeadLockInfo,
        MAX_QUEUE_READERS = 1,
        EXECUTE AS Owner                          
    );
Go
-- Iegūst service broker guid (lai zinātu uz kurieni sūtīt ziņojumus):
Select name, service_broker_guid From sys.databases
Where database_id = DB_ID()

2.daļa
Pirmās daļas izpildes beigās tiek izpildīts Select, kas atgriež vienu rindu. Šajā rindā ir datu bāzes nosaukums un service_broker_guid. Tas ir jāiekopē skriptā norādītajā vietā un jāizpilda skripta otrā daļa:

CREATE EVENT NOTIFICATION Notify_DeadLock
ON SERVER FOR DEADLOCK_GRAPH
TO SERVICE 'DeadLockInfoService', 'Šeti GUID!!!'; -- jāpapildina

Un tas arī viss!

Strupsaķeres radīšana
Tagad atliek tik gaidīt, kad notiks strupsaķere un tad jau var skatīties kāda informācija ir ir saglabāta EventsXML tabulā. Protams, var jau arī pasteidzināt laiku, un izveidot mākslīgu strupsaķeri (lai notestētu, vai tiešām strādā.
Šeit neliels skripts, kas var atvieglot šo uzdevumu. Priekšnosacījums: jābūt diviem atvērtiem Management Studio logiem. Pēc tam ir jāiezīmē skripta daļa (līdz komentāram), jāizpilda attiecīgā daļa, jārāslēdzas uz otru logu, jāizpilda skripts, jāatgriežas uz pirmo logu un jāizpilda skripts līdz beigām.
1. logā:

create table ##t1 (i int)
go
create table ##t2 (i int)
Go
Insert Into ##t1 (i) values(1)
Insert Into ##t2 (i) values(1)
Go
Begin tran
Update ##t1 set i = 2 where i = 1
-- Šajā vietā pārslēdzamies uz 2. logu un izpildam skriptu!


-- kad esam izpildījuši 2. loga skriptu, tad izpildām šo:
Update ##t2 set i = 2 where i = 1
commit

2. logā:

-- Palaižam šo skriptu:
Begin tran
Update ##t2 set i = 2 where i = 1
Update ##t1 set i = 2 where i = 1
Commit
-- Pārslēdzamies uz 1. logu un tajā izpildām 2. daļu!

Pēc šīm darbībām, ir radīta strupsaķere, kas būs reģistrēta mūsu izveidotajā tabulā!

select * from dbtest..eventsxml

Satīrīšanas skripts
Arī pavisam vienkāršs:

Use master;
DROP EVENT NOTIFICATION Notify_DeadLock ON Server;
DROP DATABASE DBTest;