[MSSQL] 데드락(교착상태) 리포트 생성 및 추적 방법 (XML 설명 추가)

데드락 리포트 생성

데드락에 대한 추적을 하기 위해서 MSSQL에 리포트를 활성화 해야합니다.

1. 관리-확장이벤트-세션-system_tealth의 속성을 엽니다

 

2.이벤트 항목 탭을 클릭하고

이벤트 라이브러리의 xml_deadlock_report 항목을 선택한이벤트 항목으로 옮기고 확인을 누릅니다.

리포트 생성 설정이 완료되었습니다.

---

 

 

 

 

 

 

데드락 실습 예제

데드락 XML 리포트 수집이 설정되었으니 데드락을 발생시켜 실습을 진행하겠습니다.

데이터베이스는 AdventureWorks2016 예제 디비를 사용했습니다.

 

1)데드락 테스트를 위한 테이블 생성하기

테이블 2개를 생성합니다.

use [AdventureWorks2016]
GO

-- Invoices
SELECT *
INTO dbo.Invoices
FROM [AdventureWorks2016].[Sales].[SalesOrderHeader]

-- InvoiceDetails
SELECT *
INTO dbo.InvoiceDetails
FROM [AdventureWorks2016].[Sales].[SalesOrderDetail]

 

2)업데이트 구문 실행(새쿼리창 1번)

새쿼리창1번을 열어 1번 쿼리를 실행합니다.

트랜젝션은 열어둔 상태입니다.

--1번 쿼리실행

use AdventureWorks2016

go

DECLARE @SalesOrderId BIGINT;

DECLARE @SumValue BIGINT;

SET @SalesOrderId = 43659;

 

BEGIN TRAN

 

SELECT @SumValue = SUM(LineTotal) -- we will round up to next int

FROM dbo.InvoiceDetails

WHERE SalesOrderId = @SalesOrderId

 

UPDATE dbo.Invoices

SET

    SubTotal = @SumValue ,

TaxAmt   = @SumValue * .21

WHERE SalesOrderId = @SalesOrderId

 

 

3)업데이트 구문 실행(새쿼리창 2번)

새쿼리창2번을 열어 1번 쿼리를 실행합니다.

'2)업데이트 구문(새쿼리창 1번)' 에서 실행한 업데이트 구문으로 대기가 발생합니다.

use AdventureWorks2016

go

--1번쿼리 스크립트 실행 후 실행

DECLARE @SalesOrderId BIGINT;

DECLARE @SumValue BIGINT;

SET @SalesOrderId = 43659;

 

BEGIN TRAN

 

UPDATE dbo.InvoiceDetails SET LineTotal = 0 where SalesOrderId = @SalesOrderId ;

SELECT * FROM dbo.Invoices where SalesOrderId = @SalesOrderId ; --select 대기 발생

 

4)조회구문 실행(새쿼리창 1번)

다시 새쿼리창 1번 으로 돌아와 아래의 조회 쿼리 2번을 실행합니다

--2번 쿼리 스크립트 실행 후 조회(교착상태 발생)
select * from dbo.InvoiceDetails --with(nolock)

 

실행 후 아래와 같이 데드락 오류 메시지를 확인할 수 있습니다.

메시지 1205, 수준 13, 상태 45, 줄 20

트랜잭션(프로세스 ID 56)이 잠금 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. 트랜잭션을 다시 실행하십시오.

 

*데드락의 희생자를 결정하는 방식은 일반적으로 SQL서버가 롤백 할 경우 자원이 덜 소모될 것으로 예상되는 세션으로 선택됩니다.

 

5)오류로그 확인하기

sp_readerrorlog 명령어로 데드락이 발생한 로그 확인이 가능합니다.

만약 보이지 않는다면 아래의 교착상태 에러로그 활성화 명령어를 입력 한 다음 다시 한번 위의 과정을 반복해 보시기 바랍니다.

--교착상태 에러로그 활성화
EXEC sp_altermessage 1205, 'WITH_LOG', 'true'

 

 

---

XML 리포트 변환

우리는 앞서 데드락 리포트를 설정해 두었습니다.

따라서 SQL Server 시스템에서 .xel 확장자로 리포트가 생성됩니다.

생성된 xml_deadlock_repor.xel 리포트를 가져와서 XML로 변경하는 구문은 다음과 같습니다.

SELECT        CAST(event_data AS XML) AS event_data                
FROM        sys.fn_xe_file_target_read_file(N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_TEST\MSSQL\Log\system_health_*.xel', null, null, null) 
WHERE        object_name = 'xml_deadlock_report'

 

 

 

Timestamp를 이용하여 발생한 시간대에 XML 리포트를 클릭하면 아래와 같이 XML 정보를 쿼리로 확인이 가능합니다.

 <victimProcess id="process26759605c28" />는 희생자 ID 입니다. 해당 ID로 검색하면 어떤 쿼리가 희생되었는지 알 수 있습니다.

---

XML 리포트 설명

Deadlock XML은 3개 섹션으로 구성됩니다.

 

 - 희생자 명단

 - 프로세스 목록 및

 - 리소스 목록

 

희생자 명단

피해자 목록은 정확히 데드락 피해자로 선택된 프로세스 ID 목록입니다. 이 특별한 경우에는 피해자가 한 명 뿐이지만 데드락에 여러 명의 피해자가 포함될 수 있습니다.

프로세스 목록

프로세스 목록에는 데드락에 관련된 각 프로세스에 대해 하나의 노드가 포함됩니다. MSDN 별로 각 프로세스에 대해 다음 세부 정보가 표시됩니다 .

ID	특정 프로세스의 프로세스 ID
Task priority	이것은 교착 상태 우선 순위입니다. 일반적인 값은 낮음 : -5, 정상: 0 및 , 높음: 5
Log used	프로세스에서 사용하는 트랜잭션 로그의 양
Waitresource	Waitresource의 형식은 다음과 같습니다 PAGE: 5:1:27056 첫 번째 부분인 Page는 리소스 유형을 나타냅니다. 이 경우 열쇠. 두 번째 부분: 5은 데이터베이스 ID입니다. 세 번째 부분: 1은 File_id입니다. 네 번째 부분: 27056은 페이지 번호 입니다.
Waittime	리소스가 사용 가능해질 때까지 프로세스가 대기한 시간
Ownerid	프로세스를 소유한 트랜잭션의 트랜잭션 ID를 표시합니다. 이 값은 sys.dm_tran_locks DMV의 request_owner_id 필드에 해당합니다.
Input buffer	이벤트 유형과 실행되는 코드를 설명합니다. 즉, 실행 중인 SQL 스크립트가 여기에 표시됩니다.
Statement	다음과 같은 명령문 유형: SELECT UPDATE INSERT DELETE NOP(작동 없음)

 

 

XML 자체를 보면 MSDN에 언급되지 않은 몇 가지 다른 세부 사항을 알 수 있습니다. 

MSDN이 데드락 그래프의 그래픽 표현에서만 사용할 수 있는 세부 정보를 참조하는 것 같습니다. 

이것이 우리가 XML을 살펴봐야 하는 이유입니다. 추가 필드는 다음과 같습니다.

transactionname	트랜잭션 이름 또는 트랜잭션 유형: 예: user_transaction
Lasttranstarted	마지막 트랜잭션이 시작된 날짜 시간
XDES	트랜잭션 설명 구조
LockMode	프로세스가 취하고자 하는 잠금 유형
Schedulerid	프로세스가 실행 중인 프로세서의 ID
Kpid	Windows 스레드 ID
Status	프로세스의 상태입니다. 즉: Suspended Dormant Running Background Rollback Pending Runnable Spinloop
Spid	SQL 프로세스 ID
Sbid	배치 ID
Ecid	이것은 프로세스가 병렬로 실행될 때의 실행 컨텍스트입니다. 병렬로 실행되지 않으면 이 값은 0이 됩니다.
Priority	교착상태 태스크 우선순위와 동일
Trancount	교착 상태와 관련된 트랜잭션 수
lastbatchstarted	마지막 배치가 시작된 날짜 시간
lastbatchcompleted	마지막 배치가 완료된 날짜 시간
lastattention	교착 상태와 관련된 스레드의 시간 초과 또는 취소와 같은 마지막 주의 이벤트 처리의 타임스탬프입니다.
Clientapp	프로세스를 생성한 클라이언트 애플리케이션
Hostname	클라이언트가 실행 중인 호스트 이름
Hostpid	클라이언트 애플리케이션의 프로세스 ID
Loginname	로그인한 사용자의 이름
Isolationlevel	프로세스의 격리 수준
Xactid	프로세스의 트랜잭션 ID
Currentdb	프로세스가 실행된 데이터베이스의 데이터베이스 ID
lockTimeout	프로세스가 잠금이 해제될 때까지 대기할 수 있는 최대 시간
Clientoption1	ANSI_NULLS 또는 QUOTED_IDENTIFIER와 같은 SET 옵션입니다.
Clientoption2	더 많은 SET 옵션

그래픽 보기에서 볼 수 없는 프로세스 목록에서 관심을 끄는 주요 사항은 프로세스의 격리 수준(Isolationlevel)입니다. 

예를 들어 격리 수준이 직렬화 가능한 경우 교착 상태의 원인이 될 수 있습니다. 그런 다음 돌아가서 이 격리 수준이 필요한지 확인하고 필요한 경우 관련된 모든 쿼리를 검토하여 항상 같은 순서로 테이블에 액세스하는지 확인해야 합니다.

 

 

리소스 목록

리소스 목록은 데드락과 관련된 각 리소스에 대한 자세한 정보를 제공합니다. 리소스는 행, 키, 페이지, 익스텐트, 힙 또는 B-트리(HoBT), 테이블, 파일, 할당 단위 또는 메타데이터일 수 있습니다. 

여기서 데드락은 페이지 또는 인덱스와 관련되었습니다.

리소스 목록에서 다음 정보를 사용할 수 있습니다.

pageid	페이지 ID
dbID	리소스가 존재하는 데이터베이스 ID
objectName	자원의 이름
Id	키 잠금의 ID
Mode	잠금 모드
associatedObjectId	교착 상태가 발생한 개체의 ID
Owner-list	프로세스 소유자 및 해당 잠금 모드에 대한 정보
Waiter-list	리소스를 대기 중인 프로세스, 요청된 잠금 유형 및 요청 유형에 대한 정보입니다.

 

데드락과 관련된 각 리소스에 대해 리소스 노드가 포함됩니다.

 

결론

데드락 그래프는 데드락에 대한 정보를 얻는 가장 쉬운 방법입니다. 그래프를 보는 것만으로도 많은 정보를 볼 수 있지만 일반적으로 관련된 잠금 유형, 사용된 격리 수준 등과 같이 교착 상태를 해결할 수 있으려면 더 자세한 정보가 필요합니다. 교착 상태에 대한 XML 설명 교착 상태 문제를 훨씬 쉽게 해결할 수 있는 수많은 추가 정보를 제공합니다. 따라서 교착 상태 그래프를 사용하는 대신 그래프의 XML 설명도 확인해야 합니다.