다음을 통해 공유

Azure SQL Database 및 Fabric SQL 데이터베이스에서 교착 상태 분석 및 방지

  • 아티클

적용 대상: Fabric의 Azure SQL Database SQL 데이터베이스

이 문서에서는 교착 상태를 식별하고, 교착 상태 그래프 및 쿼리 저장소 사용하여 교착 상태의 쿼리를 식별하고, 교착 상태가 재발하지 않도록 변경 내용을 계획하고 테스트하는 방법을 설명합니다. 이 문서는 Azure SQL Database의 많은 기능을 공유하는 Azure SQL Database 및 패브릭 SQL 데이터베이스에 적용됩니다.

이 문서에서는 잠금 경합으로 인한 교착 상태를 식별하고 분석하는 데 중점을 둡니다. 교착 상태에 빠질 수 있는 리소스에서 다른 형식의 교착 상태에 대해 자세히 알아봅니다.

교착 상태 발생 방법

Azure SQL 데이터베이스의 각 새 데이터베이스에는 기본적으로 RCSI(읽기 커밋된 스냅샷) 데이터베이스 설정이 사용하도록 설정되어 있습니다. 데이터 읽기 세션과 데이터 쓰기 세션 간의 차단은 RCSI에서 최소화되며, 행 버전 관리로 동시성을 높입니다. 그러나 다음과 같은 이유로 Azure SQL 데이터베이스 데이터베이스에서 차단 및 교착 상태가 발생할 수 있습니다.

  • 데이터를 수정하는 쿼리가 서로 차단할 수 있습니다.
  • 차단을 증가시키는 격리 수준에서 쿼리를 실행할 수 있습니다. 격리 수준은 Transact-SQL의 클라이언트 라이브러리 메서드, 쿼리 힌트 또는 SET 문을 통해 지정할 수 있습니다.
  • RCSI를 사용하지 않도록 설정하면 데이터베이스가 공유(S) 잠금을 사용하여 읽기 커밋된 격리 수준에서 실행되는 SELECT 문을 보호할 수 있습니다. 이렇게 하면 차단 및 교착 상태가 증가할 수 있습니다.

교착 상태의 예

교착 상태는 각 작업에 다른 작업이 잠그려고 하는 리소스에 대한 잠금이 있기 때문에 둘 이상의 작업이 서로를 영구적으로 차단할 때 발생합니다. 교착 상태는 순환 종속성이라고도 합니다. 두 작업 교착 상태의 경우 트랜잭션 A는 트랜잭션 B에 대한 종속성을 가지며 트랜잭션 B는 트랜잭션 A에 대한 종속성을 가짐으로써 원을 닫습니다.

예시:

  1. 세션 A는 명시적 트랜잭션을 시작하고 배타적(X) 잠금으로 변환되는 테이블 SalesLT.Product의 한 행에서 업데이트(U) 잠금을 획득하는 업데이트 문을 실행합니다.
  2. 세션 B는 SalesLT.ProductDescription 테이블을 수정하는 업데이트 문을 실행합니다. 업데이트 문은 업데이트할 올바른 행을 찾기 위해 SalesLT.Product 테이블에 조인됩니다.
    • 세션 B는 SalesLT.ProductDescription 테이블의 72개 행에서 업데이트(U) 잠금을 획득합니다.
    • 세션 B는 세션 A에 의해 잠긴 행을 포함하여 테이블 SalesLT.Product의 행에 대한 공유 잠금이 필요합니다. 세션 B는 SalesLT.Product에서 차단됩니다.
  3. 세션 A는 트랜잭션을 계속하고 이제 SalesLT.ProductDescription 테이블에 대해 업데이트를 실행합니다. 세션 A는 SalesLT.ProductDescription의 세션 B에 의해 차단됩니다.

교착 상태에 있는 두 세션을 보여 주는 다이어그램입니다. 각 세션은 다른 프로세스가 계속하기 위해 필요한 리소스를 소유합니다.

교착 상태에 있는 모든 트랜잭션은 예를 들어 세션이 종료되었기 때문에 참여 트랜잭션 중 하나가 롤백되지 않는 한 무기한 대기합니다.

데이터베이스 엔진 교착 상태 모니터는 교착 상태에 있는 작업을 주기적으로 확인합니다. 교착 상태 모니터가 순환 종속성을 검색하면 작업 중 하나를 희생자로 선택하고 오류 1205, "트랜잭션(프로세스 ID N)이 다른 프로세스의 잠금 리소스에서 교착 상태가 되었고 교착 상태 희생자로 선택되었습니다. 트랜잭션을 다시 실행하십시오"와 함께 트랜잭션을 종료합니다. 이러한 방식으로 교착 상태를 해제하면 교착 상태에 있는 다른 작업이 트랜잭션을 완료할 수 있습니다.

참고 항목

이 문서의 교착 상태 프로세스 목록 섹션에서 교착 상태 희생자를 선택하는 기준에 대해 자세히 알아봅니다.

두 세션 간의 교착 상태의 개요입니다. 한 세션이 교착 상태 피해자로 선택되었습니다.

교착 상태 희생자로 선택된 트랜잭션이 있는 애플리케이션은 일반적으로 교착 상태와 관련된 다른 트랜잭션 또는 트랜잭션이 완료된 후에 완료되는 트랜잭션을 다시 시도해야 합니다.

동일한 교착 상태가 다시 발생하지 않도록 다시 시도하기 전에 짧은 임의 지연을 도입하는 것이 가장 좋습니다. 일시적인 오류에 대한 다시 시도 논리를 설계하는 방법에 대해 자세히 알아봅니다.

Azure SQL 데이터베이스의 기본 격리 수준

Azure SQL 데이터베이스의 새 데이터베이스는 기본적으로 RCSI(읽기 커밋된 스냅샷)를 사용하도록 설정합니다. RCSI는 SELECT 문에 공유(S) 잠금을 사용하지 않고 문 수준 일관성을 제공하기 위해 행 버전 관리를 사용하도록 커밋된 격리 수준 읽기의 동작을 변경합니다.

RCSI가 사용하도록 설정된 경우:

  • 데이터를 읽는 문은 데이터를 수정하는 문을 차단하지 않습니다.
  • 데이터를 수정하는 문은 데이터를 읽는 문을 차단하지 않습니다.

스냅샷 격리 수준은 기본적으로 Azure SQL 데이터베이스의 새 데이터베이스에도 사용하도록 설정됩니다. 스냅샷 격리는 데이터에 대한 트랜잭션 수준 일관성을 제공하고 행 버전을 사용하여 업데이트할 행을 선택하는 추가적인 행 기반 격리 수준입니다. 스냅샷 격리를 사용하려면 쿼리 또는 연결에서 트랜잭션 격리 수준을 명시적으로 SNAPSHOT으로 설정해야 합니다. 이 작업은 데이터베이스에 대해 스냅샷 격리를 사용하도록 설정한 경우에만 수행할 수 있습니다.

Transact-SQL RCSI 및/또는 스냅샷 격리를 사용할 수 있는지 확인할 수 있습니다. Azure SQL 데이터베이스에 연결하고 다음 쿼리를 실행합니다.

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

RCSI를 사용하는 경우 is_read_committed_snapshot_on 열이 1 값을 반환합니다. 스냅샷 격리를 사용하는 경우 snapshot_isolation_state_desc 열이 ON 값을 반환합니다.

Azure SQL 데이터베이스의 데이터베이스에 대해 RCSI가 사용하지 않도록 설정된 경우 다시 사용하도록 설정하기 전에 RCSI가 사용하지 않도록 설정된 이유를 조사합니다. 애플리케이션 코드는 데이터를 읽는 쿼리가 데이터를 쓰는 쿼리에 의해 차단되어 RCSI가 사용하도록 설정된 경우 경합 조건으로 인해 잘못된 결과가 발생할 것으로 예상하여 작성되었을 수 있습니다.

교착 상태 이벤트 해석

Azure SQL 데이터베이스의 교착 상태 관리자가 교착 상태를 검색하고 트랜잭션을 희생자로 선택한 후 교착 상태 이벤트가 내보내집니다. 즉, 교착 상태에 대한 경고를 설정하면 개별 교착 상태가 해결된 후 알림이 실행됩니다. 해당 교착 상태에 대해 수행해야 하는 사용자 작업이 없습니다. 다음 오류 1205를 수신한 후 자동으로 계속되도록 애플리케이션은 재시도 논리를 포함하도록 작성되어야 합니다. "트랜잭션(프로세스 ID N)이 다른 프로세스의 잠금 리소스에서 교착 상태가 되었고 교착 상태 희생자로 선택되었습니다. 트랜잭션을 다시 실행합니다.”

그러나 교착 상태가 다시 발생할 수 있으므로 경고를 설정하는 것이 유용합니다. 교착 상태 경고를 사용하면 데이터베이스에서 반복 교착 상태가 발생하는지 조사할 수 있으며, 이 경우 교착 상태가 다시 발생하지 않도록 작업을 취할 수 있습니다. 이 문서의 교착 상태 모니터링 및 경고 섹션에서 경고에 대해 자세히 알아봅니다.

교착 상태를 방지하는 주요 방법

교착 상태가 다시 발생하지 않도록 방지하는 가장 낮은 위험 방식은 일반적으로 교착 상태와 관련된 쿼리를 최적화하기 위해 비클러스터형 인덱스를 조정하는 것입니다.

  • 비클러스터형 인덱스를 튜닝해도 쿼리 코드 자체를 변경할 필요가 없으므로 잘못된 데이터가 사용자에게 반환되는 Transact-SQL 다시 작성할 때 사용자 오류의 위험이 줄어들기 때문에 이 방법은 위험 수준이 낮습니다.
  • 효과적인 비클러스터형 인덱스 튜닝은 쿼리가 읽고 수정할 데이터를 보다 효율적으로 찾는 데 도움이 됩니다. 쿼리가 액세스해야 하는 데이터의 양을 줄임으로써 차단 가능성이 줄어들고 교착 상태가 자주 방지될 수 있습니다.

경우에 따라 클러스터형 인덱스를 만들거나 튜닝하면 차단 및 교착 상태를 줄일 수 있습니다. 클러스터형 인덱스는 모든 비클러스터형 인덱스 정의에 포함되어 있으므로 클러스터형 인덱스를 만들거나 수정하는 작업은 기존 비클러스터형 인덱스가 있는 더 큰 테이블에서 IO 집약적이고 시간 소모적인 작업이 될 수 있습니다. 클러스터형 인덱스 디자인 지침에 대해 자세히 알아봅니다.

인덱스 튜닝이 교착 상태를 방지하는 데 성공하지 못한 경우 다른 방법을 사용할 수 있습니다.

  • 교착 상태와 관련된 쿼리 중 하나에 대해 특정 계획이 선택된 경우에만 교착 상태가 발생하는 경우 쿼리 저장소를 사용하여 쿼리 계획을 강제 실행하면 교착 상태가 다시 발생하지 않을 수 있습니다.
  • 교착 상태와 관련된 하나 이상의 트랜잭션에 대해 Transact-SQL을 다시 작성하면 교착 상태를 방지하는 데 도움이 될 수도 있습니다. 명시적 트랜잭션을 더 작은 트랜잭션으로 분리하려면 동시 수정이 발생할 때 데이터 유효성을 보장하기 위해 신중한 코딩 및 테스트가 필요합니다.

이 문서의 교착 상태가 다시 발생하지 않도록 방지 섹션에서 이러한 각 방법에 대해 자세히 알아봅니다.

교착 상태 모니터링 및 경고

이 문서에서는 AdventureWorksLT 샘플 데이터베이스를 사용하여 교착 상태에 대한 경고를 설정하고, 교착 상태의 예를 발생시키고, 교착 상태의 예에 대한 교착 상태 그래프를 분석하고, 교착 상태가 다시 발생하지 않도록 변경 내용을 테스트합니다.

이 문서에서는 대화형 시각적 모드에서 교착 상태 그래프를 표시하는 기능이 포함된 SSMS(SQL Server Management Studio) 클라이언트를 사용합니다. Azure Data Studio와 같은 다른 클라이언트를 사용하여 예시를 따라갈 수 있지만 교착 상태 그래프는 XML로만 볼 수 있습니다.

AdventureWorksLT 데이터베이스 만들기

예시를 따르려면 Azure SQL 데이터베이스에서 새 데이터베이스를 만들고 샘플 데이터를 데이터 원본으로 선택합니다.

Azure Portal, Azure CLI 또는 PowerShell로 AdventureWorksLT를 만드는 방법에 대한 자세한 지침은 빠른 시작: Azure SQL 데이터베이스 단일 데이터베이스 만들기에서 원하는 방법을 선택합니다.

Azure Portal에서 교착 상태 경고 설정

교착 상태 이벤트에 대한 경고를 설정하려면 Azure Portal을 사용하여 Azure SQL 데이터베이스 및 Azure Synapse Analytics에 대한 경고 만들기 문서의 단계를 따릅니다.

경고의 신호 이름으로 교착 상태를 선택합니다. 이메일/SMS/푸시/음성 작업 형식과 같이 선택한 방법을 사용하여 알림을 받도록 작업 그룹을 구성합니다.

확장 이벤트를 사용하여 Azure SQL 데이터베이스에서 교착 상태 그래프 수집

교착 상태 그래프는 교착 상태와 관련된 프로세스 및 잠금에 관한 풍부한 정보 원본입니다. Azure SQL 데이터베이스에서 확장 이벤트(XEvents)로 교착 상태 그래프를 수집하려면 sqlserver.database_xml_deadlock_report 이벤트를 캡처합니다.

링 버퍼 대상 또는 이벤트 파일 대상을 사용하여 XEvent로 교착 상태 그래프를 수집할 수 있습니다. 적절한 대상 유형을 선택하기 위한 고려 사항은 다음 표에 요약되어 있습니다.

접근법 이점 고려 사항 사용 시나리오
링 버퍼 대상
  • Transact-SQL만 사용하여 간단하게 설정합니다.
  • 데이터베이스를 오프라인으로 전환하거나 데이터베이스 장애 조치(failover)를 취하는 것과 같은 이유로 XEvents 세션이 중지되면 이벤트 데이터가 지워집니다.
  • 데이터베이스 리소스는 링 버퍼의 데이터를 유지 관리하고 세션 데이터를 쿼리하는 데 사용됩니다.
  • 테스트 및 학습을 위해 샘플 추적 데이터를 수집합니다.
  • 이벤트 파일 대상을 즉시 사용하여 세션을 설정할 수 없는 경우 단기 요구를 위해 만듭니다.
  • 추적 데이터를 테이블에 유지하기 위해 자동화된 프로세스를 설정한 경우 추적 데이터에 대한 "랜딩 패드"로 사용합니다.
이벤트 파일 대상
  • 세션이 중지된 후에도 데이터를 사용할 수 있도록 Azure Storage의 Blob에 이벤트 데이터를 유지합니다.
  • 이벤트 파일은 Azure Portal 또는 Azure Storage Explorer에서 다운로드하고 로컬로 분석할 수 있습니다. 세션 데이터를 쿼리하기 위해 데이터베이스 리소스를 사용할 필요가 없습니다.
  • 설정은 더 복잡하며 Azure Storage 컨테이너 및 데이터베이스 범위 자격 증명을 구성해야 합니다.
  • 이벤트 세션이 중지된 후에도 이벤트 데이터를 유지하려는 경우에 일반적으로 사용합니다.
  • 메모리에 유지하려는 것보다 더 많은 양의 이벤트 데이터를 생성하는 추적을 실행하려고 합니다.

사용하려는 대상 유형을 선택합니다.

링 버퍼 대상은 편리하고 쉽게 설정할 수 있지만 용량이 제한되어 있어 이전 이벤트가 손실될 수 있습니다. 링 버퍼는 이벤트를 스토리지에 유지하지 않으며 링 버퍼 대상은 XEvents 세션이 중지될 때 지워집니다. 이는 장애 조치(failover)와 같은 이유로 데이터베이스 엔진이 다시 시작될 때 수집된 XEvent를 사용할 수 없음을 의미합니다. 링 버퍼 대상은 XEvents 세션을 이벤트 파일 대상으로 즉시 설정할 수 없는 경우 학습 및 단기 요구에 가장 적합합니다.

이 샘플 코드는 링 버퍼 대상을 사용하여 메모리에서 교착 상태 그래프를 캡처하는 XEvents 세션을 만듭니다. 링 버퍼 대상에 허용되는 최대 메모리는 4MB이며 장애 조치(failover) 후와 같이 데이터베이스가 온라인 상태가 되면 세션이 자동으로 실행됩니다.

링 버퍼 대상에 쓰는 sqlserver.database_xml_deadlock_report 이벤트에 대한 XEvents 세션을 만든 다음 시작하려면 데이터베이스에 연결하고 다음 Transact-SQL을 실행합니다.

CREATE EVENT SESSION [deadlocks] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer 
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = START;
GO

AdventureWorksLT에서 교착 상태 발생

참고 항목

이 예는 기본 스키마 및 데이터를 사용하여 AdventureWorksLT 데이터베이스에서 작동하며, RCSI가 활성화된 경우에만 작동합니다. 데이터베이스를 만드는 방법은 AdventureWorksLT 데이터베이스 만들기를 참조하세요.

교착 상태를 발생시키려면 두 개의 세션을 AdventureWorksLT 데이터베이스에 연결해야 합니다. 이러한 세션을 세션 A 및 세션 B라고 합니다.

세션 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와 함께 교착 상태가 발생하는 것을 볼 수 있습니다. 다음과 유사한 텍스트와 함께 세션 A에 오류 메시지가 나타납니다.

메시지 1205, 수준 13, 상태 51, 줄 7 트랜잭션(프로세스 ID 91)이 다른 프로세스의 잠금 리소스에서 교착 상태에 빠졌고 교착 상태 희생자로 선택되었습니다. 트랜잭션을 다시 실행합니다.

세션 B가 성공적으로 완료됩니다.

Azure Portal에서 교착 상태 경고를 설정하는 경우 교착 상태가 발생한 직후 알림을 받아야 합니다.

XEvents 세션에서 교착 상태 그래프 보기

교착 상태를 수집하도록 XEvents 세션을 설정했고 세션이 시작된 후 교착 상태가 발생한 경우 교착 상태 그래프의 대화형 그래픽 표시와 교착 상태 그래프의 XML을 볼 수 있습니다.

링 버퍼 대상 및 이벤트 파일 대상에 대한 교착 상태 정보를 가져오기 위해 다른 방법을 사용할 수 있습니다. XEvents 세션에 사용한 대상을 선택합니다.

링 버퍼에 쓰는 XEvents 세션을 설정하면 다음 Transact-SQL로 교착 상태 정보를 쿼리할 수 있습니다. 쿼리를 실행하기 전에 값을 @tracename XEvents 세션의 이름으로 바꿉니다.

DECLARE @tracename sysname = N'deadlocks';

WITH ring_buffer AS (
    SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    JOIN sys.dm_xe_database_session_targets AS t 
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename and
    t.target_name = N'ring_buffer'
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM ring_buffer
    CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

XML로 교착 상태 그래프 보기 및 저장

교착 상태 그래프를 XML 형식으로 보면 교착 상태와 관련된 Transact-SQL 문의 inputbuffer를 복사할 수 있습니다. 또한 교착 상태를 텍스트 기반 형식으로 분석하는 것을 기본 설정할 수도 있습니다.

Transact-SQL 쿼리를 사용하여 교착 상태 그래프 정보를 반환한 경우 교착 상태 그래프 XML을 보려면 아무 행에서나 deadlock_xml 열의 값을 선택하여 SSMS의 새 창에서 교착 상태 그래프의 XML을 엽니다.

이 예시 교착 상태 그래프의 XML은 다음과 같습니다.

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    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'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    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';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

교착 상태 그래프를 XML 파일로 저장하려면:

  1. 파일 및 다른 이름으로 저장...을 선택합니다.
  2. 파일 형식 값을 기본 XML 파일(*.xml)로 그대로 둡니다.
  3. 파일 이름을 원하는 이름으로 설정합니다.
  4. 저장을 선택합니다.

교착 상태 그래프를 SSMS 대화형으로 표시할 수 있는 XDL 파일로 저장

교착 상태 그래프의 대화식 표현을 보는 것은 교착 상태와 관련된 프로세스 및 리소스에 대한 빠른 개요를 얻고 교착 상태 희생자를 신속하게 식별하는 데 유용할 수 있습니다.

교착 상태 그래프를 SSMS에서 그래픽으로 표시할 수 있는 파일로 저장하려면:

  1. SSMS의 새 창에서 교착 상태 그래프의 XML을 열려면 임의의 행에서 deadlock_xml 열의 값을 선택합니다.

  2. 파일 및 다른 이름으로 저장...을 선택합니다.

  3. 파일 형식을 모든 파일로 설정합니다.

  4. 파일 이름을 원하는 이름으로 설정하고 확장자는 .xdl로 설정합니다.

  5. 저장을 선택합니다.

    교착 상태 그래프 XXL 파일을 xsd 확장자가 있는 파일에 저장하는 SSMS의 스크린샷

  6. 창 상단의 탭에서 X를 선택하거나 파일을 선택한 다음 닫기를 선택하여 파일을 닫습니다.

  7. 파일, 열기, 파일을 차례로 선택하여 SSMS에서 파일을 다시 엽니다. .xdl 확장자로 저장한 파일을 선택합니다.

    교착 상태 그래프는 교착 상태와 관련된 프로세스 및 리소스를 시각적으로 표현하여 SSMS에 표시됩니다.

    SSMS에서 열린 xdl 파일의 스크린샷입니다. 교착 상태 그래프는 타원형으로 표시된 프로세스와 잠금 리소스를 사각형으로 표시하는 그래픽으로 표시됩니다.

Azure SQL 데이터베이스에 대한 교착 상태 분석

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

  • Victim-list. 교착 상태의 피해자 프로세스 식별자
  • Process-list. 교착 상태에 관련된 모든 프로세스에 대한 정보입니다. 교착 상태 그래프는 '프로세스'라는 용어를 사용하여 트랜잭션을 실행하는 세션을 나타냅니다.
  • Resource-list. 교착 상태에 관련된 리소스에 대한 정보입니다.

교착 상태를 분석할 때 이러한 노드를 단계별로 살펴보는 것이 유용합니다.

교착 상태 희생자 목록

교착 상태 희생자 목록은 교착 상태 희생자로 선택된 프로세스를 보여 줍니다. 교착 상태 그래프의 시각적 표현에서 프로세스는 타원으로 표시됩니다. 교착 상태 희생 프로세스는 타원 위에 "X"가 그려져 있습니다.

교착 상태의 시각적 개체 표시 스크린샷입니다. 희생자로 선택된 프로세스를 나타내는 타원형에는 X가 그려져 있습니다.

교착 상태 그래프의 XML 보기에서 victim-list 노드는 교착 상태의 희생자였던 프로세스에 대한 ID를 제공합니다.

이 교착 상태의 예에서 희생자 프로세스 ID는 process24756e75088입니다. 프로세스 목록 및 리소스 목록 노드를 검사할 때 이 ID를 사용하여 희생자 프로세스와 해당 프로세스가 잠그거나 잠금을 요청한 리소스에 대해 자세히 알아볼 수 있습니다.

교착 상태 프로세스 목록

교착 상태 프로세스 목록은 교착 상태와 관련된 트랜잭션에 대한 풍부한 정보 원본입니다.

교착 상태 그래프의 그래픽 표현은 교착 상태 그래프 XML에 포함된 정보의 하위 집합만 보여 줍니다. 교착 상태 그래프의 타원은 프로세스를 나타내며 다음을 포함한 정보를 표시합니다.

  • 세션 ID 또는 SPID라고도 하는 서버 프로세스 ID입니다.

  • 세션의 교착 상태 우선 순위입니다. 두 세션의 교착 상태 우선 순위가 다르면 교착 상태 우선 순위가 낮은 세션이 처리하지 않을 세션으로 선택됩니다. 이 예에서 두 세션의 교착 상태 우선 순위는 동일합니다.

  • 세션에서 사용하는 트랜잭션 로그의 양(바이트)입니다. 두 세션의 교착 상태 우선 순위가 동일한 경우 교착 상태 모니터는 교착 상태 희생자로 롤백하는 데 비용이 덜 드는 세션을 선택합니다. 비용은 각 트랜잭션에서 해당 시점에 기록된 로그 바이트 수를 비교하여 결정됩니다.

    이 교착 상태의 예에서 session_id 89는 더 적은 양의 트랜잭션 로그를 사용했으며 교착 상태의 희생자로 선택되었습니다.

또한 각 프로세스에 마우스를 가져가면 교착 상태가 발생하기 전에 각 세션에서 마지막으로 실행된 문에 대한 입력 버퍼를 볼 수 있습니다. 입력 버퍼가 툴팁에 나타납니다.

SSMS에 시각적으로 표시되는 교착 상태 그래프의 스크린샷입니다. 두 개의 타원형은 프로세스를 나타냅니다. 한 프로세스에 대한 입력 버퍼가 표시됩니다.

다음을 포함하여 교착 상태 그래프의 XML 보기에서 프로세스에 대한 추가 정보를 사용할 수 있습니다.

  • 클라이언트 이름, 호스트 이름 및 로그인 이름과 같은 세션에 대한 식별 정보입니다.
  • 교착 상태가 발생하기 전에 각 세션에서 실행한 마지막 문에 대한 쿼리 계획 해시입니다. 쿼리 계획 해시는 쿼리 저장소에서 쿼리에 대한 추가 정보를 쿼리하는 데 유용합니다.

Microsoft의 예에서 교착 상태:

  • 두 세션 모두 chrisqpublic 로그인에서 SSMS 클라이언트를 사용하여 실행되었음을 알 수 있습니다.
  • 교착 상태 희생자가 교착 상태 이전에 실행한 마지막 문의 쿼리 계획 해시는 0x02b0f58d7730f798입니다. 입력 버퍼에서 이 문의 텍스트를 볼 수 있습니다.
  • 교착 상태에서 다른 세션이 실행한 마지막 문의 쿼리 계획 해시도 0x02b0f58d7730f798입니다. 입력 버퍼에서 이 문의 텍스트를 볼 수 있습니다. 이 경우 같음 조건자로 사용되는 리터럴 값을 제외하고 쿼리가 동일하기 때문에 두 쿼리 모두 동일한 쿼리 계획 해시를 갖습니다.

이 문서 뒷부분에서 이 값을 사용하여 쿼리 저장소에서 추가 정보를 찾습니다.

교착 상태 프로세스 목록에서 입력 버퍼의 제한 사항

교착 상태 프로세스 목록의 입력 버퍼 정보와 관련하여 알아야 할 몇 가지 제한 사항이 있습니다.

쿼리 텍스트는 입력 버퍼에서 잘릴 수 있습니다. 입력 버퍼는 실행 중인 문의 처음 4,000자로 제한됩니다.

또한 교착 상태와 관련된 일부 문은 교착 상태 그래프에 포함되지 않을 수 있습니다. 이 예에서 세션 A는 단일 트랜잭션 내에서 두 개의 업데이트 문을 실행했습니다. 교착 상태를 유발한 업데이트인 두 번째 업데이트 문만 교착 상태 그래프에 포함됩니다. 세션 A에서 실행한 첫 번째 업데이트 문은 세션 B를 차단하여 교착 상태를 만들었습니다. 세션 A에 의해 실행된 첫 번째 문에 대한 입력 버퍼, query_hash 및 관련 정보는 교착 상태 그래프에 포함되지 않습니다.

교착 상태와 관련된 다중 문 트랜잭션에서 전체 Transact-SQL 실행을 식별하려면 쿼리를 실행한 저장 프로시저 또는 애플리케이션 코드에서 관련 정보를 찾거나 확장 이벤트를 사용해 추적을 실행하여 교착 상태가 발생하는 동안 교착 상태에 관련된 세션이 실행하는 전체 명령문을 캡처합니다. 교착 상태에 관련된 문이 잘리고 입력 버퍼에 Transact-SQL의 일부만 표시되는 경우 실행 계획을 사용하여 쿼리 저장소의 문에 대한 Transact-SQL을 찾을 수 있습니다.

교착 상태 리소스 목록

교착 상태 리소스 목록은 교착 상태에 있는 프로세스가 소유하고 대기하는 잠금 리소스를 보여 줍니다.

리소스는 교착 상태의 시각적 표현에서 직사각형으로 표시됩니다.

SSMS에 시각적으로 표시되는 교착 상태 그래프의 스크린샷입니다. 사각형은 교착 상태에 관련된 리소스를 표시합니다.

참고 항목

Azure SQL 데이터베이스의 데이터베이스에 대한 교착 상태 그래프에서 데이터베이스 이름이 고유한 식별자로 표시된다는 것을 알 수 있습니다. 이는 sys.databases 및 sys.dm_user_db_resource_governance 동적 관리 뷰에 나열된 데이터베이스에 대한 physical_database_name입니다.

이 예시에서 교착 상태:

  • 세션 A라고 하는 교착 상태 희생자:

    • SalesLT.Product 테이블의 PK_Product_ProductID 인덱스에 있는 키에 대한 배타적(X) 잠금을 소유합니다.
    • SalesLT.ProductDescription 테이블의 PK_ProductDescription_ProductDescriptionID 인덱스에 있는 키에 대한 업데이트(U) 잠금을 요청합니다.

  • 세션 B라고 하는 다른 프로세스:

    • SalesLT.ProductDescription 테이블의 PK_ProductDescription_ProductDescriptionID 인덱스에 있는 키에 대한 업데이트(U) 잠금을 소유합니다.
    • SalesLT.ProductDescription 테이블의 PK_ProductDescription_ProductDescriptionID 인덱스에 있는 키에 대한 공유(S) 잠금을 요청합니다.

resource-list 노드의 교착 상태 그래프의 XML에서도 동일한 정보를 볼 수 있습니다.

쿼리 저장소에서 쿼리 실행 계획 찾기

교착 상태와 관련된 문에 대한 쿼리 실행 계획을 검사하는 것은 종종 유용합니다. 이러한 실행 계획은 교착 상태 그래프의 프로세스 목록에 대한 XML 보기의 쿼리 계획 해시를 사용하여 쿼리 저장소에서 찾을 수 있습니다.

이 Transact-SQL 쿼리는 예시 교착 상태에 대해 찾은 쿼리 계획 해시와 일치하는 쿼리 계획을 찾습니다. Azure SQL 데이터베이스의 사용자 데이터베이스에 연결하여 쿼리를 실행합니다.

DECLARE @query_plan_hash binary(8) = 0x02b0f58d7730f798

SELECT 
    qrsi.end_time as interval_end_time,
    qs.query_id,
    qp.plan_id,
    qt.query_sql_text, 
    TRY_CAST(qp.query_plan as XML) as query_plan,
    qrs.count_executions
FROM sys.query_store_query as qs
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qrsi on qrs.runtime_stats_interval_id=qrsi.runtime_stats_interval_id
WHERE query_plan_hash =  @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

쿼리 저장소 CLEANUP_POLICY 또는 QUERY_CAPTURE_MODE 설정에 따라 쿼리 저장소에서 쿼리 실행 계획을 얻지 못할 수도 있습니다. 이 경우 쿼리에 대한 예상 실행 계획을 표시하여 필요한 정보를 얻을 수 있습니다.

차단을 증가시키는 패턴 찾기

교착 상태와 관련된 쿼리 실행 계획을 검사할 때 차단 및 교착 상태에 기여할 수 있는 패턴을 찾습니다.

  • 테이블 또는 인덱스 검사. 데이터를 수정하는 쿼리가 RCSI에서 실행될 때 업데이트할 행 선택은 데이터 값을 읽을 때 데이터 행에 업데이트(U) 잠금이 수행되는 차단 검사를 사용하여 수행됩니다. 데이터 행이 업데이트 기준을 충족하지 않으면 업데이트 잠금이 해제되고 다음 행이 잠기고 검사됩니다.

    수정 쿼리가 행을 보다 효율적으로 찾을 수 있도록 인덱스를 튜닝하면 발급된 업데이트 잠금 수가 줄어듭니다. 이렇게 하면 차단 및 교착 상태의 가능성이 줄어듭니다.

  • 두 개 이상의 테이블을 참조하는 인덱싱된 뷰. 인덱싱된 뷰에서 참조되는 테이블을 수정할 때 데이터베이스 엔진은 인덱싱된 뷰도 유지해야 합니다. 이를 위해서는 더 많은 잠금을 해제해야 하며 차단 및 교착 상태가 증가할 수 있습니다. 인덱싱된 뷰로 인해 업데이트 작업이 커밋된 읽기 격리 수준에서 내부적으로 실행될 수도 있습니다.

  • 외래 키 제약 조건에서 참조되는 열 수정. FOREIGN KEY 제약 조건에서 참조되는 테이블의 열을 수정할 때 데이터베이스 엔진은 참조하는 테이블에서 관련 행을 찾아야 합니다. 이러한 읽기에는 행 버전을 사용할 수 없습니다. 계단식 업데이트 또는 삭제가 사용하도록 설정된 경우 격리 수준은 가상 삽입으로부터 보호하기 위해 문의 기간 동안 직렬화 가능으로 에스컬레이션될 수 있습니다.

  • 잠금 힌트. 더 많은 잠금이 필요한 격리 수준을 지정하는 테이블 힌트를 찾습니다. 이러한 힌트에는 HOLDLOCK(직렬화 가능), SERIALIZABLE, READCOMMITTEDLOCK(RCSI 사용 안 함) 및 REPEATABLEREAD가 포함됩니다. 또한 PAGLOCK, TABLOCK, UPDLOCK 및 XLOCK과 같은 힌트는 차단 및 교착 상태의 위험을 증가시킬 수 있습니다.

    이러한 힌트가 있는 경우 힌트가 구현된 이유를 조사합니다. 이러한 힌트는 경쟁 조건을 방지하고 데이터 유효성을 보장할 수 있습니다. 이러한 힌트를 그대로 두고 필요한 경우 이 문서의 교착 상태가 다시 발생하지 않도록 방지 섹션에 있는 대체 방법을 사용하여 향후 교착 상태를 방지할 수 있습니다.

    참고 항목

    트랜잭션 잠금 및 행 버전 관리 가이드에서 행 버전 관리를 사용하여 데이터를 수정할 때의 동작에 대해 자세히 알아봅니다.

실행 계획이나 애플리케이션 쿼리 코드에서 트랜잭션의 전체 코드를 검사할 때 발생하는 문제가 있는 추가 패턴을 찾습니다.

  • 트랜잭션에서의 사용자 상호 작용. 명시적 다중 문 트랜잭션 내부의 사용자 상호 작용은 트랜잭션 기간을 크게 늘립니다. 이로 인해 이러한 트랜잭션이 겹치고 차단 및 교착 상태가 발생할 가능성이 높아집니다.

    마찬가지로 열린 트랜잭션을 유지하고 관련 없는 데이터베이스 또는 시스템 트랜잭션 중간에 쿼리하면 차단 및 교착 상태가 발생할 가능성이 크게 높아집니다.

  • 다른 순서로 개체에 액세스하는 트랜잭션. 동시 명시적 다중 문 트랜잭션이 동일한 패턴을 따르고 동일한 순서로 개체에 액세스할 때 교착 상태가 발생할 가능성이 적습니다.

교착 상태가 다시 발생하지 않도록 방지

인덱스 튜닝, 쿼리 저장소로 계획 강제 실행, Transact-SQL 쿼리 수정을 포함하여 교착 상태가 다시 발생하지 않도록 방지하는 데 사용할 수 있는 여러 기술이 있습니다.

  • 테이블의 클러스터형 인덱스를 검토. 대부분의 테이블은 클러스터형 인덱스의 이점을 활용하며 실수로 테이블이 힙으로 구현되기도 합니다.

    클러스터형 인덱스 확인 방법 중 하나는 sp_helpindex 시스템 저장 프로시저를 사용하는 것입니다. 예를 들어 다음 문을 실행하여 SalesLT.Product테이블의 인덱스 요약을 볼 수 있습니다.

    exec sp_helpindex 'SalesLT.Product';
GO

    index_description 열을 검토합니다. 각 테이블마다 클러스터형 인덱스를 하나만 포함할 수 있습니다. 클러스터형 인덱스가 테이블에 구현된 경우 index_description에는 'clustered'라는 단어가 포함됩니다.

    클러스터형 인덱스가 없으면 테이블은 힙입니다. 이 경우 특정 성능 문제를 해결하기 위해 테이블이 의도적으로 힙으로 만들어졌는지 검토합니다. 클러스터형 인덱스 디자인 지침에 따라 클러스터형 인덱스 구현을 고려합니다.

    경우에 따라 클러스터형 인덱스를 만들거나 튜닝하면 교착 상태의 차단이 줄어들거나 제거될 수 있습니다. 다른 경우에는 이 목록의 다른 기술과 같은 추가 기술을 사용해야 할 수도 있습니다.

  • 비클러스터형 인덱스를 만들거나 수정합니다. 비클러스터형 인덱스를 튜닝하면 수정 쿼리가 업데이트할 데이터를 더 빨리 찾는 데 도움이 되어 필요한 업데이트 잠금 수를 줄일 수 있습니다.

    이 교착 상태의 예에서 쿼리 저장소에 있는 쿼리 실행 계획에는 PK_Product_ProductID 인덱스에 대한 클러스터형 인덱스 검사가 포함되어 있습니다. 교착 상태 그래프는 이 인덱스에 대한 공유(S) 잠금 대기가 교착 상태의 구성 요소임을 나타냅니다.

    쿼리 실행 계획의 스크린샷입니다. 클러스터형 인덱스 스캔이 Product 테이블의 PK_Product_ProductID 인덱스에 대해 수행되고 있습니다.

    업데이트 쿼리가 vProductAndDescription이라는 인덱싱된 뷰를 수정해야 하기 때문에 이 인덱스 검사가 수행되고 있습니다. 이 문서의 차단을 증가시키는 패턴 찾기 섹션에서 언급했듯이 여러 테이블을 참조하는 인덱싱된 뷰는 차단과 교착 상태의 가능성을 증가시킬 수 있습니다.

    인덱싱된 뷰에서 참조하는 SalesLT.Product의 열을 "포괄"하는 다음과 같은 비클러스터형 인덱스를 AdventureWorksLT 데이터베이스에 만들면 쿼리가 행을 훨씬 더 효율적으로 찾는 데 도움이 됩니다.

    CREATE INDEX ix_Product_ProductID_Name_ProductModelID on SalesLT.Product (ProductID, Name, ProductModelID);
GO

    이 인덱스를 만든 후에는 교착 상태가 더 이상 발생하지 않습니다.

    교착 상태가 외래 키 제약 조건에서 참조되는 열에 대한 수정을 포함하는 경우 FOREIGN KEY의 참조 테이블에 있는 인덱스가 관련 행을 효율적으로 찾는 것을 지원하는지 확인합니다.

    인덱스는 경우에 따라 쿼리 성능을 크게 향상할 수 있지만 인덱스에는 오버헤드 및 관리 비용도 있습니다. 인덱스, 특히 넓은 인덱스와 큰 테이블의 인덱스를 만들기 전에 인덱스의 이점을 평가하는 데 도움이 되도록 일반 인덱스 디자인 지침을 검토합니다.

  • 인덱싱된 뷰의 가치 평가. 예시 교착 상태가 다시 발생하지 않도록 하는 또 다른 옵션은 SalesLT.vProductAndDescription 인덱싱된 뷰를 삭제하는 것입니다. 해당 인덱싱된 뷰가 사용되지 않는 경우 시간이 지남에 따라 인덱싱된 뷰를 유지 관리하는 오버헤드가 줄어듭니다.

  • 스냅샷 격리를 사용합니다. 경우에 따라 교착 상태와 관련된 하나 이상의 트랜잭션에 대한 스냅샷으로 트랜잭션 격리 수준을 설정하면 차단 및 교착 상태가 다시 발생하지 않을 수 있습니다.

    이 기술은 데이터베이스에서 커밋된 스냅샷 읽기가 사용하지 않도록 설정된 경우 SELECT 문에 사용할 때 성공할 가능성이 가장 높습니다. 커밋된 스냅샷 읽기가 사용하지 않도록 설정된 경우 커밋된 읽기 격리 수준을 사용하는 SELECT 쿼리에는 공유(S) 잠금이 필요합니다. 이러한 트랜잭션에서 스냅샷 격리를 사용하면 차단 및 교착 상태를 방지할 수 있는 공유 잠금이 필요하지 않습니다.

    읽기 커밋된 스냅샷 격리가 사용하도록 설정된 데이터베이스에서 SELECT 쿼리에는 공유(S) 잠금이 필요하지 않으므로 데이터를 수정하는 트랜잭션 간에 교착 상태가 발생할 가능성이 더 높습니다. 데이터를 수정하는 여러 트랜잭션 간에 교착 상태가 발생하는 경우 스냅샷 격리로 인해 교착 상태가 아닌 업데이트 충돌이 발생할 수 있습니다. 마찬가지로 트랜잭션 중 하나가 작업을 다시 시도해야 합니다.

  • 쿼리 저장소에서 계획 실행. 교착 상태에 있는 쿼리 중 하나에 여러 실행 계획이 있고 교착 상태는 특정 계획이 사용될 때만 발생한다는 것을 알 수 있습니다. 쿼리 저장소에서 계획을 강제 실행하여 교착 상태가 다시 발생하는 것을 방지할 수 있습니다.

  • Transact-SQL 수정. 교착 상태가 다시 발생하지 않도록 Transact-SQL을 수정해야 할 수 있습니다. Transact-SQL 수정은 신중하게 수행되어야 하며 수정이 동시에 실행될 때 데이터가 올바른지 확인하기 위해 변경 내용을 엄격하게 테스트해야 합니다. Transact-SQL을 다시 작성할 때 다음을 고려합니다.

    • 트랜잭션에서 동일한 순서로 개체에 액세스하도록 문을 정렬합니다.
    • 가능하면 트랜잭션을 더 작은 트랜잭션으로 나눕니다.
    • 필요한 경우 쿼리 힌트를 사용하여 성능을 최적화합니다. 쿼리 저장소를 사용하여 애플리케이션 코드를 변경하지 않고 힌트를 적용할 수 있습니다.

더 알아보려면 교착 상태 가이드에서 교착 상태 최소화를 참조하세요.

참고 항목

경우에 따라 세션 중 하나가 재시도 없이 성공적으로 완료되는 것이 중요한 경우 또는 교착 상태와 관련된 쿼리 중 하나가 중요하지 않고 항상 희생자로 선택되어야 하는 경우 교착 상태와 관련된 하나 이상의 세션의 교착 상태 우선 순위를 조정할 수 있습니다. 이것이 교착 상태가 다시 발생하는 것을 방지하지는 않지만 향후 교착 상태의 영향을 줄일 수 있습니다.

XEvents 세션 삭제

중요한 데이터베이스에서 오랫동안 실행 중인 교착 상태 정보를 수집하는 XEvents 세션을 그대로 둘 수 있습니다. 이벤트 파일 대상을 사용하는 경우 여러 교착 상태가 발생하면 파일이 커질 수 있다는 점에 유의합니다. 현재 기록 중인 파일을 제외하고 활성 추적을 위해 Azure Storage에서 blob 파일을 삭제할 수 있습니다.

XEvents 세션을 제거하려는 경우 선택한 대상 유형에 관계없이 Transact-SQL 삭제 세션은 동일합니다.

XEvents 세션을 제거하려면 다음 Transact-SQL을 실행합니다. 코드를 실행하기 전에 세션 이름을 적절한 값으로 바꿉니다.

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Azure Storage Explorer 사용

Azure Storage Explorer는 Azure Storage의 blob에 저장된 이벤트 파일 대상 작업을 간소화하는 독립 실행형 애플리케이션입니다. Storage Explorer를 사용하여 다음을 수행할 수 있습니다.

Azure Storage Explorer를 다운로드합니다.

다음 단계

Azure SQL 데이터베이스의 성능에 대해 자세히 알아봅니다.