교착 상태 가이드

아티클
10/09/2023

적용 대상:SQL Server Azure SQL Database Azure SQL Managed InstanceAzure Synapse Analytics Analytics Platform System(PDW)

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

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

교착 상태 이해

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

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

트랜잭션 B가 완료될 때까지 트랜잭션 A를 완료할 수 없지만 트랜잭션 B는 트랜잭션 A에 의해 차단됩니다. 이 조건을 순환 종속성이라고도 합니다. 트랜잭션 A는 트랜잭션 B에 대한 종속성을 가지며 트랜잭션 B는 트랜잭션 A에 대한 종속성을 사용하여 원을 닫습니다.

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

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

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

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

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

교착 상태 감지 및 종료

한 태스크에서 잠근 리소스를 다른 태스크에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하면 교착 상태가 발생합니다. 다음 그래프는 교착 상태의 상위 수준 보기를 표시합니다.

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

SQL Server 데이터베이스 엔진 SQL Server 내에서 교착 상태 주기를 자동으로 검색합니다. SQL Server 데이터베이스 엔진 세션 중 하나를 교착 상태 희생자로 선택하고 현재 트랜잭션이 종료되어 교착 상태를 깨뜨릴 수 있습니다.

교착 상태에 빠질 수 있는 리소스

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

잠금. 개체, 페이지, 행, 메타데이터 및 애플리케이션과 같은 리소스에 대한 잠금을 획득하기 위해 대기하면 교착 상태가 발생할 수 있습니다. 예를 들어 트랜잭션 T1에는 r1 행에 대한 공유(S) 잠금이 있으며 r2에서 배타적(X) 잠금을 가져오기 위해 대기 중입니다. 트랜잭션 T2가 r2에 대한 공유(S) 잠금을 가지고 있고 r1 행에 대한 배타적(X) 잠금을 얻으려고 대기 중입니다. 이로 인해 T1과 T2가 서로가 잠긴 리소스를 해제할 때까지 기다리는 잠금 주기가 발생합니다.
작업자 스레드. 대기 중인 작업에서 사용 가능한 작업자 스레드를 대기하면 교착 상태가 발생할 수 있습니다. 대기 중인 작업이 모든 작업자 스레드를 차단하는 리소스를 소유하는 경우 교착 상태가 발생합니다. 예를 들어 세션 S1이 트랜잭션을 시작하고 r1 행에 대한 공유(S) 잠금을 획득한 후 중지됩니다. 사용 가능한 모든 작업자 스레드에서 실행 중인 활성 세션이 r1 행에 대한 배타적(X) 잠금을 획득하려고 합니다. 세션 S1이 작업자 스레드를 획득할 수 없으므로 트랜잭션을 커밋할 수 없고 r1 행에 대한 잠금을 해제하지 못합니다. 이로 인해 교착 상태가 발생합니다.
메모리. 동시 요청이 사용 가능한 메모리에 만족할 수 없는 메모리 부여를 기다리는 경우 교착 상태가 발생할 수 있습니다. 예를 들어 두 개의 동시 쿼리 Q1과 Q2가 각각 10MB와 20MB의 메모리를 획득하는 사용자 정의 함수를 실행합니다. 각 쿼리에 30MB가 필요하고 사용 가능한 총 메모리는 20MB인 경우 Q1과 Q2는 서로 메모리를 해제할 때까지 대기해야 하며 이로 인해 교착 상태가 발생합니다.
병렬 쿼리 실행 관련 리소스입니다. 교환 포트와 연결된 코디네이터, 생산자 또는 소비자 스레드는 일반적으로 병렬 쿼리의 일부가 아닌 하나 이상의 다른 프로세스를 포함할 때 서로 차단되어 교착 상태가 발생할 수 있습니다. 또한 병렬 쿼리 실행이 시작되면 SQL Server는 현재 워크로드에 따라 병렬 처리 수준 또는 작업자 스레드 수를 결정합니다. 예를 들어 서버에서 새 쿼리가 실행되기 시작하거나 시스템이 작업자 스레드가 부족한 경우 시스템 워크로드가 예기치 않게 변경되면 교착 상태가 발생할 수 있습니다.
MARS(다중 활성 결과 집합) 리소스입니다. MARS 리소스는 MARS에서 여러 활성 요청의 인터리브를 제어하는 데 사용합니다. 자세한 내용은 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에서 실행되는 저장 프로시저가 세션 뮤텍스를 획득합니다. 저장 프로시저를 실행하는 데 시간이 오래 걸리는 경우 저장 프로시저가 사용자의 입력을 기다리고 있다고 SQL Server 데이터베이스 엔진 가정합니다. 사용자가 U2에서 결과 집합을 기다리는 동안 사용자 요청 U2가 세션 뮤텍스를 기다리고 있으며 U1은 사용자 리소스를 기다리고 있습니다. 이는 다음과 같이 논리적으로 설명되는 교착 상태입니다.

교착 상태 검색

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

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

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

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

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

기본적으로 SQL Server 데이터베이스 엔진 롤백 비용이 가장 낮은 트랜잭션을 실행하는 세션에서 교착 상태의 희생자로 선택합니다. 또는 사용자가 SET DEADLOCK_PRIORITY 문을 사용하여 교착 상태에 있는 세션의 우선 순위를 지정할 수 있습니다. DEADLOCK_PRIORITY LOW, NORMAL 또는 HIGH로 설정하거나 범위의 정수 값(-10~10)으로 설정할 수 있습니다. 교착 상태 우선 순위는 기본적으로 NORMAL입니다. 두 세션의 교착 상태 우선 순위가 다르면 교착 상태 우선 순위가 낮은 세션이 처리하지 않을 세션으로 선택됩니다. 두 세션의 교착 상태 우선 순위가 같으면 롤백 비용이 가장 낮은 트랜잭션이 있는 세션이 선택됩니다. 교착 상태 주기와 관련된 세션의 교착 상태 우선 순위와 비용이 같으면 피해자가 임의로 선택됩니다.

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

교착 상태 정보 도구

교착 상태 정보를 보기 위해 SQL Server 데이터베이스 엔진 xEvent 세션, 두 추적 플래그 및 SQL Profiler의 system_health 교착 상태 그래프 이벤트 형식의 모니터링 도구를 제공합니다.

참고 항목

이 섹션에는 확장 이벤트, 추적 플래그 및 추적에 대한 정보가 포함되어 있지만 교착 상태 확장 이벤트는 교착 상태 정보를 캡처하는 데 권장되는 방법입니다.

교착 상태 확장 이벤트

SQL Server 2012(11.x) xml_deadlock_report 부터 SQL Trace 또는 SQL Profiler의 Deadlock Graph 이벤트 클래스 대신 확장 이벤트(xEvent)를 사용해야 합니다.

또한 SQL Server 2012(11.x)부터 교착 상태가 발생하면 system_health 세션은 교착 상태 그래프를 포함하는 모든 xml_deadlock_report xEvent를 이미 캡처합니다. system_health 세션은 기본적으로 사용하도록 설정되어 있으므로 교착 상태 정보를 캡처하도록 별도의 xEvent 세션을 구성할 필요는 없습니다. xml_deadlock_report xEvent를 사용하여 교착 상태 정보를 캡처하는 추가 작업은 필요하지 않습니다.

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

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

세션 파일 또는 링 버퍼 xml_deadlock_report 를 system_health 열고 xEvent가 기록되면 Management Studio는 다음 예제와 같이 교착 상태에 관련된 작업 및 리소스를 그래픽으로 묘사합니다.

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

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
    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 [Date] DESC;

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

다음 예제에서는 위의 결과의 첫 번째 링크를 선택한 후 출력을 보여줍니다.

<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>

자세한 내용은 system_health 세션 사용을 참조 하세요.

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

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

Important

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

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

속성	추적 플래그 1204 및 추적 플래그 1222	추적 플래그 1204만	추적 플래그 1222만
출력 형식	출력은 SQL Server 오류 로그에 캡처됩니다.	교착 상태에 관련된 노드에 초점을 맞췄습니다. 각 노드에는 전용 섹션이 있으며, 마지막 섹션에서는 교착 상태 피해자에 대해 설명합니다.	XSD(XML 스키마 정의) 스키마를 준수하지 않는 XML과 유사한 형식의 정보를 반환합니다. 형식에는 세 가지 주요 섹션이 있습니다. 첫 번째 섹션에서는 교착 상태 피해자를 선언합니다. 두 번째 섹션에서는 교착 상태와 관련된 각 프로세스에 대해 설명합니다. 세 번째 섹션에서는 추적 플래그 1204의 노드와 동의어인 리소스에 대해 설명합니다.
특성 식별	SPID:<x> ECID:<x>. 병렬 프로세스의 경우 시스템 프로세스 ID 스레드를 식별합니다. x>가 SPID 값으로 대체되는 항목`SPID:<x> ECID:0`<은 기본 스레드를 나타냅니다. x>가 SPID 값으로 대체되고 <y>가 0보다 큰 항목`SPID:<x> ECID:<y>`<은 동일한 SPID에 대한 하위 스레드를 나타냅니다. BatchID (추적 플래그 1222의 경우 사용 ). 코드 실행이 잠금을 요청하거나 보유하는 일괄 처리를 식별합니다. MARS(다중 활성 결과 집합)를 사용하지 않도록 설정하면 BatchID 값은 0입니다. MARS를 사용하도록 설정하면 활성 일괄 처리의 값은 1에서 n입니다. 세션에 활성 일괄 처리가 없으면 BatchID는 0입니다. 모드. 스레드에서 요청, 부여 또는 대기한 특정 리소스에 대한 잠금 유형을 지정합니다. Mode는 IS(내재된 공유), S(공유), U(업데이트), IX(의도 배타), SIX(의도 배타 공유) 및 X(배타)가 될 수 있습니다. 줄 # (추적 플래그 1222의 줄 ). 교착 상태가 발생했을 때 실행 중인 문의 현재 일괄 처리에 있는 줄 번호를 나열합니다. Input Buf (추적 플래그 1222에 대한 inputbuf ). 현재 일괄 처리의 모든 문을 나열합니다.	노드입니다. 교착 상태 체인의 항목 번호를 나타냅니다. 목록입니다. 잠금 소유자는 다음 목록에 속할 수 있습니다. 허용 목록. 리소스의 현재 소유자를 열거합니다. Convert List. 잠금을 더 높은 수준으로 변환하려는 현재 소유자를 열거합니다. 대기 목록입니다. 리소스에 대한 현재 새 잠금 요청을 열거합니다. 문 유형입니다. 스레드에 사용 권한이 있는 DML 문(SELECT, INSERT, UPDATE 또는 DELETE)의 형식에 대해 설명합니다. 피해자 리소스 소유자입니다. SQL Server가 교착 상태 주기를 중단하기 위해 희생자로 선택하는 참여 스레드를 지정합니다. 선택된 스레드와 기존의 모든 하위 스레드가 종료됩니다. 다음 분기입니다. 교착 상태 순환과 관련된 동일한 SPID의 하위 스레드 두 개 이상을 나타냅니다.	교착 상태 피해자입니다. 교착 상태의 희생자로 선택된 작업의 실제 메모리 주소( sys.dm_os_tasks 참조(Transact-SQL) 참조)를 나타냅니다. 해결되지 않은 교착 상태의 경우 0일 수 있습니다. 롤백하고 있는 태스크를 처리하지 않도록 선택할 수는 없습니다. executionstack. 교착 상태가 발생할 때 실행 중인 Transact-SQL 코드를 나타냅니다. priority. 교착 상태 우선 순위를 나타냅니다. 경우에 따라 SQL Server 데이터베이스 엔진 동시성을 높이기 위해 짧은 기간 동안 교착 상태 우선 순위를 변경하도록 선택할 수 있습니다. logused. 작업에서 사용하는 로그 공간입니다. 소유자 ID입니다. 요청을 제어할 수 있는 트랜잭션의 ID입니다. 상태. 작업의 상태입니다. 다음 값 중 하나입니다. >>보류 중입니다. 작업자 스레드를 기다리고 있습니다. >>runnable입니다. 실행 준비가 완료되었지만 퀀텀 대기 중입니다. >>실행 중입니다. 현재 스케줄러에서 실행 중입니다. >>일시 중단되었습니다. 실행이 일시 중단됩니다. >>완료했습니다. 작업이 완료되었습니다. >>spinloop. spinlock이 사용 가능할 때까지 대기합니다. waitresource. 태스크에 필요한 리소스입니다. 대기 시간입니다. 리소스를 기다리는 시간(밀리초)입니다. schedulerid. 이 작업과 연결된 스케줄러입니다. sys.dm_os_schedulers(Transact-SQL)를 참조하세요. hostname. 워크스테이션의 이름입니다. isolationlevel입니다. 현재 트랜잭션 격리 수준입니다. Xactid. 요청을 제어하는 트랜잭션의 ID입니다. currentdb. 데이터베이스의 ID입니다. lastbatchstarted. 클라이언트 프로세스가 일괄 처리를 마지막으로 시작한 시간입니다. lastbatchcompleted. 클라이언트 프로세스가 일괄 처리를 마지막으로 완료한 시간입니다. clientoption1 및 clientoption2. 이 클라이언트 연결에 대한 옵션을 설정합니다. SET NOCOUNT 및 SET XACTABORT와 같은 SET 문에서 일반적으로 제어하는 옵션에 대한 정보를 포함하는 비트 마스크입니다. associatedObjectId. HoBT(힙 또는 B-트리) ID를 나타냅니다.
리소스 특성	RID. 잠금이 유지되거나 요청되는 테이블 내의 단일 행을 식별합니다. RID는 RID `db_id:file_id:page_no:row_no`로 표시됩니다. 예: `RID: 6:1:20789:0`. 개체입니다. 잠금이 유지되거나 요청되는 테이블을 식별합니다. OBJECT는 OBJECT `db_id:object_id`로 표시됩니다. 예: `TAB: 6:2009058193`. KEY. 잠금이 유지되거나 요청되는 인덱스 내의 키 범위를 식별합니다. KEY는 KEY `db_id:hobt_id` 로 표시됩니다. (인덱스 키 해시 값). 예: `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 `lock_resource`로 표시됩니다. 예: `APP: Formf370f478`. 메타데이터. 교착 상태에 관련된 메타데이터 리소스를 나타냅니다. 메타데이터에는 많은 하위 리소스가 있으므로 반환되는 값은 교착 상태가 있는 하위 리소스에 따라 달라집니다. 예를 들어 `METADATA.USER_TYPE`는 `user_type_id = integer_value`를 반환합니다. 메타데이터 리소스 및 하위 리소스에 대한 자세한 내용은 sys.dm_tran_locks(Transact-SQL)를 참조하세요. 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의 출력을 보여 줍니다.

Important

SQL Profiler는 2016년에 사용되지 않으며 확장 이벤트로 대체된 추적을 만듭니다. 확장 이벤트는 성능 오버헤드가 훨씬 적고 추적보다 훨씬 더 구성 가능합니다. 추적 대신 확장 이벤트 교착 상태 이벤트를 사용하는 것이 좋습니다.

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

확장 이벤트의 SQL 추적 이벤트 클래스에 해당하는 항목이 있습니다. SQL 추적 이벤트 클래스에 해당하는 확장 이벤트를 참조 하세요. 확장 이벤트는 SQL 추적을 통해 권장됩니다.

교착 상태 처리

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

Your transaction (process ID #52) 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를 트래핑하는 오류 처리기를 구현하면 애플리케이션에서 교착 상황을 처리하고 자동으로 교착 상태와 관련된 쿼리를 다시 전송하는 등의 동작을 취할 수 있습니다. 쿼리를 자동으로 다시 제출하면 사용자는 교착 상태가 발생했음을 알 필요가 없습니다.

쿼리를 다시 전송하기 전에 애플리케이션이 일시 중지되어야 합니다. 이때 교착 상태와 관련된 다른 트랜잭션이 완료되어 교착 상태의 원인이 되는 해당 잠금을 해제할 수 있습니다. 이를 통해 다시 전송하는 쿼리에서 잠금을 요청할 때 교착 상태가 다시 발생할 가능성을 최소화할 수 있습니다.

교착 상태 최소화

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

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

교착 상태를 최소화하려면 다음을 수행합니다.

같은 순서로 개체에 액세스합니다.
트랜잭션에서 사용자 상호 작용을 피합니다. - 트랜잭션을 하나의 일괄 처리로 짧게 유지합니다.
낮은 격리 수준을 사용합니다.
행 버전 관리 기반의 격리 수준을 사용합니다.
- READ_COMMITTED_SNAPSHOT 데이터베이스 옵션을 ON으로 설정하여 커밋된 읽기 트랜잭션이 행 버전 관리를 사용할 수 있도록 합니다.
- 스냅샷 격리를 사용합니다.
바인딩된 연결을 사용합니다..

같은 순서로 개체에 액세스

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

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

사용자 개입 없이 실행되는 일괄 처리의 속도는 애플리케이션의 매개 변수 요청 프롬프트에 대한 응답 등 사용자가 수동으로 쿼리에 응답해야 하는 경우의 속도에 비해 매우 빠르므로 사용자 상호 작용을 포함하는 트랜잭션은 작성하지 않는 것이 좋습니다. 예를 들어 트랜잭션이 사용자 입력을 기다리고 있고 사용자가 주말 동안 점심이나 집으로 가는 경우 사용자는 트랜잭션이 완료되지 않도록 지연합니다. 이 경우 트랜잭션에서 보유한 잠금은 트랜잭션이 커밋 또는 롤백되어야 해제되므로 시스템 처리량이 줄어듭니다. 교착 상태가 발생하지 않더라도 동일한 리소스에 액세스하는 다른 트랜잭션은 트랜잭션이 완료되기를 기다리는 동안 차단됩니다.

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

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

트랜잭션을 하나의 일괄 처리로 유지하면 트랜잭션 중 네트워크 왕복이 최소화되므로 트랜잭션을 완료하고 잠금을 해제하는 데 걸리는 지연 시간을 줄일 수 있습니다.

업데이트 잠금에 대한 자세한 내용은 트랜잭션 잠금 및 행 버전 관리 가이드를 참조 하세요.

낮은 격리 수준 사용

트랜잭션을 더 낮은 격리 수준에서 실행할 수 있는지 여부를 확인합니다. 커밋된 읽기를 구현하면 트랜잭션이 첫 번째 트랜잭션이 완료될 때까지 기다리지 않고 다른 트랜잭션에서 이전에 읽은 데이터를 읽을 수 있습니다(수정되지 않음). 커밋된 읽기 등 낮은 격리 수준을 사용하면 순차 가능 등의 높은 격리 수준보다 짧은 기간 동안 공유 잠금을 보유합니다. 이렇게 하면 잠금 경합이 줄어듭니다.

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

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

참고 항목

일부 애플리케이션은 커밋된 읽기 격리의 잠금과 차단에 의존합니다. 이러한 애플리케이션의 경우 이 옵션을 사용하도록 설정하려면 몇 가지 변경이 필요합니다.

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

이러한 격리 수준을 구현하여 읽기 및 쓰기 작업 간에 발생할 수 있는 교착 상태를 최소화합니다.

바운드 연결 사용

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

트랜잭션 중지

교착 상태 시나리오에서는 피해자 트랜잭션이 자동으로 중지되고 롤백됩니다. 교착 상태 시나리오에서 트랜잭션을 중지할 필요가 없습니다.

교착 상태 발생

참고 항목

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

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

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

BEGIN TRAN

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

이제 세션 B에서 다음 Transact-SQL을 실행합니다. 이 코드는 트랜잭션을 명시적으로 시작하지 않습니다. 대신 자동 커밋 트랜잭션 모드에서 작동합니다. 이 문은 SalesLT.ProductDescription 테이블을 업데이트합니다. 업데이트는 SalesLT.ProductDescription 테이블의 72개 행에 대한 업데이트(U) 잠금을 해제합니다. 쿼리는 SalesLT.Product 테이블을 포함하여 다른 테이블에 조인됩니다.

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

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

세션 A로 돌아갑니다. 다음 Transact-SQL 문을 실행합니다. 이는 열린 트랜잭션의 일부로 두 번째 UPDATE 문을 실행합니다.

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

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

세션 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에서 기본적으로 활성화되고 활성화된 확장 이벤트 세션의 system_health ring_buffer 대상에서 교착 상태에 대한 세부 정보를 볼 수 있습니다. 다음과 같은 쿼리를 고려해 보세요.

WITH cteDeadLocks ([Deadlock_XML]) AS (
  SELECT [Deadlock_XML] = CAST(target_data AS 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 
  Deadlock_XML = x.Graph.query('(event/data/value/deadlock)[1]')  
, when_occurred = x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') 
, DB = DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) --Current database of the first listed process 
FROM (
 SELECT Graph.query('.') AS Graph 
 FROM cteDeadLocks c
 CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)
) AS x
ORDER BY when_occurred desc;

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

최적화된 잠금 및 교착 상태

적용 대상: Azure SQL Database

최적화된 잠금 은 배타적 TID 잠금과 관련된 교착 상태를 보고하는 방법을 변경하는 다른 잠금 메커니즘을 도입했습니다. 교착 상태 보고서의 각 리소스에서 각 <xactlock> 요소는 교착 상태의 <resource-list>각 멤버의 잠금에 대한 기본 리소스 및 특정 정보를 보고합니다.

최적화된 잠금이 사용되는 다음 예제를 고려합니다.

CREATE TABLE t2 
(a int PRIMARY KEY not null 
,b int null); 

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

두 세션의 다음 TSQL 명령은 테이블에 t2교착 상태를 만듭니다.

세션 1에서:

--session 1
BEGIN TRAN foo;
UPDATE t2 SET b = b+ 10 WHERE a = 1;

세션 2:

--session 2:
BEGIN TRAN bar 
UPDATE t2 SET b = b+ 10 WHERE a = 2;

세션 1에서:

--session 1:
UPDATE t2 SET b = b + 100 WHERE a = 2;

세션 2:

--session 2:
UPDATE t2 SET b = b + 20 WHERE a = 1;

이 경쟁 UPDATE 문 시나리오에서는 교착 상태가 발생합니다. 이 경우 각 세션이 자체 TID에 X 잠금을 유지하고 다른 TID의 S 잠금을 대기하는 keylock 리소스로 인해 교착 상태가 발생합니다. 교착 상태 보고서로 캡처된 다음 XML에는 최적화된 잠금과 관련된 요소 및 특성이 포함되어 있습니다.

교착 상태 가이드

교착 상태 이해

교착 상태 감지 및 종료

교착 상태에 빠질 수 있는 리소스

교착 상태 검색

교착 상태 정보 도구

교착 상태 확장 이벤트

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

추적 플래그 1204 예제

추적 플래그 1222 예제

Profiler 교착 상태 그래프 이벤트

교착 상태 처리

교착 상태 최소화

같은 순서로 개체에 액세스

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

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

낮은 격리 수준 사용

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

바운드 연결 사용

트랜잭션 중지

교착 상태 발생

최적화된 잠금 및 교착 상태

관련 콘텐츠

피드백

피드백

추가 리소스