교착 상태 가이드

적용 대상:SQL 서버 Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics 분석 플랫폼 시스템(PDW)Microsoft Fabric의 SQL 데이터베이스

이 문서에서는 데이터베이스 엔진의 교착 상태에 대해 자세히 설명합니다. 교착 상태는 종종 다단계 트랜잭션에서 데이터베이스의 경쟁적인 동시 잠금으로 인해 발생합니다. 트랜잭션 및 잠금에 대한 자세한 내용은 트랜잭션 잠금 및 행 버전 관리 가이드를 참조하세요.

Azure SQL Database 및 Fabric의 SQL 데이터베이스에서 교착 상태 식별 및 방지에 대한 자세한 내용은 Azure SQL Database 및 Fabric의 SQL 데이터베이스에서 교착 상태 분석 및 방지를 참조하세요.

교착 상태 이해

한 태스크에서 잠근 리소스를 다른 태스크에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하면 교착 상태가 발생합니다. 다음은 그 예입니다.

트랜잭션 A가 1행에 대한 공유 잠금을 획득합니다.
트랜잭션 B가 2행에 대한 공유 잠금을 획득합니다.
이제 트랜잭션 A는 행 2에 대한 배타적 잠금을 요청하고 트랜잭션 B가 완료되고 행 2에 있는 공유 잠금을 해제할 때까지 차단됩니다.
이제 트랜잭션 B는 행 1에 대한 배타적 잠금을 요청하고 트랜잭션 A가 완료되고 행 1에 있는 공유 잠금을 해제할 때까지 차단됩니다.

트랜잭션 B가 완료되어야 트랜잭션 A도 완료될 수 있지만 트랜잭션 B는 트랜잭션 A에 의해 차단된 상태입니다. 이러한 상태를 순환 종속 관계라고 합니다. 트랜잭션 A는 트랜잭션 B에 종속성을 갖고, 트랜잭션 B는 트랜잭션 A에 대한 종속성을 가짐으로써 순환을 닫습니다.

교착 상태가 외부 프로세스에 의해 손상되지 않는 한 교착 상태의 두 트랜잭션은 모두 영원히 대기합니다. 데이터베이스 엔진 교착 상태 모니터는 교착 상태에 있는 작업을 주기적으로 확인합니다. 교착 상태 모니터가 순환 종속성을 감지하면 태스크 중 하나를 희생자로 선택하고 오류와 함께 트랜잭션을 종료합니다. 이렇게 하면 다른 태스크가 해당 트랜잭션을 완료할 수 있습니다. 오류로 종료된 트랜잭션이 있는 애플리케이션은 트랜잭션을 다시 시도할 수 있으며, 일반적으로 교착 상태의 다른 트랜잭션이 완료된 후에 완료됩니다.

교착 상태는 보통 기본 차단과 혼동되는 경우가 많습니다. 트랜잭션이 다른 트랜잭션에 의해 잠긴 리소스에 대한 잠금을 요청하면 요청 트랜잭션은 잠금이 해제될 때까지 대기합니다. 기본적으로 데이터베이스 엔진의 트랜잭션은 설정되지 않는 한 LOCK_TIMEOUT 시간 초과되지 않습니다. 요청 트랜잭션이 잠금을 소유하는 트랜잭션을 차단하기 위해 아무 작업도 수행하지 않았기 때문에 요청 트랜잭션이 교착 상태가 아니면 차단됩니다. 결국 잠금을 소유하는 트랜잭션이 완료되고 잠금을 해제하면 잠금을 요청하는 트랜잭션에 잠금이 허가되고 트랜잭션이 진행됩니다. 교착 상태는 거의 즉시 해결되는 반면, 차단은 이론적으로 무기한 지속될 수 있습니다. 교착 상태는 때때로 치명적인 포옹이라고도 합니다.

교착 상태는 관계형 데이터베이스 관리 시스템뿐만 아니라 다중 스레드를 사용하는 어느 시스템에서나 발생할 수 있으며 데이터베이스 개체에 대한 잠금 이외의 리소스에 대해 발생할 수 있습니다. 예를 들어 다중 스레드 운영 체제의 스레드는 메모리 블록과 같은 하나 이상의 리소스를 획득할 수 있습니다. 획득되는 리소스를 현재 다른 스레드에서 소유하는 경우 첫 번째 스레드는 소유 스레드가 대상 리소스를 해제할 때까지 대기해야 할 수 있습니다. 대기 중인 스레드는 해당 특정 리소스에 대한 소유 스레드에 종속성을 가지고 있다고 합니다. 데이터베이스 엔진의 인스턴스에서 메모리 또는 스레드와 같은 데이터베이스가 아닌 리소스를 획득할 때 세션이 교착 상태에 빠질 수 있습니다.

트랜잭션 교착 상태를 보여 주는 다이어그램

다음 일러스트레이션에서 트랜잭션 T1은 Part 테이블 잠금 리소스에 대한 트랜잭션 T2에 종속성이 있습니다. 마찬가지로 트랜잭션 T2는 Supplier 테이블 잠금 리소스에 대한 트랜잭션 T1에 종속성이 있습니다. 이러한 종속성은 주기를 형성하므로 트랜잭션 T1과 T2 사이에 교착 상태가 발생합니다.

다음은 교착 상태에 대한 보다 일반적인 그림입니다.

교착 상태의 작업을 보여 주는 다이어그램입니다.

태스크 T1에는 리소스 R1(R1에서 T1까지의 화살표로 표시됨)에 대한 잠금이 있으며 리소스 R2에 대한 잠금을 요청했습니다(T1에서 R2로 화살표로 표시됨).
태스크 T2에는 리소스 R2(R2에서 T2까지의 화살표로 표시됨)에 대한 잠금이 있으며 리소스 R1에 대한 잠금을 요청했습니다(T2에서 R1로 화살표로 표시됨).
리소스를 사용할 수 있을 때까지 두 태스크를 계속할 수 없으며 태스크가 계속될 때까지 두 리소스를 모두 해제할 수 없으므로 교착 상태가 존재합니다.

Note

데이터베이스 엔진은 교착 상태 주기를 자동으로 검색합니다. 트랜잭션 중 하나를 교착 상태 피해자로 선택하고 교착 상태를 깨기 위해 오류로 종료합니다.

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

각 사용자 세션에서 각 태스크가 리소스를 획득하거나 획득하기 위해 대기한다면 하나 이상의 태스크가 대신 실행될 수 있습니다. 다음 유형의 리소스가 차단을 일으켜 교착 상태가 발생할 수 있습니다.

Locks. 개체, 페이지, 행, 메타데이터, 애플리케이션 등의 리소스에 대한 잠금을 획득하려고 대기할 때 교착 상태가 발생할 수 있습니다. 예를 들어 트랜잭션 T1에는 r1 행에 대한 공유(S) 잠금이 있으며 r2에서 배타적(X) 잠금을 가져오기 위해 대기 중입니다. 트랜잭션 T2에는 r2에 대한 공유(S) 잠금이 있으며 r1 행에서 배타적(X) 잠금을 받기 위해 대기 중입니다. 이로 인해 T1과 T2가 서로가 잠긴 리소스를 해제할 때까지 대기하는 잠금 주기가 발생합니다.
작업자 스레드 사용 가능한 작업자 스레드를 대기하는 태스크가 교착 상태를 일으킬 수 있습니다. 대기 중인 작업이 모든 작업자 스레드를 차단하는 리소스를 소유하는 경우 교착 상태가 발생합니다. 예를 들어 세션 S1은 트랜잭션을 시작하고 r1 행에서 공유(S) 잠금을 획득한 다음 절전 모드로 이동합니다. 사용 가능한 모든 작업자 스레드에서 실행되는 활성 세션은 행 r1에서 배타적(X) 잠금을 획득하려고 합니다. 세션 S1이 작업자 스레드를 획득할 수 없으므로 트랜잭션을 커밋할 수 없고 r1 행에 대한 잠금을 해제하지 못합니다. 이로 인해 교착 상태가 발생합니다.
Memory. 동시 요청이 사용 가능한 메모리에 만족할 수 없는 메모리 부여를 대기하는 경우 교착 상태가 발생할 수 있습니다. 예를 들어 두 개의 동시 쿼리 Q1과 Q2가 각각 10MB와 20MB의 메모리를 획득하는 사용자 정의 함수를 실행합니다. 각 쿼리에 30MB가 필요하고 사용 가능한 총 메모리는 20MB인 경우 Q1과 Q2는 서로 메모리를 해제할 때까지 대기해야 하며 이로 인해 교착 상태가 발생합니다.
병렬 쿼리 실행 관련 리소스. 교환 포트와 연결된 코디네이터, 생산자 또는 소비자 스레드는 일반적으로 병렬 쿼리의 일부가 아닌 하나 이상의 다른 프로세스를 포함할 때 서로 차단되어 교착 상태가 발생할 수 있습니다. 또한 병렬 쿼리 실행이 시작되면 데이터베이스 엔진은 현재 워크로드에 따라 병렬 처리 수준과 필요한 작업자 스레드 수를 결정합니다. 예를 들어 서버에서 새 쿼리가 실행되기 시작하거나 시스템이 작업자 스레드가 부족한 경우 시스템 워크로드가 예기치 않게 변경되면 교착 상태가 발생할 수 있습니다.
MARS(Multiple Active Result Sets) 리소스. MARS 리소스는 MARS에서 여러 활성 요청의 인터리브를 제어하는 데 사용합니다. 자세한 내용은 SQL Server Native Client에서 MARS(Multiple Active Result Sets) 사용을 참조하세요.
- 사용자 리소스입니다. 스레드가 사용자 애플리케이션에서 제어하는 리소스를 대기할 때 해당 리소스는 외부 또는 사용자 리소스로 간주되고 잠금처럼 처리됩니다.
- 세션 뮤텍스. 한 세션에서 실행되는 태스크는 인터리빙됩니다. 즉, 한 번에 하나의 태스크만 세션에서 실행할 수 있습니다. 세션 뮤텍스를 배타적으로 사용할 수 있어야 태스크가 실행될 수 있습니다.
- 트랜잭션 뮤텍스. 한 세션에서 실행되는 모든 태스크는 인터리빙됩니다. 즉, 주어진 시간 동안 한 번에 하나의 태스크만 세션에서 실행할 수 있습니다. 태스크를 실행하려면 먼저 트랜잭션 뮤텍스에 대한 배타적 액세스 권한이 있어야 합니다.
  
  MARS에서 태스크를 실행하려면 세션 뮤텍스를 획득해야 합니다. 태스크가 트랜잭션에서 실행 중인 경우 트랜잭션 뮤텍스를 획득해야 합니다. 이를 통해 지정된 세션과 지정된 트랜잭션에서 한 번에 한 태스크만 활성화되도록 할 수 있습니다. 필요한 뮤텍스를 획득하면 태스크를 실행할 수 있습니다. 작업이 완료되거나 요청 중간에 생성되면 먼저 트랜잭션 뮤텍스를 해제한 다음 세션 뮤텍스를 취득한 순으로 해제합니다. 그러나 이러한 리소스에서 교착 상태가 발생할 수 있습니다. 다음 의사코드 예제에서는 사용자 요청 U1과 사용자 요청 U2라는 두 태스크가 같은 세션에서 실행되고 있습니다.
```
U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
U2:    Rs2=Command2.Execute("select colA from sometable");
```
  사용자 요청 U1에서 실행되는 저장 프로시저가 세션 뮤텍스를 획득합니다. 저장 프로시저를 실행하는 데 시간이 오래 걸리는 경우 데이터베이스 엔진에서 저장 프로시저가 사용자의 입력을 기다리고 있다고 가정합니다. 사용자가 U2에서 결과 집합을 기다리는 동안 사용자 요청 U2가 세션 뮤텍스를 기다리고 있으며 U1은 사용자 리소스를 기다리고 있습니다. 이는 다음과 같이 논리적으로 설명되는 교착 상태입니다.

테이블이 분할되고 LOCK_ESCALATION의 ALTER TABLE 설정이 AUTO으로 설정된 경우에도 교착 상태가 발생할 수 있습니다. 이 값을 LOCK_ESCALATION설정하면 AUTO 데이터베이스 엔진이 테이블 수준이 아닌 HoBT 수준에서 테이블 파티션을 잠글 수 있도록 하여 동시성이 증가합니다. 그러나 개별 트랜잭션이 테이블에 파티션 잠금을 보유하고 다른 트랜잭션 파티션에서 잠금을 원하면 교착 상태가 발생합니다. 이 교착 상태 유형은 LOCK_ESCALATION을 TABLE로 설정하여 방지할 수 있습니다. 그러나 이 설정은 파티션에 대한 대규모 업데이트가 테이블 잠금을 기다리도록 강제하여 동시성을 줄입니다.

교착 상태 탐지

교착 상태일 수 있는 리소스 섹션에 나열된 모든 리소스는 데이터베이스 엔진 교착 상태 검색 체계에 참여합니다. 교착 상태 검색은 데이터베이스 엔진 인스턴스의 모든 작업을 통해 주기적으로 검색을 시작하는 잠금 모니터 스레드에 의해 수행됩니다. 다음은 검색 프로세스에 대한 설명입니다.

기본 시간 간격은 5초로 설정됩니다.
잠금 모니터 스레드가 교착 상태를 발견하면 잠금 상태의 빈도에 따라 5초에서 최하 100밀리초까지 교착 상태 검색 간격이 짧아집니다.
잠금 모니터 스레드가 교착 상태 찾기를 중지하면 데이터베이스 엔진은 검색 간격을 5초로 늘입니다.
교착 상태가 감지되면 잠금을 기다려야 하는 새 스레드가 교착 상태 주기에 진입하는 것으로 가정합니다. 교착 상태 검색 후 처음 몇 번의 잠금 대기에서 다음 교착 상태 검색 간격을 대기하지 않고 교착 상태 검색을 즉시 트리거합니다. 예를 들어 현재 간격이 5초일 경우 교착 상태가 검색되면 다음 잠금 대기에서 교착 상태 검색기를 즉시 시작합니다. 이 잠금 대기가 교착 상태의 일부인 경우, 다음 교착 상태 검색 중이 아니라 즉시 감지됩니다.

데이터베이스 엔진은 일반적으로 주기적인 교착 상태 검색만 수행합니다. 시스템에서 발생하는 교착 상태의 수는 일반적으로 적기 때문에 주기적인 교착 상태 검색은 시스템에서 교착 상태 검색의 오버헤드를 줄이는 데 도움이 됩니다.

잠금 모니터가 특정 스레드에 대한 교착 상태 검색을 시작하면 스레드가 대기 중인 리소스를 식별합니다. 그런 다음 잠금 모니터는 해당 특정 리소스에 대한 소유자를 찾고 주기를 찾을 때까지 해당 스레드에 대한 교착 상태 검색을 재귀적으로 계속합니다. 이러한 방식으로 식별된 주기는 교착 상태를 형성합니다.

교착 상태가 감지되면 데이터베이스 엔진은 스레드 중 하나를 교착 상태 희생자로 선택하여 교착 상태를 종료합니다. 데이터베이스 엔진은 스레드에 대해 실행 중인 현재 일괄 처리를 종료하고, 교착 상태 피해자의 트랜잭션을 롤백하고, 오류 1205를 애플리케이션에 반환합니다. 교착 상태 희생자에 대한 트랜잭션을 롤백하면 트랜잭션이 보유한 모든 잠금이 해제됩니다. 이렇게 하면 다른 스레드의 트랜잭션이 차단 해제되고 계속할 수 있습니다. 1205(교착 상태 피해자) 오류는 교착 상태에 관련된 리소스 유형에 대한 정보를 기록합니다.

기본적으로 데이터베이스 엔진은 롤백 비용이 가장 낮은 트랜잭션을 교착 상태 희생자로 선택합니다. 또는 사용자가 SET DEADLOCK_PRIORITY 문을 사용하여 교착 상태에 있는 세션의 우선 순위를 지정할 수 있습니다. DEADLOCK_PRIORITY를 설정하거나 LOW로 설정하거나 NORMAL로 설정하거나 HIGH로 설정하거나, -10에서 10까지의 범위 내의 정수 값으로 설정할 수 있습니다. 경우에 따라 데이터베이스 엔진은 더 나은 동시성을 달성하기 위해 짧은 기간 동안 교착 상태 우선 순위를 변경하도록 선택할 수 있습니다.

교착 상태 우선 순위는 기본적으로 0으로 설정됩니다 NORMAL. 두 세션의 교착 상태 우선 순위가 서로 다른 경우 우선 순위가 낮은 세션의 트랜잭션이 교착 상태 희생자로 선택됩니다. 두 세션의 교착 상태 우선 순위가 같으면 롤백 비용이 가장 낮은 트랜잭션이 선택됩니다. 교착 상태 주기와 관련된 세션의 교착 상태 우선 순위와 비용이 같으면 희생자가 임의로 선택됩니다. 롤백하고 있는 태스크를 처리하지 않도록 선택할 수는 없습니다.

CLR(공용 언어 런타임)을 사용하는 경우 교착 상태 모니터는 관리 프로시저 내에서 액세스하는 동기화 리소스(모니터, 판독기/기록기 잠금 및 스레드 조인)에 대한 교착 상태를 자동으로 검색합니다. 그러나 교착 상태는 교착 상태의 희생자로 선택된 프로시저에서 예외를 throw하여 해결됩니다. 처리하지 않도록 선택된 프로시저에서 현재 소유하고 있는 리소스가 예외를 통해 자동으로 해제되지는 않습니다. 리소스는 명시적으로 해제해야 합니다. 예외 동작과 일치하면 교착 상태 피해자를 식별하는 데 사용되는 예외를 catch하고 해제할 수 있습니다.

교착 상태 정보 도구

교착 상태 정보를 보기 위해 데이터베이스 엔진은 SQL Profiler에서 확장 이벤트, 두 개의 추적 플래그 및 교착 상태 그래프 이벤트의 형태로 xml_deadlock_report 모니터링 도구를 제공합니다.

xml_deadlock_report 확장 이벤트는 교착 상태 정보를 캡처하는 데 권장되는 방법입니다.

교착 상태 확장 이벤트

SQL Server 2012(11.x) 이상 버전 xml_deadlock_report 에서는 SQL Trace 또는 SQL Profiler의 교착 상태 그래프 이벤트 클래스 대신 확장 이벤트를 사용해야 합니다.

system_health 이벤트 세션은 기본적으로 이벤트를 캡처합니다xml_deadlock_report. 이러한 이벤트에는 교착 상태 그래프가 포함됩니다. 세션은 system_health 기본적으로 사용하도록 설정되어 있으므로 교착 상태 정보를 캡처하도록 별도의 이벤트 세션을 구성할 필요가 없습니다.

교착 상태 그래프에는 일반적으로 세 개의 서로 다른 노드가 있습니다.

victim-list; 교착 상태의 피해자 프로세스 식별자
process-list; 교착 상태에 관련된 모든 프로세스에 대한 정보입니다.
resource-list; 교착 상태에 관련된 리소스에 대한 정보입니다.

Management Studio에서 event_file 세션의 system_health 대상 데이터를 볼 수 있습니다. xml_deadlock_report 이벤트가 발생한 경우 Management Studio는 다음 예제와 같이 교착 상태에 관련된 작업 및 리소스를 그래픽으로 보여 줍니다.

다음 쿼리는 세션의 ring_buffer 대상에 의해 system_health 캡처된 모든 교착 상태 이벤트를 볼 수 있습니다.

SELECT xdr.value('@timestamp', 'datetime') AS deadlock_time,
       xdr.query('.') AS event_data
FROM (SELECT CAST ([target_data] AS XML) AS target_data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY deadlock_time DESC;

결과 집합은 다음과 같습니다.

system_health XEvent 쿼리 결과의 SSMS 스크린샷.

다음 예제는 event_data 열의 출력 예를 보여줍니다.

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

추적 플래그 1204 및 추적 플래그 1222

교착 상태가 발생하고 추적 플래그 1204 또는 추적 플래그 1222를 사용하도록 설정하면 SQL Server 오류 로그에 교착 상태 세부 정보가 보고됩니다. 추적 플래그 1204는 교착 상태에 관련된 각 노드에 의해 형식이 지정된 교착 상태 정보를 보고합니다. 추적 플래그 1222는 교착 상태 정보를 먼저 프로세스별로 포맷한 다음 리소스별로 서식을 지정합니다. 두 추적 플래그를 모두 사용하여 동일한 교착 상태 이벤트의 두 표현을 가져올 수 있습니다.

Important

교착 상태가 발생하는 워크로드 집약적 시스템에서 추적 플래그 1204 및 1222를 사용하지 않습니다. 이러한 추적 플래그를 사용하면 성능 문제가 발생할 수 있습니다. 대신 Deadlock 확장 이벤트를 사용하여 필요한 정보를 캡처합니다.

다음 표에서는 추적 플래그 1204 및 1222의 속성을 정의하는 것 외에도 유사점과 차이점을 보여 줍니다.

Property	추적 플래그 1204 및 추적 플래그 1222	추적 플래그는 1204만 사용하여	추적 플래그 1222만 사용
출력 형식	출력은 SQL Server 오류 로그에 캡처됩니다.	교착 상태에 관련된 노드에 초점을 맞췄습니다. 각 노드에는 전용 섹션이 있으며, 마지막 섹션에서는 교착 상태 피해자에 대해 설명합니다.	XSD(XML 스키마 정의) 스키마를 준수하지 않는 XML과 유사한 형식의 정보를 반환합니다. 형식에는 세 가지 주요 섹션이 있습니다. 첫 번째 섹션에서는 교착 상태 피해자를 선언합니다. 두 번째 섹션에서는 교착 상태와 관련된 각 프로세스에 대해 설명합니다. 세 번째 섹션에서는 추적 플래그 1204의 노드와 동의어인 리소스에 대해 설명합니다.
특성 식별	`SPID:<x> ECID:<x>.` 병렬 프로세스의 경우 세션 ID 스레드를 식별합니다. `SPID:<x> ECID:0`가 SPID 값으로 대체되는 항목인 `<x>`은(는) 주 스레드를 나타냅니다. SPID 값으로 대체된 `SPID:<x> ECID:<y>`, `<x>` 값이 0보다 클 때 `<y>` 항목은 동일한 SPID의 실행 컨텍스트를 나타냅니다. `BatchID` (`sbid` 추적 플래그 1222의 경우). 코드 실행이 잠금을 요청하거나 보유하는지 배치를 식별합니다. MARS(다중 활성 결과 집합)를 사용하지 않도록 설정하면 BatchID 값은 0입니다. MARS를 사용하도록 설정하면 활성 일괄 처리의 값은 1에서 n입니다. 세션에 활성 배치가 없으면 BatchID는 0입니다. `Mode` 스레드에서 요청, 부여 또는 대기한 특정 리소스에 대한 잠금 유형을 지정합니다. 모드는 의도 공유(`IS`), 공유(`S`), 업데이트(`U`), 의도 배타적(`IX`), 의도 배타적 공유(`SIX`), 배타적(`X`)일 수 있습니다. `Line #` (`line` 추적 플래그 1222의 경우). 교착 상태가 발생했을 때 실행 중인 문의 현재 배치에 있는 줄 번호를 나열합니다. `Input Buf` (`inputbuf` 추적 플래그 1222의 경우). 현재 배치에 있는 문의 수입니다.	`Node` 교착 상태 체인의 항목 번호를 나타냅니다. `Lists` 잠금 소유자는 다음 목록에 속할 수 있습니다. `Grant List` 리소스의 현재 소유자를 열거합니다. `Convert List` 잠금을 더 높은 수준으로 변환하려는 현재 소유자를 열거합니다. `Wait List` 리소스에 대한 현재 새 잠금 요청을 열거합니다. `Statement Type`스레드에 사용 권한이 있는 문(`SELECT`, `INSERT`또는`UPDATEDELETE`)의 형식에 대해 설명합니다. `Victim Resource Owner` 데이터베이스 엔진이 교착 상태 주기를 중단하기 위해 희생자로 선택하는 참여 스레드를 지정합니다. 선택한 스레드와 모든 실행 컨텍스트가 종료됩니다. `Next Branch` 교착 상태 주기에 관련된 동일한 SPID의 두 개 이상의 실행 컨텍스트를 나타냅니다.	`deadlock victim` 교착 상태의 희생자로 선택된 작업의 실제 메모리 주소(sys.dm_os_tasks 참조)를 나타냅니다. 해결되지 않은 교착 상태의 경우 값이 0일 수 있습니다. `executionstack` 교착 상태가 발생할 때 실행 중인 Transact-SQL 호출 스택을 나타냅니다. `priority` 교착 상태 우선 순위를 나타냅니다. `logused` 작업에서 사용하는 로그 공간입니다. `owner id` 요청을 제어하는 트랜잭션의 ID입니다. `status` 태스크의 상태입니다. 자세한 내용은 sys.dm_os_tasks 참조하세요. `waitresource` 태스크에 필요한 리소스입니다. `waittime` 리소스를 기다리는 시간(밀리초)입니다. `schedulerid` 이 작업과 연결된 스케줄러입니다. sys.dm_os_schedulers 참조하세요. `hostname` 워크스테이션의 이름입니다. `isolationlevel` 현재 트랜잭션 격리 수준입니다. `Xactid` 요청을 제어하는 트랜잭션의 ID입니다. `currentdb` 데이터베이스 ID입니다. `lastbatchstarted` 클라이언트 프로세스가 배치 실행을 마지막으로 시작한 시간입니다. `lastbatchcompleted` 클라이언트 프로세스가 배치 실행을 마지막으로 완료한 시간입니다. `clientoption1` 및 `clientoption2` 이 세션의 설정 옵션입니다. 이러한 값은 일반적으로 `SET` 문과 같은 문으로 제어되는 `SET NOCOUNT` 및 `SET XACTABORT` 옵션을 나타내는 비트 마스크입니다. 자세한 내용은 @@OPTIONS 참조하세요. `associatedObjectId` HoBT(힙 또는 B-트리) ID를 나타냅니다.
리소스 특성	`RID` 잠금이 유지되거나 요청되는 테이블 내의 단일 행을 식별합니다. RID는 RID: `db_id:file_id:page_no:row_no` 표시됩니다. 예들 들어 `RID: 6:1:20789:0`입니다. `OBJECT` 잠금이 유지되거나 요청되는 테이블을 식별합니다. `OBJECT`은 `OBJECT: db_id:object_id`(으)로 표시됩니다. 예들 들어 `TAB: 6:2009058193`입니다. `KEY` 잠금이 유지되거나 요청되는 인덱스 내의 키 범위를 식별합니다. KEY는 KEY: `db_id:hobt_id`(index key hash value) 표시됩니다. 예들 들어 `KEY: 6:72057594057457664 (350007a4d329)`입니다. `PAG` 잠금이 보유 또는 요청된 페이지 리소스를 식별합니다. `PAG`은 `PAG: db_id:file_id:page_no`(으)로 표시됩니다. 예들 들어 `PAG: 6:1:20789`입니다. `EXT` 익스텐트 구조를 식별합니다. `EXT`은 `EXT: db_id:file_id:extent_no`(으)로 표시됩니다. 예들 들어 `EXT: 6:1:9`입니다. `DB` 데이터베이스 잠금을 식별합니다. `DB`는 다음 방법 중 하나로 표시됩니다. `DB: db_id` `DB: db_id[BULK-OP-DB]`- 데이터베이스 백업에서 수행한 데이터베이스 잠금을 식별합니다. `DB: db_id[BULK-OP-LOG]`는 로그 백업에서 취한 잠금을 식별합니다. `APP` 애플리케이션 잠금을 식별합니다. `APP`은 `APP: lock_resource`(으)로 표시됩니다. 예들 들어 `APP: Formf370f478`입니다. `METADATA` 교착 상태에 관련된 메타데이터 리소스를 나타냅니다. `METADATA`에는 많은 하위 리소스가 있으므로 반환되는 값은 교착 상태가 있는 하위 리소스에 따라 달라집니다. 예를 들어 `METADATA.USER_TYPE`는 `user_type_id = integer_value`를 반환합니다. `METADATA` 리소스 및 하위 리소스에 대한 자세한 내용은 sys.dm_tran_locks를 참조하세요. `HOBT` 교착 상태와 관련된 힙 또는 B-트리를 나타냅니다.	이 추적 플래그에만 해당되지 않습니다.	이 추적 플래그에만 해당되지 않습니다.

추적 플래그 1204 예제

다음 예제에서는 추적 플래그 1204가 켜져 있는 경우의 출력을 보여줍니다. 이 경우 노드 1의 테이블은 인덱스가 없는 힙이고 노드 2의 테이블은 비클러스터형 인덱스가 있는 힙입니다. 교착 상태가 발생하면 노드 2의 인덱스 키가 업데이트됩니다.

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

추적 플래그 1222 예제

다음 예제에서는 추적 플래그 1222가 켜져 있는 경우의 출력을 보여 집니다. 이 경우 첫 번째 테이블은 인덱스가 없는 힙이고 다른 테이블은 비클러스터형 인덱스가 있는 힙입니다. 두 번째 테이블에서 교착 상태가 발생하면 인덱스 키가 업데이트됩니다.

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444
   transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:42.733 XDES=0x3a3dad0
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54
   sbid=0 ecid=0 priority=0 transcount=2
   lastbatchstarted=2022-02-05T11:22:42.733
   lastbatchcompleted=2022-02-05T11:22:42.733
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310444 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p1 line=6 stmtstart=202
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380
   waitresource=KEY: 6:72057594057457664 (350007a4d329)
   waittime=5015 ownerId=310462 transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:44.077 XDES=0x4d9e258 lockMode=U
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0
   priority=0 transcount=2 lastbatchstarted=2022-02-05T11:22:44.077
   lastbatchcompleted=2022-02-05T11:22:44.077
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310462 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p2 line=6 stmtstart=200
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2022.dbo.T2
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2022.dbo.T1
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Profiler 교착 상태 그래프 이벤트

SQL Profiler에는 교착 상태와 관련된 작업 및 리소스에 대한 그래픽 묘사를 표시하는 이벤트가 있습니다. 다음 예에서는 교착 상태 그래프 이벤트가 설정되어 있을 때 SQL Profiler의 출력을 보여 줍니다.

SQL 프로파일러 및 SQL 추적 기능은 더 이상 사용되지 않으며 확장 이벤트로 대체됩니다. 확장 이벤트는 성능 오버헤드가 적으며 SQL 추적보다 더 구성 가능합니다. SQL Profiler에서 교착 상태를 추적하는 대신 확장 이벤트 교착 상태 이벤트를 사용하는 것이 좋습니다.

SQL 추적에서 시각적 교착 상태 그래프의 SSMS 스크린샷.

교착 상태 이벤트에 대한 자세한 내용은 Lock:Deadlock 이벤트 클래스를 참조하세요. SQL Profiler 교착 상태 그래프에 대한 자세한 내용은 교착 상태 그래프 저장(SQL Server Profiler)을 참조하세요.

확장 이벤트는 SQL Trace 이벤트 클래스에 해당하는 클래스를 제공합니다. 자세한 내용은 SQL 추적 이벤트 클래스에 해당하는 확장 이벤트 보기를 참조하세요. 확장 이벤트는 SQL 추적 대신 권장됩니다.

교착 상태 처리

데이터베이스 엔진 인스턴스가 트랜잭션을 교착 상태의 희생자로 선택하면 현재 일괄 처리를 종료하고 트랜잭션을 롤백하며 오류 1205를 애플리케이션에 반환합니다. 반환된 메시지는 다음과 같이 구조화됩니다.

Your transaction (process ID #...) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Transact-SQL 쿼리를 제출하는 모든 애플리케이션을 교착 상태의 희생자로 선택할 수 있으므로 애플리케이션에는 오류 1205를 처리할 수 있는 오류 처리기가 있어야 합니다. 애플리케이션이 오류를 처리하지 않는 경우 애플리케이션은 트랜잭션이 롤백되었다는 사실을 모르고 계속 진행할 수 있습니다.

오류 1205를 catch하는 오류 처리기를 구현하면 애플리케이션에서 교착 상태를 처리하고 수정 작업을 수행할 수 있습니다(예: 교착 상태에 관련된 쿼리를 자동으로 다시 제출).

쿼리를 다시 전송하기 전에 애플리케이션이 일시 중지되어야 합니다. 이렇게 하면 교착 상태에 관련된 다른 트랜잭션이 잠금을 완료하고 해제할 수 있습니다. 일시 중지 기간을 임의로 지정하면 다시 제출된 쿼리가 잠금을 요청할 때 교착 상태가 다시 발생할 가능성이 최소화됩니다. 예를 들어 오류 처리기는 1초에서 3초 사이의 임의 기간 동안 일시 중지하도록 코딩될 수 있습니다.

TRY...CATCH를 사용하여 처리

교착 상태를 처리하기 위해 TRY...CATCH를 사용할 수 있습니다. 오류 1205는 CATCH 블록에서 포착될 수 있습니다.

자세한 내용은 교착 상태 처리를 참조하세요.

교착 상태 최소화

교착 상태를 완전히 방지할 수는 없지만 특정 코딩 규칙을 따르면 교착 상태 발생을 최소화할 수 있습니다. 교착 상태를 최소화하면 트랜잭션 처리량이 늘어나고 더 적은 수의 트랜잭션이 다음과 같이 되므로 시스템 오버헤드가 줄어듭니다.

롤백되어 트랜잭션에 의해 수행된 모든 작업이 실행 취소됩니다.
교착 상태 발생 시 롤백되었으므로 애플리케이션에 의해 다시 전송됩니다.

교착 상태를 최소화하기

같은 순서로 개체에 액세스합니다.
트랜잭션에서 사용자 상호 작용을 피합니다.
트랜잭션을 하나의 일괄 처리로 짧게 유지합니다.
필요하지 않은 경우와 같은 REPEATABLE READSERIALIZABLE 더 높은 격리 수준을 피합니다.
행 버전 관리 기반의 격리 수준을 사용합니다.
- 트랜잭션에 행 버전 관리를 사용하려면 READ_COMMITTED_SNAPSHOT 데이터베이스 옵션을 활성화하여 READ COMMITTED 격리 수준을 사용하십시오.
- 스냅샷 격리 트랜잭션을 사용합니다.
바인딩된 연결을 사용합니다..

같은 순서로 개체에 액세스

모든 동시 트랜잭션이 동일한 순서로 개체에 액세스하는 경우 교착 상태가 발생할 가능성이 적습니다. 예를 들어 두 개의 동시 트랜잭션이 Supplier 테이블에 대한 잠금을 가져온 다음 Part 테이블에서 다른 트랜잭션이 완료될 때까지 한 트랜잭션이 Supplier 테이블에서 차단 됩니다. 첫 번째 트랜잭션이 커밋되거나 롤백된 후 두 번째가 계속되므로 교착 상태는 발생하지 않습니다. 모든 데이터 수정에 저장 프로시저를 사용하면 개체 액세스 순서를 표준화할 수 있습니다.

교착 상태의 다이어그램입니다.

트랜잭션에서 사용자 상호 작용 피하기

사용자 개입 없이 실행되는 일괄 처리 속도는 애플리케이션에서 요청한 매개 변수에 대한 프롬프트에 회신하는 것과 같이 사용자가 쿼리에 수동으로 응답해야 하는 속도보다 훨씬 빠르므로 사용자 상호 작용을 포함하는 트랜잭션을 방지합니다. 이 경우 트랜잭션에서 보유한 잠금은 트랜잭션이 커밋 또는 롤백되어야 해제되므로 시스템 처리량이 줄어듭니다. 교착 상태가 발생하지 않더라도 동일한 리소스에 액세스하는 다른 트랜잭션은 트랜잭션이 완료되기를 기다리는 동안 차단됩니다.

트랜잭션을 하나의 일괄 처리로 짧게 유지

교착 상태는 보통 오래 실행되는 여러 개의 트랜잭션이 같은 데이터베이스에서 동시에 실행될 때 발생합니다. 트랜잭션 실행 시간이 길어질수록 배타적 또는 업데이트 잠금 보유 시간이 길어지므로 다른 작업이 차단되고 교착 상태 상황이 발생할 수 있습니다.

트랜잭션을 하나의 일괄 처리로 유지하면 트랜잭션 중에 네트워크 왕복이 최소화되어 클라이언트 처리로 인해 트랜잭션 완료가 지연될 수 있습니다.

더 높은 격리 수준 방지

잠금 경쟁이 줄어들도록 트랜잭션을 더 낮은 격리 수준에서 실행할 수 있는지 확인합니다. READ COMMITTED를 사용하면 트랜잭션이 완료될 때까지 기다리지 않고 다른 트랜잭션이 이전에 읽었으나 수정하지 않은 데이터를 읽을 수 있습니다. READ COMMITTED 와 같이 SERIALIZABLE더 높은 격리 수준보다 짧은 기간 동안 공유 잠금을 보유합니다. 이렇게 하면 잠금 경합이 줄어듭니다.

행 버전 관리 기반의 격리 수준 사용

READ_COMMITTED_SNAPSHOT 데이터베이스 옵션을 설정ON하면 격리 수준에서 실행되는 READ COMMITTED 트랜잭션은 읽기 작업 중에 공유 잠금 대신 행 버전 관리가 사용됩니다.

팁 (조언)

애플리케이션이 잠금 기반 READ COMMITTED 격리 수준의 차단 동작에 의존하지 않는 한, Microsoft는 모든 애플리케이션에 대해 행 버전 관리 기반 READ COMMITTED 격리 수준을 권장합니다.

스냅샷 격리는 읽기 작업 중에 공유 잠금을 사용하지 않는 행 버전 관리 또한 사용합니다. 스냅샷 격리 상태에서 트랜잭션을 실행하려면 먼저 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정해야 합니다.

행 버전 관리 기반 격리 수준을 사용하여 읽기 및 쓰기 작업 간에 발생할 수 있는 교착 상태를 최소화합니다.

바인딩된 연결 사용

바인딩된 연결을 사용하면 동일한 애플리케이션에서 열린 두 개 이상의 연결이 서로 협력할 수 있습니다. 보조 연결에서 얻은 잠금은 기본 연결에서 얻은 것과 같이 유지되며 반대의 경우도 마찬가지입니다. 따라서 서로를 차단하지 않습니다.

교착 상태 발생

학습 또는 데모를 위해 교착 상태를 발생시켜야 할 수 있습니다.

다음 예제는 AdventureWorksLT2019 경우 기본 스키마 및 데이터를 사용하여 샘플 데이터베이스에서 작동 합니다. 이 샘플을 다운로드하려면 AdventureWorks 샘플 데이터베이스를 방문 하세요.

최적화된 잠금을 사용할 때 교착 상태를 발생시키는 예제는 최적화된 잠금 및 교착 상태를 참조하세요.

교착 상태를 발생시키려면 두 개의 세션을 AdventureWorksLT2019 데이터베이스에 연결해야 합니다. 이러한 세션을 세션 A 및 세션 B라고 합니다. SSMS(SQL Server Management Studio)에서 두 개의 쿼리 창을 만들어 이러한 두 세션을 만들 수 있습니다.

세션 A에서 다음 일괄 처리를 실행합니다. 이 코드는 명시적 트랜잭션 을 시작하고 테이블을 업데이트하는 문을 실행합니다 SalesLT.Product . 이를 위해 트랜잭션은 테이블 의 한정 행에 대한 SalesLT.Product을 획득한 다음 배타적(X) 잠금으로 변환됩니다. 트랜잭션을 열어 둡니다.

BEGIN TRANSACTION;

UPDATE SalesLT.Product
    SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

이제 세션 B에서 다음 일괄 처리를 실행합니다. 이 코드는 트랜잭션을 명시적으로 시작하지 않습니다. 대신 자동 커밋 트랜잭션 모드에서 작동합니다. 이 문은 SalesLT.ProductDescription 테이블을 업데이트합니다. 업데이트는 U 테이블 내의 자격을 갖춘 행들에 대한 업데이트(SalesLT.ProductDescription) 잠금을 설정합니다. 쿼리는 SalesLT.Product 테이블을 포함하여 다른 테이블에 조인됩니다.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

이 업데이트를 완료하려면 세션 B는 테이블S의 행에 대한 공유 (SalesLT.Product) 잠금이 필요하며, 여기에는 세션 A에 의해 잠긴 행도 포함됩니다. 세션 B는 SalesLT.Product에서 차단됩니다.

세션 A로 돌아갑니다. 다음 문을 실행합니다UPDATE. 이 명령문은 이전에 열린 트랜잭션의 일부로 실행됩니다.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

세션 A의 두 번째 업데이트 문은 의 SalesLT.ProductDescription에 의해 차단됩니다.

세션 A와 세션 B가 이제 서로를 차단하고 있습니다. 두 트랜잭션 모두 서로에 의해 잠긴 리소스가 필요하므로 진행할 수 없습니다.

몇 초 후 교착 상태 모니터는 세션 A와 세션 B의 트랜잭션이 서로를 차단하고 있으며 둘 다 진행할 수 없음을 식별합니다. 세션 A 가 교착 상태 희생자로 선택된 상태에서 교착 상태가 발생하는 것을 볼 수 있습니다. 세션 B가 완료되었습니다. 다음 예제와 유사한 텍스트가 있는 세션 A 의 쿼리 창에 오류 메시지가 나타납니다.

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

교착 상태가 발생하지 않으면 READ_COMMITTED_SNAPSHOT을 샘플 데이터베이스에서 사용하도록 설정할 수 있는지 확인합니다. 교착 상태는 모든 데이터베이스 구성에서 발생할 수 있지만 이 예제에서는 READ_COMMITTED_SNAPSHOT을(를) 사용하도록 설정해야 합니다.

SQL Server 및 Azure SQL Managed Instance에서 기본적으로 활성화된 이벤트 세션의 ring_buffer 대상에서 교착 상태의 system_health 세부 정보를 확인할 수 있습니다. 다음과 같은 쿼리를 고려해 보세요.

WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
    FROM sys.dm_xe_sessions AS xs
         INNER JOIN sys.dm_xe_session_targets AS xst
             ON xs.[address] = xst.event_session_address
    WHERE xs.[name] = 'system_health'
          AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
       x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
       DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
      FROM cteDeadLocks AS c
      CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;

하이퍼링크로 표시될 셀을 선택하여 SSMS 내의 Deadlock_XML 열에서 XML을 볼 수 있습니다. 이 출력을 .xdl파일로 저장하고, 닫은 다음, 시각적 교착 상태 그래프를 위해 SSMS에서 .xdl파일을 다시 엽니다. 교착 상태 그래프는 다음 이미지와 같이 표시됩니다.

최적화된 잠금 및 교착 상태

최적화된 잠금을 사용하면 페이지 및 행 잠금이 트랜잭션이 끝날 때까지 유지되지 않습니다. 행이 업데이트되는 즉시 해제됩니다. 또한 READ_COMMITTED_SNAPSHOT가 활성화된 경우, 업데이트(U) 잠금이 사용되지 않습니다. 따라서 교착 상태가 발생할 가능성이 줄어듭니다.

이전 예제에서는 업데이트(U) 잠금을 사용하므로 최적화된 잠금을 사용할 때 교착 상태가 발생하지 않습니다.

다음 예제는 최적화된 잠금을 사용하도록 설정된 데이터베이스에서 교착 상태를 일으키는 데 사용할 수 있습니다.

먼저 예제 테이블을 만들고 데이터를 추가합니다.

CREATE TABLE t2
(
    a INT PRIMARY KEY NOT NULL,
    b INT NULL
);

INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);

다음 T-SQL 일괄 처리는 두 개의 별도 세션에서 순서대로 실행되어 교착 상태를 만듭니다.

세션 1에서:

BEGIN TRANSACTION xactA;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

세션 2에서:

BEGIN TRANSACTION xactB;

UPDATE t2
    SET b = b + 10
WHERE a = 2;