DeadLock 감지 및 확인

데드락 감지는 다양한 형태로 감지할 수 있다.
가. SQL Errorlog
나. 경고 시스템을 이용한 데드락  감시
다. SQL Server 프로파일러를 이용한 데드락 정보 추적. 

이번에는 경고 시스템을 이용한 데드락 감시 방법에 대한 설명을 하겠다.
작업할 내용 : 데드락 발생시 DBA라는 DB에 데드락 정보를 기록(SQL2008 이후 버젼에서만 사용 가능)

1. 데드락 감지를 위한 추적 플레그 확성화 체크
   T1204: 교착 상태에 있는 잠금의 유형과 리소스 및 현재 영향을 받은 명령을 반환.
   T1222: 교착 상태에 있는 잠금의 유형과 리소스 및 현재 영향을 받은 명령을 XSD 스키마에 맞지않는 XML형식으로 반환.

DBCC TRACEON (1222, 1204, -1);
go

 
 
2. 데드락 정보 경고 발생으로 알람 기능

가. Object 생성

------------------------------------------------------------------------------          
-- TABLE
------------------------------------------------------------------------------
CREATE TABLE [dbo].[TblAlert](
   [mIdx] [int] IDENTITY(1,1) NOT NULL,
   [mRegDate] [datetime] NOT NULL,
   [mType] [tinyint] NOT NULL,
   [DeadLocks] [xml] NULL,
   CONSTRAINT [PK_TblAlert] PRIMARY KEY CLUSTERED
   (
   [mIdx] DESC
   )
)
GO
ALTER TABLE [dbo].[TblAlert] ADD  CONSTRAINT [DF_TblAlert_mRegDate]  DEFAULT (getdate()) FOR [mRegDate]
GO
ALTER TABLE [dbo].[TblAlert] ADD  CONSTRAINT [DF_TblAlert_mType]  DEFAULT ((0)) FOR [mType]
GO
 
--------------------------------------------------------------------------------------       
-- PROCEDURE, FUNCTION
--------------------------------------------------------------------------------------
GO
CREATE PROCEDURE [dbo].[Usp_Ins_DeadLockCount]
AS
   DECLARE @DeadLocks XML;     
 
   SET @DeadLocks =
   (
    SELECT
    x.y.value('(@name)[1]', 'varchar(50)') [deadlock-list/deadlock/@eventname],
    x.y.value('(@timestamp)[1]', 'datetime') [deadlock-list/deadlock/@timestamp],
    CAST(x.y.value('data(.//data/value)[1]','VARCHAR(MAX)')  AS XML).value('deadlock[1]/victim-list[1]/victimProcess[1]/@id[1]','VARCHAR(15)') [deadlock-list/deadlock/@victim],
    CAST(x.y.value('data(.//data/value)[1]','VARCHAR(MAX)')  AS XML).query('deadlock/process-list/*') [deadlock-list/deadlock/process-list],
    CAST(x.y.value('data(.//data/value)[1]','VARCHAR(MAX)')  AS XML).query('deadlock/resource-list/*') [deadlock-list/deadlock/resource-list]
    FROM
    (
     SELECT
     CAST([target_data] as XML) [target_data]
     FROM sys.dm_xe_session_targets st
     INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
     WHERE s.[name] = 'system_health'
    ) [deadlock]
    CROSS APPLY [target_data].nodes('/RingBufferTarget/event') AS x(y)
    WHERE x.y.query('.').exist('/event[@name="xml_deadlock_report"]') = 1
    FOR XML PATH(''),ROOT('root')
   )
   INSERT INTO [dbo].[TblAlert] (mType, DeadLocks)        
   SELECT
      1, x.y.query('.') [deadlocks]
   FROM @DeadLocks.nodes('/root/deadlock-list') AS x(y);
 
GO
CREATE FUNCTION [dbo].[UNF_DEADLOCK_VIEW]
(
   @deadlock XML
)
RETURNS TABLE
AS
RETURN
(
   SELECT
    [PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
    [DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
    [KeylockObject] = Keylock.Process.value('@objectname', 'varchar(200)'),
    [Index] = Keylock.Process.value('@indexname', 'varchar(200)'),
    [IndexLockMode] = Keylock.Process.value('@mode', 'varchar(5)'),
    [Victim] = case
    when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
    [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
    [LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
    [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
    [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
    [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
    [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
    [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
    [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
    from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
     LEFT JOIN @deadlock.nodes('/deadlock-list/deadlock/resource-list/keylock') as Keylock(Process)
      ON Keylock.Process.value('owner-list[1]/owner[1]/@id', 'varchar(50)') =
         Deadlock.Process.value('@id', 'varchar(50)')
);
 
GO
--------------------------------------------------------------------------------------       
-- 경고 및 JOB 추가
--------------------------------------------------------------------------------------
USE [msdb]
GO
 
EXEC sp_add_job @job_name = '[경고] 데드락발생'
EXEC sp_add_jobstep
   @job_name = '[경고] 데드락발생'
   , @step_id = 1
   , @step_name = '데드락발생'
   , @subsystem = 'TSQL'
   , @command = 'EXEC dbo.Usp_Ins_DeadLockCount'
   , @database_name = 'DBA'
EXEC sp_add_jobserver
   @job_name = '[경고] 데드락발생'
   , @server_name = N'(local)'
GO
EXEC sp_add_alert
   @name = '[경고] 데드락발생경고'
   , @message_id = 0
   , @severity = 0
   , @enabled = 1
   , @delay_between_responses = 0
   , @include_event_description_in = 0
   , @category_name = N'[Uncategorized]'
   , @performance_condition = N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0'
   , @job_name = '[경고] 데드락발생'
GO

나. 데드락 발생

USE DBA
GO
 
Create table dbo.Cat
(
    Thing1 int not null
);
 
Create table dbo.Hat
(
    Thing2 int not null
);
 
insert into dbo.Cat
values(1);
 
insert into dbo.Hat
values(1);
 
go
 
 
-- sesssion 1
 
BEGIN TRANSACTION
 
UPDATE dbo.Hat
SET Thing2 = 1
 
-- session 2
BEGIN TRANSACTION
 
UPDATE dbo.Cat
SET Thing1 = 1
 
 
-- sesssion 1
UPDATE dbo.Cat
SET Thing1 = 1
 
 
-- session 2
UPDATE dbo.Hat
SET Thing2 = 1

 
다. 데드락 정보 확인

 SELECT
         *
FROM dbo.TblAlert T1
         CROSS APPLY dbo.UNF_DEADLOCK_VIEW( T1.DeadLocks ) T2