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

jevida(강성욱) 2013.11.18 14:28 Views : 11409

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)
  • 하드웨어 성능 향상
  • 지속적인 모니터링

 

 

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

 

 

참조 및 참고 사이트


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

, , , , , , , , , , I want to
cmd_document_vote_user Update Log List
No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 2191
1606 extended events 용 excel view 공개 버전 minsouk 2014.04.07 6425
1605 [TIP] 한 라인에서 여러 테이블 삭제 하기. [3] 엘리엘1 2014.01.03 8646
1604 GhostRecord(인덱스에서 행 삭제시발생) jevida(강성욱) 2013.11.25 13581
1603 프로시저, 함수, 트리거 생성 정보 보기. jevida(강성욱) 2013.11.25 10656
1602 힙테이블에서 행을 삭제하면어떻게 처리 될까? [1] jevida(강성욱) 2013.11.25 9395
1601 SSMS의 디자이너에서 테이블 수정 시 발생하는 영향 [1] jevida(강성욱) 2013.11.25 10546
1600 SQL Server를 활용한 Perfmon 로그 저장 [1] jevida(강성욱) 2013.11.21 14778
1599 VLF 환경과 성능 jevida(강성욱) 2013.11.21 10095
1598 Block 모니터링 jevida(강성욱) 2013.11.21 8089
1597 추적파일을 테이블로 로드하기. jevida(강성욱) 2013.11.21 8912
1596 LOGON 트리거 jevida(강성욱) 2013.11.18 6585
» DeadLock(교착상태) 모니터 하기 [2] jevida(강성욱) 2013.11.18 11409
1594 Tempdb 경합(동시성 강화) jevida(강성욱) 2013.11.18 7580
1593 SSAS Backup 자동화 하기 jevida(강성욱) 2013.11.18 8191
1592 SSAS - MDX 스크립트 jevida(강성욱) 2013.11.18 7285
1591 SSAS - MDX 쿼리 jevida(강성욱) 2013.11.18 8166
1590 SSAS - MDX 활용 jevida(강성욱) 2013.11.12 7201
1589 SSAS - 집계 최적화 jevida(강성욱) 2013.11.12 7422
1588 SSAS - 사용자 계층 설계 jevida(강성욱) 2013.11.12 6619
1587 SSAS - 집계 설계 이해 - (3)집계 마법사에 특성 추가 jevida(강성욱) 2013.11.06 6984
Write Tag


XE Login