MSSQL 교착 상태 완벽 이해와 예방 방법

🔍 교착 상태란?

교착 상태(Deadlock)는 데이터베이스나 시스템 환경에서 두 개 이상의 프로세스 또는 세션이 서로가 보유한 리소스를 기다리며 무한 대기 상태에 빠지는 현상을 의미합니다.
MSSQL에서는 주로 트랜잭션이 테이블이나 행(row)에 잠금(Lock)을 건 상태에서, 다른 트랜잭션이 그 잠금을 해제해주기를 기다리는 과정에서 발생합니다.
문제는 각 트랜잭션이 서로의 리소스를 요구하는 구조가 되면, 어느 쪽도 작업을 마무리할 수 없게 된다는 점입니다.

이 현상은 단순한 지연과 달리, 시스템이 자체적으로 문제를 감지하지 않는다면 영구적으로 지속될 수 있습니다.
MSSQL은 일정 시간 간격으로 교착 상태를 감지하고, 피해를 최소화하기 위해 그 중 하나의 트랜잭션을 강제로 종료합니다.
이 과정에서 종료된 트랜잭션은 “deadlock victim”으로 지정되며, 해당 작업은 롤백됩니다.

📌 교착 상태 발생 예시

예를 들어, 세션 A가 테이블 1의 행을 잠근 뒤 테이블 2의 행을 요청하고, 동시에 세션 B가 테이블 2의 행을 잠근 뒤 테이블 1의 행을 요청하는 경우를 생각해봅시다.
이 상황에서는 두 세션 모두 상대방이 가진 잠금을 기다리게 되고, 외부 개입 없이는 절대 해결되지 않는 대기 상태가 형성됩니다.

-- 세션 A
BEGIN TRAN;
UPDATE Table1 SET Col1 = 'A' WHERE ID = 1;
UPDATE Table2 SET Col2 = 'B' WHERE ID = 2;

-- 세션 B
BEGIN TRAN;
UPDATE Table2 SET Col2 = 'B' WHERE ID = 2;
UPDATE Table1 SET Col1 = 'A' WHERE ID = 1;

🛠️ 교착 상태 발생 원인

MSSQL에서 교착 상태는 대부분 트랜잭션의 리소스 잠금 순서와 트랜잭션 범위 문제에서 비롯됩니다.
특히 여러 세션이 동일한 데이터에 접근하고 수정하려 할 때, 잠금 순서가 어긋나면 순환 대기(Circular Wait)가 발생하게 됩니다.
이 외에도 시스템 구조나 인덱스 설계 문제, 과도한 동시성 요청 등 다양한 원인이 작용할 수 있습니다.

📌 주요 원인별 설명

• 🔄트랜잭션 간 리소스 요청 순서 불일치로 인한 순환 대기
• ⏳불필요하게 긴 트랜잭션 범위로 인해 잠금 지속 시간 증가
• 📊적절하지 않은 인덱스 설계로 인한 광범위 잠금
• 👥과도한 동시성 처리 요청으로 인한 경합
• ⚙️트리거나 저장 프로시저에서 복잡한 쿼리 호출

📌 내부 동작 관점

교착 상태는 시스템 내부적으로 ‘대기 그래프(Wait-for Graph)’ 형태로 표현됩니다.
여기서 노드는 트랜잭션, 간선은 리소스 요청 관계를 나타내며, 이 그래프에 사이클이 생기면 교착 상태로 판정됩니다.
MSSQL의 Lock Monitor 스레드는 주기적으로 이 그래프를 검사하여 사이클이 발견되면 강제 해제를 수행합니다.

📈 교착 상태 진단과 분석 방법

MSSQL에서는 교착 상태를 자동으로 감지하지만, 정확한 원인을 분석하려면 추가적인 진단 절차가 필요합니다.
실시간 모니터링 도구나 시스템 로그를 활용하면, 어떤 쿼리와 세션이 문제를 일으켰는지 구체적으로 파악할 수 있습니다.
특히 DBA 입장에서는 교착 상태 발생 직전의 리소스 점유 상황과 잠금 종류를 아는 것이 매우 중요합니다.

📌 기본 진단 방법

• 🔍SQL Server Management Studio(SSMS)의 Activity Monitor 활용
• 📜서버 오류 로그(Error Log) 및 시스템 헬스 확장 이벤트(System Health Extended Event) 확인
• 📊sp_who2, sys.dm_exec_requests DMV로 세션 및 잠금 상태 점검
• 💾교착 상태 그래프(Deadlock Graph) XML 파일 분석

📌 예시 쿼리

-- 현재 교착 상태 관련 세션 확인
SELECT * 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

-- 교착 상태 XML 캡처
DBCC TRACEON(1222, -1);

💡 예방을 위한 쿼리 설계 전략

교착 상태는 본질적으로 두 세션이 서로의 리소스를 기다리며 순환 대기를 만드는 구조에서 발생합니다.
따라서 예방의 핵심은 트랜잭션 순서를 일관되게 맞추고 트랜잭션 범위를 최소화하는 것입니다.
여기에 적절한 인덱싱과 격리 수준 조정, 읽기/쓰기 분리, 짧은 잠금 유지 원칙을 더하면 실무 환경에서 대부분의 교착 상태를 사전에 차단할 수 있습니다.
아래 체크리스트와 예제를 참고해, 애플리케이션과 데이터베이스 양쪽에서 동시에 개선을 진행해 보세요.

🧭 일관된 리소스 접근 순서

서비스 전 구간에서 테이블과 행을 접근하는 순서를 표준화합니다.
예를 들어 항상 TableA → TableB → TableC 순서로 잠금을 획득하도록 규칙을 정하면 순환 대기를 끊을 수 있습니다.
트랜잭션 내부에서 조건 분기 때문에 순서가 뒤바뀌지 않도록 저장 프로시저로 캡슐화하는 것도 좋은 방법입니다.

✂️ 트랜잭션 범위 최소화

사용자 입력 검증, 외부 API 호출, 파일 처리 같은 작업은 트랜잭션 밖으로 이동합니다.
업데이트가 필요한 핵심 구문만 트랜잭션으로 감싸 잠금 시간을 줄이세요.
가능하다면 배치 업데이트를 작은 단위로 나누어 커밋 간격을 좁히는 것도 효과적입니다.

• 🧩모든 서비스에서 리소스 접근 순서 표준 문서화 및 코드 리뷰 체크
• ⏱️트랜잭션 내 대기 작업 제거 및 커밋 지연 금지
• 🧮적절한 커버링 인덱스로 잠금 범위 축소
• 🔎읽기 전용 트래픽은 READ COMMITTED SNAPSHOT 등 버전 기반 격리 검토
• 🧯필요 시 행 수준 잠금 힌트(UPDLOCK, ROWLOCK)로 과도한 범위 잠금 방지

🧱 인덱스 및 격리 수준 전술

필요한 컬럼만 효율적으로 탐색하도록 인덱스를 구성하면 스캔 범위가 줄어 잠금 경쟁이 감소합니다.
또한 트랜잭션 격리 수준을 무리하게 높이면 잠금 지속 시간이 길어질 수 있으니, 읽기 트래픽은 스냅샷 기반 격리를 도입해 쓰기 작업과 충돌을 피하세요.

-- 읽기 트래픽 충돌 완화: 데이터베이스 단위 스냅샷 격리
ALTER DATABASE MyDb SET READ_COMMITTED_SNAPSHOT ON;  -- 운영 전 부하 테스트 권장

-- 일관된 접근 순서 보장 예시 (A -> B)
BEGIN TRAN;
UPDATE dbo.TableA WITH (UPDLOCK, ROWLOCK) SET Col = @v WHERE Id = @idA;
UPDATE dbo.TableB WITH (UPDLOCK, ROWLOCK) SET Col = @v WHERE Id = @idB;
COMMIT;

⚙️ 실시간 모니터링과 자동 해제 설정

교착 상태를 완전히 막을 수는 없지만, 실시간 모니터링과 자동 해제 설정을 통해 피해를 최소화할 수 있습니다.
MSSQL은 기본적으로 교착 상태를 감지하면 그 중 하나의 트랜잭션을 자동으로 종료하지만, 더 빠른 대응을 위해 DBA가 직접 모니터링 체계를 구성하는 것이 좋습니다.
이를 위해 시스템 헬스 이벤트, 확장 이벤트, 사용자 정의 경고(Alerts) 등을 적극적으로 활용할 수 있습니다.

📡 확장 이벤트(Extended Events) 활용

확장 이벤트는 교착 상태를 포함한 다양한 성능 문제를 실시간으로 포착할 수 있는 강력한 도구입니다.
특히 xml_deadlock_report 이벤트를 설정하면 발생 즉시 XML 형태의 그래프를 저장해 분석할 수 있습니다.
이를 통해 특정 쿼리 패턴이나 애플리케이션 동작에서 반복적으로 교착 상태가 발생하는지 추적할 수 있습니다.

CREATE EVENT SESSION [DeadlockMonitor]
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename='C:\XE\Deadlock.xel', max_file_size=5, max_rollover_files=5)
WITH (MAX_MEMORY=4096KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS);
ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = START;

⏱️ 경고 및 자동 스크립트

SQL Server Agent를 이용해 교착 상태 이벤트를 감지하면 자동으로 관리자에게 이메일을 발송하거나, 특정 세션을 강제 종료하는 스크립트를 실행할 수 있습니다.
이 방식은 운영 중단 시간을 줄이고, 재발 시 빠른 대처가 가능합니다.

• 🛰️확장 이벤트로 xml_deadlock_report 실시간 수집
• 📨SQL Server Agent 경고로 관리자 알림
• 🔌자동 세션 종료 스크립트 실행
• 📊주기적 로그 분석으로 패턴 파악

❓ 자주 묻는 질문 (FAQ)