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



No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 20057
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 12120
1594 Tempdb 경합(동시성 강화) jevida(강성욱) 2013.11.18 7593
1593 SSAS Backup 자동화 하기 jevida(강성욱) 2013.11.18 8201
1592 SSAS - MDX 스크립트 jevida(강성욱) 2013.11.18 7295
1591 SSAS - MDX 쿼리 jevida(강성욱) 2013.11.18 8197
1590 SSAS - MDX 활용 jevida(강성욱) 2013.11.12 7216
1589 SSAS - 집계 최적화 jevida(강성욱) 2013.11.12 7434
1588 SSAS - 사용자 계층 설계 jevida(강성욱) 2013.11.12 6627
1587 SSAS - 집계 설계 이해 - (3)집계 마법사에 특성 추가 jevida(강성욱) 2013.11.06 6989
1586 SSAS - 집계 설계 이해 - (2)파티션 수 변경 jevida(강성욱) 2013.11.06 6769
1585 SSAS - 집계 설계 이해 - (1)집계 디자인 마법사 jevida(강성욱) 2013.11.06 7191
1584 SSAS - 계정차원 - (2)단항 연산자 jevida(강성욱) 2013.11.01 6243
1583 SSAS - 계정차원 - (1) 계정 인텔리전스 jevida(강성욱) 2013.11.01 6241
1582 SSAS - 차원관계 - (1)참조 관계 유형 jevida(강성욱) 2013.10.29 6554
1581 SSAS - 계산 작업 jevida(강성욱) 2013.10.29 6498
1580 SSAS - 고유 카운트 측정값 jevida(강성욱) 2013.10.29 5863
1579 SSAS - 반가산 측정값 집계 jevida(강성욱) 2013.10.25 12386
1578 SSAS - 측정값 그룹의 세분성 jevida(강성욱) 2013.10.25 11034
1577 SSAS - 차원용도 jevida(강성욱) 2013.10.25 5938
1576 SSAS - 측정값 그룹 jevida(강성욱) 2013.10.21 5900
1575 SSAS 넌-리프 수준의 멤버 jevida(강성욱) 2013.10.21 6209





XE Login