Deadlock 감지하여 알림하기

Deadlock 감지하여 알림하기

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

교착 상태란 둘 이상의 프로세서가 서로 남이 가진 자원을 요구하면서 양쪽 모두 작업을 수행 할 수 없이 대기 상태로 놓여지는 상태로 멀티프로그래밍이 가능한 시스템에서 일어날 수 있는 현상이다.

A라는 태스크가 B라는 태스크의 종료 후에 실행되기 위해 대기 상태에 있을 때 B라는 태스크도 A의 종료 후에 실행을 종료시키는 상태에 있으면 모두 무한 대기 상태가 되며 시스템이 정지해 있는 것처럼 되어 버린다.

 

이 상태에서는 어느 하나를 강제로 종료하지 않으면 처리가 이루어지지 않기 때문에 이를 빨리 감지하여 해결하는 것이 매우 중요하다.

 

이번 포스트는 데드락 이벤트가 발생 하였을 때 이를 모니터링 하는 방법을 알아 본다.

 

데드락에 관한 자세한 내용은 다음 아티클을 참고 한다.

• DeadLock(교착상태) 모니터 하기 : http://sqlmvp.kr/140164120810

 

 

데드락 실습을 위한 테이블 생성과 프로시저 생성을 한다.

CREATE TABLE TBL_A (COL1 INT, COL2 INT) GO   CREATE TABLE TBL_B (COL1 INT, COL2 INT) GO   INSERT INTO TBL_A VALUES (1, 1) GO   INSERT INTO TBL_B VALUES (1, 1) GO   CREATE PROC SESSION_1 AS BEGIN TRAN UPDATE TBL_A SET COL2 = COL2 * 2 WHERE COL1 = 1 WAITFOR DELAY '00:00:03'; UPDATE TBL_B SET COL2 = COL2 * 2 WHERE COL1 = 1 ROLLBACK TRAN GO   CREATE PROC SESSION_2 AS BEGIN TRAN UPDATE TBL_B SET COL2 = COL2 * 2 WHERE COL1 = 1   WAITFOR DELAY '00:00:03';   UPDATE TBL_A SET COL2 = COL2 * 2 WHERE COL1 = 1 ROLLBACK TRAN GO

 

서로 다른 세션에서 프로시저를 실행한다.

세션1	세션2
EXEC SESSION_1	EXEC SESSION_2

 

 

성능모니터에서는 데드락이 발생 하였을 경우 현재 발생한 데드락을 카운트 한다.

 

다음 스크립트는 sys.dm_os_performance_counters 에서 Deadlock 카운터 값을 읽어 카운터 값이 0보다 큰 경우 (데드락 발생 또는 발생 했었음) 사용자에게 알려 준다. 지금까지 발생한 데드락의 개수를 확인 할 수 있다.

 

데드락 발견 시 기록할 테이블 생성

begin try     drop table DBA_DeadLock_Detect end try begin catch end catch go   create table DBA_DeadLock_Detect ( num int identity, CurrentDate datetime, cntr_value int ) go

 

데드락 감지시 데드락 히스토리 테이블에 데이터를 입력하고 사용자에게 알림.

declare @cntr_value_1 int declare @cntr_value_2 int   select top 1 @cntr_value_1 = cntr_value from DBA_DeadLock_Detect order by num desc   select      @cntr_value_2 = cntr_value     from     sys.dm_os_performance_counters with(nolock) where     object_name = 'SQLServer:Locks'     and counter_name= 'Number of Deadlocks/sec'     and instance_name = '_Total'     if @cntr_value_2 > isnull(@cntr_value_1, 0) begin     insert into DBA_DeadLock_Detect(CurrentDate, cntr_value) values (getdate(), @cntr_value_2)     select '[Server:' + @@SERVERNAME + '] ' + ' Deadlock Founds : ' + convert(nvarchar(10), @cntr_value_2) end

 

 

위 스크립트를 응용하여 데드락 발생 시 SMS 또는 이메일 등으로 상황을 전파하여 더 큰 장애를 방지 할 수 있도록 하자.

 

[참고자료]

• http://sqlmag.com/blog/better-way-enable-email-alerts-deadlocks
• DeadLock(교착상태) 모니터 하기 : http://sqlmvp.kr/140164120810

 

 

 

2013-08-22 / 강성욱 / http://sqlmvp.kr