교착 상태 가이드
적용 대상:
Microsoft Fabric의 SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW) SQL 데이터베이스
이 문서에서는 SQL Server 데이터베이스 엔진 교착 상태에 대해 자세히 설명합니다. 교착 상태는 종종 다단계 트랜잭션에서 데이터베이스의 경쟁적인 동시 잠금으로 인해 발생합니다. 트랜잭션 잠금에 대한 자세한 내용은 트랜잭션 잠금 및 행 버전 관리 가이드를 참조하세요.
Azure SQL 데이터베이스의 교착 상태 식별 및 방지에 대한 더 구체적인 내용은 Azure SQL 데이터베이스의 교착 상태 분석 및 방지를 참조하세요.
교착 상태 이해
한 태스크에서 잠근 리소스를 다른 태스크에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하면 교착 상태가 발생합니다. 예시:
트랜잭션 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은 Part 테이블 잠금 리소스에 대한 트랜잭션 T2에 종속성이 있습니다. 마찬가지로 트랜잭션 T2는 Supplier 테이블 잠금 리소스에 대한 트랜잭션 T1에 종속성이 있습니다. 이러한 종속성은 주기를 형성하므로 트랜잭션 T1과 T2 사이에 교착 상태가 발생합니다.
테이블이 분할되고 ALTER TABLE의 LOCK_ESCALATION 설정이 AUTO으로 설정된 경우에도 교착 상태가 발생할 수 있습니다. LOCK_ESCALATION이(가) AUTO로 설정되면 SQL Server 데이터베이스 엔진에서 TABLE 수준이 아니라 HoBT 수준에서 테이블 파티션을 잠그도록 허용하여 동시성이 증가합니다. 그러나 개별 트랜잭션이 테이블에 파티션 잠금을 보유하고 다른 트랜잭션 파티션에서 잠금을 원하면 교착 상태가 발생합니다. 이 교착 상태 유형은 LOCK_ESCALATION을 TABLE로 설정하여 방지할 수 있습니다. 그러나 이 설정은 파티션에 대한 대규모 업데이트가 테이블 잠금을 기다리도록 강제하여 동시성을 줄입니다.
교착 상태 검색 및 종료
한 태스크에서 잠근 리소스를 다른 태스크에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하면 교착 상태가 발생합니다. 다음 그래프는 교착 상태의 상위 수준 뷰를 표시합니다.
태스크 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(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에서 실행되는 저장 프로시저가 세션 뮤텍스를 획득합니다. 저장 프로시저를 실행하는 데 시간이 오래 걸리는 경우 저장 프로시저가 사용자의 입력을 기다리고 있다고 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 데이터베이스 엔진은 system_health XEvent 세션, 두 개의 추적 플래그 및 교착 상태 그래프 이벤트 형식으로 SQL 프로파일러에 모니터링 도구를 제공합니다.
참고 항목
이 섹션에는 확장 이벤트, 추적 플래그 및 추적에 대한 정보가 포함되어 있지만 Deadlock 확장 이벤트는 교착 상태 정보를 캡처하는 데 권장되는 방법입니다.
교착 상태 확장 이벤트
SQL Server 2012(11.x) 이상 버전에서 SQL 추적 또는 SQL 프로파일러의 Deadlock Graph 이벤트 클래스나 SQL 프로파일러 대신 xml_deadlock_report XEvent(확장 이벤트)를 사용해야 합니다.
교착 상태가 발생할 때 system_health 세션이 이미 교착 상태 그래프가 포함된 xml_deadlock_report XEvent를 모두 캡처합니다. system_health 세션은 기본적으로 사용하도록 설정되어 있으므로 교착 상태 정보를 캡처하도록 별도의 XEvent 세션을 구성할 필요는 없습니다. xml_deadlock_report XEvent를 사용하여 교착 상태 정보를 캡처하는 추가 작업은 필요하지 않습니다.
교착 상태 그래프에는 일반적으로 세 개의 서로 다른 노드가 있습니다.
victim-list. 교착 상태의 피해자 프로세스 식별자process-list. 교착 상태에 관련된 모든 프로세스에 대한 정보입니다.resource-list. 교착 상태에 관련된 리소스에 대한 정보입니다.
system_health 세션 파일 또는 링 버퍼를 열고 xml_deadlock_report 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_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>
자세한 내용은 system_health 세션을 사용하는 방법을 참조하세요.
추적 플래그 1204 및 추적 플래그 1222
교착 상태가 발생하면 추적 플래그 1204 및 추적 플래그 1222는 SQL Server 오류 로그에 캡처된 정보를 반환합니다. 추적 플래그 1204는 교착 상태에 관련된 각 노드별로 형식이 지정된 SQL 오류 로그에 교착 상태 정보를 보고합니다. 추적 플래그 1222는 우선 프로세스별로 SQL 오류의 교착 상태 정보 형식을 지정한 후 리소스별로 지정합니다. 두 추적 플래그를 모두 사용하여 동일한 교착 상태 이벤트의 두 표현을 가져올 수 있습니다.
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은(는) 주 스레드를 나타냅니다. SPID:<x> ECID:<y> 항목은 동일한 SPID의 하위 스레드를 나타냅니다. 여기서 <x>는 SPID 값으로 대체되고 <y>는 0보다 큽니다.BatchID (추적 플래그 1222에 대한 sbid). 코드 실행이 잠금을 요청하거나 보유하는지 배치를 식별합니다. MARS(다중 활성 결과 집합)를 사용하지 않도록 설정하면 BatchID 값은 0입니다. MARS를 사용하도록 설정하면 활성 일괄 처리의 값은 1에서 n입니다. 세션에 활성 배치가 없으면 BatchID는 0입니다.Mode 스레드에서 요청, 부여 또는 대기한 특정 리소스에 대한 잠금 유형을 지정합니다. Mode는 IS(내재된 공유), S(공유), U(업데이트), IX(의도 배타), SIX(의도 배타 공유) 및 X(배타)가 될 수 있습니다.Line # (추적 플래그 1222에 대한 line). 교착 상태가 발생했을 때 실행 중인 문의 현재 배치에 있는 줄 번호를 나열합니다.Input Buf (추적 플래그 1222에 대한 inputbuf). 현재 배치에 있는 문의 수입니다. |
Node 교착 상태 체인의 항목 번호를 나타냅니다.Lists 잠금 소유자는 다음 목록에 속할 수 있습니다.Grant List 리소스의 현재 소유자를 열거합니다.Convert List 잠금을 더 높은 수준으로 변환하려는 현재 소유자를 열거합니다.Wait List 리소스에 대한 현재 새 잠금 요청을 열거합니다.Statement Type 스레드에 사용 권한이 있는 DML 문(SELECT, INSERT, UPDATE 또는 DELETE)의 형식을 설명합니다.Victim Resource Owner SQL Server에서 교착 상태 순환을 끊도록 선택되는 참여 스레드를 지정합니다. 선택된 스레드와 기존의 모든 하위 스레드가 종료됩니다.Next Branch 교착 상태 순환과 관련된 동일한 SPID의 하위 스레드 두 개 이상을 나타냅니다. |
deadlock victim 교착 상태의 희생자로 선택된 작업의 실제 메모리 주소(sys.dm_os_tasks 참조)를 나타냅니다. 해결되지 않은 교착 상태의 경우 0일 수 있습니다. 롤백하고 있는 태스크를 처리하지 않도록 선택할 수는 없습니다.executionstack 교착 상태가 발생할 때 실행 중인 Transact-SQL 코드를 나타냅니다.priority 교착 상태 우선 순위를 나타냅니다. 경우에 따라 SQL Server 데이터베이스 엔진 동시성을 높이기 위해 짧은 기간 동안 교착 상태 우선 순위를 변경하도록 선택할 수 있습니다.logused 작업에서 사용하는 로그 공간입니다.owner id 요청을 제어하는 트랜잭션의 ID입니다.status 태스크의 상태입니다. 다음 값 중 하나를 사용합니다.- pending 작업자 스레드를 기다리고 있습니다.- runnable 실행 준비가 완료되었지만 퀀텀 대기 중입니다.- running SPID는 현재 스케줄러에서 실행 중입니다.- suspended 실행이 일시 중단됩니다.- done 태스크가 완료되었습니다.- spinloop spinlock이 사용 가능할 때까지 대기합니다.waitresource 태스크에 필요한 리소스입니다.waittime 리소스를 기다리는 시간(밀리초)입니다.schedulerid 이 작업과 연결된 스케줄러입니다. sys.dm_os_schedulers를 참조합니다.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은 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_idDB: 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의 출력을 보여 줍니다.
Important
SQL Profiler는 2016년 이후로 사용되지 않으며 확장 이벤트로 대체된 추적을 만듭니다. 확장 이벤트는 성능 오버헤드가 훨씬 적고 추적보다 훨씬 더 구성 가능합니다. 추적 대신 확장 이벤트 교착 상태 이벤트를 사용하는 것을 고려해 보세요.
교착 상태 이벤트에 대한 자세한 내용은 Lock:Deadlock 이벤트 클래스를 참조하세요. SQL Profiler 교착 상태 그래프를 실행하는 방법에 대한 자세한 내용은 교착 상태 그래프 저장(SQL Server Profiler)을 참조하세요.
확장 이벤트의 SQL 추적 이벤트 클래스에 해당하는 항목이 있습니다. SQL 추적 이벤트 클래스에 해당하는 확장 이벤트 보기를 참조하세요. 확장 이벤트는 SQL 추적을 통해 권장됩니다.
교착 상태 처리
SQL Server Database 엔진 인스턴스에서 트랜잭션을 교착 상태 희생자로 선택하면 현재 배치가 종료되고 트랜잭션이 롤백된 다음, 애플리케이션에 오류 메시지 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를 트래핑하는 오류 처리기를 구현하면 애플리케이션에서 교착 상황을 처리하고 자동으로 교착 상태와 관련된 쿼리를 다시 전송하는 등의 동작을 취할 수 있습니다. 쿼리를 자동으로 다시 제출하므로 사용자는 교착 상태 발생을 알 필요가 없습니다.
쿼리를 다시 전송하기 전에 애플리케이션이 일시 중지되어야 합니다. 이때 교착 상태와 관련된 다른 트랜잭션이 완료되어 교착 상태의 원인이 되는 해당 잠금을 해제할 수 있습니다. 이를 통해 다시 전송하는 쿼리에서 잠금을 요청할 때 교착 상태가 다시 발생할 가능성을 최소화할 수 있습니다.
TRY...CATCH를 사용하여 처리
교착 상태를 처리하기 위해 TRY...CATCH를 사용할 수 있습니다. 1205 교착 상태 희생자 오류는 CATCH 블록에 의해 catch될 수 있으며 스레드가 잠금 해제될 때까지 트랜잭션을 롤백할 수 있습니다.
자세한 내용은 교착 상태 처리를 참조하세요.
교착 상태 최소화
교착 상태를 완전히 방지할 수는 없지만 특정 코딩 규칙을 따르면 교착 상태 발생을 최소화할 수 있습니다. 교착 상태를 최소화하면 트랜잭션 처리량이 늘어나고 더 적은 수의 트랜잭션이 다음과 같이 되므로 시스템 오버헤드가 줄어듭니다.
- 롤백되어 트랜잭션에 의해 수행된 모든 작업이 실행 취소됩니다.
- 교착 상태 발생 시 롤백되었으므로 애플리케이션에 의해 다시 전송됩니다.
교착 상태를 최소화하기
- 같은 순서로 개체에 액세스합니다.
- 트랜잭션에서 사용자 상호 작용을 피합니다.
- 트랜잭션을 하나의 일괄 처리로 짧게 유지합니다.
- 낮은 격리 수준을 사용합니다.
- 행 버전 관리 기반의 격리 수준을 사용합니다.
READ_COMMITTED_SNAPSHOT데이터베이스 옵션을 ON으로 설정하여 커밋된 읽기 트랜잭션이 행 버전 관리를 사용할 수 있도록 합니다.- 스냅샷 격리를 사용합니다.
- 바인딩된 연결을 사용합니다..
같은 순서로 개체에 액세스
모든 동시 트랜잭션이 동일한 순서로 개체에 액세스하는 경우 교착 상태가 발생할 가능성이 적습니다. 예를 들어 두 개의 동시 트랜잭션이 Supplier 테이블에 대한 잠금을 가져온 다음 Part 테이블에서 다른 트랜잭션이 완료될 때까지 한 트랜잭션이 Supplier 테이블에서 차단 됩니다. 첫 번째 트랜잭션이 커밋되거나 롤백된 후 두 번째가 계속되므로 교착 상태는 발생하지 않습니다. 모든 데이터 수정에 저장 프로시저를 사용하면 개체 액세스 순서를 표준화할 수 있습니다.
트랜잭션에서 사용자 상호 작용 피하기
사용자 개입 없이 실행되는 일괄 처리의 속도는 애플리케이션의 매개 변수 요청 프롬프트에 대한 응답 등 사용자가 수동으로 쿼리에 응답해야 하는 경우의 속도에 비해 매우 빠르므로 사용자 상호 작용을 포함하는 트랜잭션은 작성하지 않는 것이 좋습니다. 예를 들어 트랜잭션이 사용자 입력을 기다리고 있고 사용자가 주말 동안 점심이나 집으로 가는 경우 사용자는 트랜잭션이 완료되지 않도록 지연합니다. 이 경우 트랜잭션에서 보유한 잠금은 트랜잭션이 커밋 또는 롤백되어야 해제되므로 시스템 처리량이 줄어듭니다. 교착 상태 상황이 발생하지 않아도 같은 리소스에 액세스하는 다른 트랜잭션은 해당 트랜잭션을 완료할 때까지 차단됩니다.
트랜잭션을 하나의 일괄 처리로 짧게 유지
교착 상태는 보통 오래 실행되는 여러 개의 트랜잭션이 같은 데이터베이스에서 동시에 실행될 때 발생합니다. 트랜잭션 실행 시간이 길어질수록 배타적 또는 업데이트 잠금 보유 시간이 길어지므로 다른 작업이 차단되고 교착 상태 상황이 발생할 수 있습니다.
트랜잭션을 하나의 일괄 처리로 유지하면 트랜잭션 중 네트워크 왕복이 최소화되므로 트랜잭션을 완료하고 잠금을 해제하는 데 걸리는 지연 시간을 줄일 수 있습니다.
업데이트 잠금에 대한 자세한 내용은 트랜잭션 잠금 및 행 버전 관리 가이드를 참조하세요.
낮은 격리 수준 사용
잠금 경쟁이 줄어들도록 트랜잭션을 더 낮은 격리 수준에서 실행할 수 있는지 확인합니다. 트랜잭션에서는 처음 트랜잭션이 완료될 때까지 기다리지 않고 다른 트랜잭션에서 이전에 읽은 수정되지 않은 데이터를 읽을 수 있습니다. 커밋된 읽기 등 낮은 격리 수준은 순차 가능 등의 높은 격리 수준보다 짧은 기간 동안 공유 잠금을 보유합니다. 이렇게 하면 잠금 경합이 줄어듭니다.
행 버전 관리 기반의 격리 수준 사용
READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON으로 설정되면 읽기 커밋된 격리 수준에서 실행되는 트랜잭션은 읽기 작업 중에 공유 잠금 대신 행 버전 관리를 사용합니다.
참고 항목
일부 애플리케이션은 커밋된 읽기 격리의 잠금과 차단에 의존합니다. 이러한 애플리케이션의 경우 이 옵션을 사용하도록 설정하려면 몇 가지 변경이 필요합니다.
스냅샷 격리는 읽기 작업 중에 공유 잠금을 사용하지 않는 행 버전 관리 또한 사용합니다. 스냅샷 격리 상태에서 트랜잭션을 실행하려면 먼저 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정해야 합니다.
이러한 격리 수준을 구현하여 읽기 및 쓰기 작업 간에 발생할 수 있는 교착 상태를 최소화합니다.
바인딩된 연결 사용
바인딩된 연결을 사용하면 동일한 애플리케이션에서 열린 두 개 이상의 연결이 서로 협력할 수 있습니다. 보조 연결에서 얻은 잠금은 기본 연결에서 얻은 것과 같이 유지되며 반대의 경우도 마찬가지입니다. 따라서 서로 차단하지 않습니다.
트랜잭션 중단
교착 상태 시나리오에서는 피해자 트랜잭션이 자동으로 중지되고 롤백됩니다. 교착 상태 시나리오에서 트랜잭션을 중지할 필요가 없습니다.
교착 상태 발생
참고 항목
이 예는 기본 스키마 및 데이터를 사용하여 AdventureWorksLT2019 샘플 데이터베이스에서 작동하며, READ_COMMITTED_SNAPSHOT이(가) 활성화된 경우에만 작동합니다. 이 샘플을 다운로드하려면 AdventureWorks 샘플 데이터베이스를 방문 하세요.
교착 상태를 발생시키려면 두 개의 세션을 AdventureWorksLT2019 데이터베이스에 연결해야 합니다. 이러한 세션을 세션 A 및 세션 B라고 합니다. SSMS(SQL Server Management Studio)에서 두 개의 쿼리 창을 만들어 이 두 세션을 만들 수 있습니다.
세션 A에서 다음 Transact-SQL을 실행합니다. 이 코드는 명시적 트랜잭션을 시작하고 SalesLT.Product 테이블을 업데이트하는 단일 문을 실행합니다. 이를 위해 트랜잭션은 배타적(X) 잠금으로 변환되는 테이블 SalesLT.Product의 한 행에 대해 업데이트(U) 잠금을 획득합니다. 트랜잭션을 열어 둡니다.
BEGIN TRANSACTION;
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
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가 세션 A에 의해 잠긴 행을 포함하여 테이블 SalesLT.Product의 행에 대한 공유(S) 잠금이 필요합니다. 세션 B는 SalesLT.Product에 차단됩니다.
세션 A로 돌아갑니다. 다음 Transact-SQL 문을 실행합니다. 이는 열린 트랜잭션의 일부로 두 번째 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의 세션 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 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파일을 다시 엽니다. 교착 상태 그래프는 다음 이미지와 유사해야 합니다.
최적화된 잠금 및 교착 상태
적용 대상: Azure SQL Database
최적화된 잠금 은 배타적 TID 잠금과 관련된 교착 상태를 보고하는 방법을 변경하는 다른 잠금 메커니즘을 도입했습니다. 교착 상태 보고서 <resource-list>의 각 리소스에서 각 <xactlock> 요소는 교착 상태의 각 멤버의 잠금에 대한 기본 리소스 및 특정 정보를 보고합니다.
최적화된 잠금이 사용되는 다음 예제를 고려해 보세요.
CREATE TABLE t2
(
a INT PRIMARY KEY NOT NULL,
b INT NULL
);
INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);
GO
두 세션의 다음 Transact-SQL 명령은 t2 테이블에 교착 상태를 만듭니다.
세션 1에서:
--session 1
BEGIN TRANSACTION foo;
UPDATE t2
SET b = b + 10
WHERE a = 1;
세션 2에서:
--session 2:
BEGIN TRANSACTION 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 잠금을 대기하는 키 잠금 리소스로 인해 교착 상태가 발생합니다. 교착 상태 보고서로 캡처된 다음 XML에는 최적화된 잠금과 관련된 요소 및 특성이 포함되어 있습니다.
