Deadlock 감지하여 알림하기

 

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

 

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

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

 

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

 

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

 

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

 

 

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

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 또는 이메일 등으로 상황을 전파하여 더 큰 장애를 방지 할 수 있도록 하자.

 

[참고자료]

 

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38458
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20748
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2091
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1906
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3555
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2583
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1655
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1848
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4822
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 2014
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1654
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1581
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1621
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7995
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 3195
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1293
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1852
» Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1903
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1444
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1532
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1940
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1769





XE Login