[MSSQL] 데드락(DeadLock) 발생 로그 확인 및 원인 찾기

SQL SERVER 운영시 프로시저가 동작을 안하고 뻗어버리는 경우가 종종 발생합니다.

 

일반적으로 Lock이 발생하였을 때 경우는

1. 대용량 데이터 처리로 인해 쿼리문의 실행시간이 길어져 오랜시간 Lock을 잡고 있는 경우

2. 트랜잭션을 걸었지만 Commit 또는 Rollback되지 않은 경우

3. 리소스 경합으로 인한 교착상태(DeadLock) 발생 

 

Lock을 해제하는 방법은 다음의 글 참고 부탁드립니다.

 

https://omty.tistory.com/29

[MSSQL] Lock 걸린 쿼리 확인하고 해제 및 방지하기

 

데드락을 확인하는 법은 간단합니다.

 

EXEC sp_readerrorlog

 

위의 쿼리 실행시 (결과값을 텍스트로 보기로 하시면 더 가독성있게 확인이 가능합니다.) 

 

간단히 설명을 드리자면

맨위에 deadlock victim이라고 희생된 프로세스가 표시가 되며 데드락이 발생한 쿼리문이 표시가 됩니다. 

 

초록색 박스를 보시면 어떤 테이블에서 경합이 벌어졌는지 확인이 가능하며 

두번째 파란색 박스가 있는 쿼리문을 보면 조인을 통해 mode=RangeS-S 로 공유락을 요청했지만 이미 해당 테이블에 X로 배타락이 걸려 있어 데드락이 발생한 경우입니다.

 

일반적으로 SQL Serer는 데드락 발생시 가장 손해가 적은 트랜잭션을 실행하는 스레드를 희생자로 선택합니다.

데드락을 무조건 막을 수는 없지만 최소화 할 수 있는 방안은 있습니다.

 

1. 트랜잭션을 일괄처리로 작은 시간단위로 유지한다.

트랜잭션을 길게 유지할 경우 해당 테이블에 대한 배타적 Lock의 잠금 시간도 길어지기 때문에 쿼리 성능을 개선하여  하나의 일괄처리로 짧은 시간내에 데이터를 처리하는 것이 좋습니다.

 

2. 낮은 고립 수준을 사용한다.
일반적으로 대용량 데이터 저장 및 처리시 데드락이 많이 발생합니다. 사용자에게 보여지는 데이터의 정합성이 항상 일치하지 않아도 된다면(일반적인 분석 및 통계 쿼리시) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 구문이나 SELECT 절의 WITH(NOLOCK) 옵션을 통해 Lock여부에 상관없이 데이터를 읽어올 수 있습니다.