DeadLock(교착상태) 모니터 하기

DeadLock(교착상태) 모니터 하기

 

DeadLock(교착상태)?

한 태스크에서 잠근 리소스를 다른 태스크에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하는 현상. (서로 맞물린 상태의 차단)

• 순환 교착(cycle DeadLock) : 서로 다른 개체를 차단할 때 발생
• 변환 교착(Conversion DeadLock) : 같은 대상에 대해 둘 이상의 세션이 동시에 잠금을 변경하려고 할 때 발생

 

교착 상태를 일으킬 수 있는 리소스

• 잠금 : 개체, 페이지, 행, 메타데이터, 응용 프로그램 등의 리소스에 대한 잠금을 획득하려고 대기 하는 경우.
• 작업자 스레드 : 사용 가능한 작업자 스레드를 대기하는 태스크가 교착 상태를 일으킬 수 있음.대기 태스크가 모든 작업자 스레드를 차단하는 리소스를 소유하는 경우 교착상태 발생.
• 메모리 : 동시 요청이 사용 가능한 메모리보다 많은 메모리 부여를 대기하는 경우.
• 병렬 쿼리 실행 관련 리소스 : 병렬쿼리에 속하지 않는 하나 이상의 다른 프로세스를 포함할 경우 서로 차단하여 발생. 서버에서 새 쿼리 실행이 시작되거나 시스템에 작업자 스레드가 부족하여 시스템 작업이 예기치 않게 변경되면 교착 상태가 발생.
• MARS(Multiple Active Result Sets)리소스 : MASRS에서 여러 활성 요청의 인터리브를 제어하는데 사용.

DeadLock 감지하기.

교착 상태 검색은 데이터베이스 엔진 인스턴스의 모든 태스크에 대한 검색을 주기적으로 시작하는 잠금 모니터에서 수행합니다.

• 기본 간격은 5초로 수행.
• 잠금 모니터 스레드가 교착 상태를 발견하면 잠금 상태의 빈도에 따라 5초에서 최하 100밀리초까지 교착 상태 검색 간격이 짧아 짐.
• 잠금 모니터 스레드가 교착 상태 검색을 중지하면 데이터베이스 엔진은 검색 간격을 다시 5초로 늘림.

 

실습 환경

CREATE PROC SESSION_1 AS BEGIN TRAN     UPDATE TBL_A SET COL5 = COL5 * 2 WHERE COL1 = 100          WAITFOR DELAY '00:00:03';          UPDATE TBL_B SET COL5 = COL5 * 2 WHERE COL1 = 100 ROLLBACK TRAN GO   CREATE PROC SESSION_2 AS BEGIN TRAN     UPDATE TBL_B SET COL5 = COL5 * 2 WHERE COL1 = 100          WAITFOR DELAY '00:00:03';          UPDATE TBL_A SET COL5 = COL5 * 2 WHERE COL1 = 100 ROLLBACK TRAN GO

 

서로 다른 세션에서 SESSION_1, SESSION_2 실행

1. sp_lock

   

    

2. DBCC TRACEON (1204, -1)

   

   교착 상태와 관련된 각 노드에 의해 형식이 지정된 교착 상태 정보를 보고.

   

    

3. DBCC TRACEON (1222, -1) --SQL Server 2005 이상사용

   프로세스별 및 리소스별 순서로 교착상태의 정보를 보고

    

 

1. Profiler

   

   

    

2. Perfmon

   

    

3. DMV

SELECT TEXT, BLOCKING_SESSION_ID, COMMAND, DATABASE_ID, WAIT_TYPE,WAIT_RESOURCE, * FROM SYS.DM_EXEC_REQUESTS AS REQUEST CROSS APPLY SYS.DM_EXEC_SQL_TEXT(REQUEST.SQL_HANDLE) AS SQL_TEXT WHERE SESSION_ID > 50 AND SESSION_ID <> @@SPID AND BLOCKING_SESSION_ID <> 0

 

 

1. Job Alert

 

 

DeadLocK 최소화 하기

• 적절한 인덱스 설정.
• 자원의 한쪽 방향 액세스,
• 짧은 트랜잭션
• 테이블 크기 최소화
• 잠금 제한 시간 설정(SET LOCK_TIMEOUT)
• 적절한 격리 수준(READ UNCOMMITTED)
• 하드웨어 성능 향상
• 지속적인 모니터링

 

 

그런데…실제 서비스에선 이렇게 단순한 그래프로 남지 않는다는거…

아놔..bb

 

참조 및 참고 사이트

• http://msdn.microsoft.com/ko-kr/library/ms178104(v=sql.105).aspx
• http://www.sqler.com/459038
• http://www.sqler.com/320551
• http://sqlserverpedia.com/wiki/Trace_Flags