데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1542
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1720
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4497
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1715
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1537
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1528
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1536
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7700
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2103
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1241
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1770
» Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1766
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1370
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1469
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1854
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1643
1834 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1388
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1157
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2467
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421





XE Login