MSSQL 잠금 종류와 BLOCK / DEADLOCK 정확히 이해하기

wait_resource 로그를 보고 원인까지 추적하기

이때 잠금(lock), 블로킹(block), 데드락(deadlock) 개념과 함께, DMV/로그에 나오는 wait_resource 값의 의미를 정확히 이해하고 있으면 다음을 빠르게 할 수 있습니다.

운영 중인 MSSQL(SQL Server) 서버에서 응답이 갑자기 느려질 때가 있습니다. 모니터링 도구에는 LCK_M_XXX 같은 대기(wait)가 쌓이고, 심하면 deadlock victim 오류까지 발생합니다.

• “지금 누가 누구를 막고 있는지” 파악
• “어떤 리소스(테이블/인덱스/페이지/키)에서 충돌하는지” 역추적
• 재발 방지(튜닝/설계 개선) 방향 수립

이 글에서는 다음을 한 번에 정리합니다.

• MSSQL 잠금의 기본 개념과 주요 잠금 종류
• BLOCK vs DEADLOCK 차이
• DMV와 로그에서 블로킹/데드락을 찾는 방법
• wait_resource 값을 해석해 문제 테이블/인덱스까지 역추적하는 방법

---

1. MSSQL 잠금 기본 개념

1-1. 잠금이 걸리는 대상(Resource)

MSSQL에서 잠금은 다양한 “리소스 단위”에 걸립니다. 실무에서 특히 많이 마주치는 핵심은 아래 네 가지입니다.

• DATABASE: 데이터베이스 단위
• OBJECT: 테이블/뷰 등 오브젝트 단위
• PAGE: 데이터 페이지(8KB) 단위
• KEY: 인덱스 키(레코드 수준) 단위

추가로 상황에 따라 아래도 종종 보입니다.

• RID: Heap(클러스터드 인덱스 없는 테이블)의 행(row) 식별자 단위
• EXTENT / METADATA 등

DMV나 Deadlock XML에서 자주 보는 wait_resource 값은 “어떤 리소스를 두고 경쟁 중인지”를 나타내는 문자열입니다. 예:

• KEY: 5:72057594044579840 (…hash…)
• PAGE: 5:1:12345
• OBJECT: 5:123456789

---

1-2. 주요 잠금 모드(잠금 종류)

운영에서 자주 보는 잠금 모드는 아래 정도만 정확히 이해해도 대부분의 블로킹/데드락을 설명할 수 있습니다.

공유 잠금 (Shared, S)

• SELECT 같은 읽기 작업에서 사용
• S ↔ S는 공존 가능, 하지만 S ↔ X는 충돌

배타 잠금 (Exclusive, X)

• INSERT/UPDATE/DELETE 같은 쓰기 작업에서 사용
• 다른 어떤 잠금과도 공존하지 못함(다른 세션은 접근 불가)

업데이트 잠금 (Update, U)

• “읽기처럼 보이지만, 이후 X로 바뀔 가능성”이 있는 작업에서 사용
• S 잠금끼리의 교착을 줄이기 위한 중간 단계 잠금 성격

의도 잠금 (Intent Locks, IS / IX / SIX)

상위 레벨 리소스에 “아래 레벨에 잠금이 걸릴 예정”임을 알리는 잠금입니다.

• 예) 특정 키(KEY) 에 X 잠금을 걸기 전에, 상위인 오브젝트/페이지 수준에 IX가 잡힘

잠금 호환성(compatibility) 규칙에 따라 “누가 누구를 기다리는지”가 결정되고, 이것이 결국 BLOCK과 DEADLOCK으로 이어집니다.

---

2. BLOCK vs DEADLOCK 정확히 구분하기

2-1. BLOCK(블로킹)

정의

한 세션이 특정 리소스에 잠금을 잡고 있는 동안, 다른 세션이 같은 리소스를 필요로 해서 기다리는 상태입니다.

• 대기 중인 세션은 wait_type이 LCK_M_S, LCK_M_X 등으로 표시됨
• sys.dm_exec_requests의 blocking_session_id 컬럼에 나를 막고 있는 세션 ID가 기록됨

특징

• 블로킹 자체는 정상적인 동시성 현상이기도 합니다(짧게 끝나면 문제 없음).
• 문제는 오랫동안 풀리지 않는 블로킹입니다. 흔한 원인은 아래와 같습니다.
• 긴 트랜잭션(트랜잭션 범위 과다)
• COMMIT/ROLLBACK이 늦거나 누락되는 애플리케이션
• 불필요하게 큰 범위를 잠그는 쿼리(풀 스캔/대량 업데이트 등)

간단한 예시

-- 세션 1
BEGIN TRAN;
UPDATE dbo.Orders
   SET Status = 'DONE'
 WHERE OrderId = 100;
-- COMMIT/ROLLBACK 없이 오래 대기 (의도적으로 예시)

-- 세션 2
UPDATE dbo.Orders
   SET Status = 'CANCEL'
 WHERE OrderId = 100;   -- 세션 1의 X 잠금 때문에 BLOCK 발생

세션 2는 세션 1이 COMMIT/ROLLBACK 할 때까지 LCK_M_X 등으로 기다리게 됩니다. 이게 BLOCK입니다.

---

2-2. DEADLOCK(데드락)

정의

두 개 이상의 세션이 서로가 가진 잠금을 기다리며 영원히 풀리지 않는 순환 대기(cycle) 상태입니다.

• A는 B가 가진 리소스를 기다리고
• B는 A가 가진 리소스를 기다리는 구조

간단한 예시

-- 세션 1
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE Id = 1;  -- A 계좌
-- (여기서 세션 2가 실행되었다고 가정)
UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE Id = 2;  -- B 계좌
COMMIT;

-- 세션 2
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance = Balance - 200 WHERE Id = 2;  -- B 계좌
UPDATE dbo.Accounts SET Balance = Balance + 200 WHERE Id = 1;  -- A 계좌
COMMIT;

• 세션 1: Id=1 → Id=2 순서로 잠금
• 세션 2: Id=2 → Id=1 순서로 잠금
  → 서로가 서로를 기다리다가 데드락이 발생합니다.

MSSQL의 동작

SQL Server는 데드락을 감지하면 관련 세션 중 하나를 victim(희생자) 으로 선택해 롤백시키고, 나머지 세션이 진행되게 합니다. 보통 아래와 같은 오류가 반환됩니다.

• Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

정리하면:

• Block: 한쪽이 잠금을 잡고 다른 쪽이 기다리는 상태(대개 “기다리면 풀림”)
• Deadlock: 서로가 서로를 기다리는 상태(“기다려도 안 풀림” → SQL Server가 강제 종료로 해소)

[IMG04] BLOCK vs DEADLOCK diagram (one-way wait vs cyclic wait)

---

3. 현재 서버에서 BLOCK / DEADLOCK 찾는 방법

3-1. DMV로 현재 블로킹 세션 찾기

“지금 누가 누구를 막고 있지?”를 확인할 때 가장 빠르게 사용하는 DMV는 sys.dm_exec_requests입니다.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS sql_text,
    DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;  -- 누군가에 의해 막힌 세션만

여기서 특히 중요한 컬럼은 다음입니다.

• session_id: 막힌 세션 ID
• blocking_session_id: 이 세션을 막고 있는 세션 ID
• wait_type: 어떤 종류의 대기인지 (LCK_M_IX, LCK_M_S 등)
• wait_resource: 어떤 리소스를 두고 경쟁하는지(KEY/OBJECT/PAGE 등)
• sql_text: 막혀서 기다리고 있는 쿼리(대기자 쿼리)

참고: “블로커(막는 쪽)”가 현재 실행 중이 아닌 상태(sleeping)여도 잠금을 잡고 있을 수 있습니다. 이 경우 sys.dm_exec_requests만으로 블로커 SQL이 비어 보일 수 있으니, 필요 시 sys.dm_exec_sessions, sys.dm_tran_* DMV까지 확장해서 확인합니다.

“최상위 블로커(남을 막지만, 본인은 안 막힌 세션)”를 빠르게 찾는 간단한 접근 예시:

;WITH BlockingTree AS (
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_resource
    FROM sys.dm_exec_requests r
)
SELECT DISTINCT bt.blocking_session_id AS root_blocker
FROM BlockingTree bt
LEFT JOIN BlockingTree bt2
  ON bt.blocking_session_id = bt2.session_id
WHERE bt.blocking_session_id <> 0
  AND bt2.session_id IS NULL;  -- 자기 위로는 안 막힌 세션

---

3-2. Deadlock 로그 / Extended Events

데드락은 보통 아래에서 확인합니다.

• SQL Server Error Log: 기본적인 Deadlock 정보(환경에 따라 제한적일 수 있음)
• Extended Events: system_health의 xml_deadlock_report 등
• (구버전/특정 구성) Trace Flag 1204, 1222 등으로 출력

Deadlock 그래프 XML에는 다음과 같은 정보가 들어 있습니다.

• 각 세션의 실행 SQL(inputbuf)
• Owner/Waiter 리스트
• 각 리소스에 대한 waitresource
• victim으로 선택된 세션

즉, 데드락도 결국 wait_resource(또는 XML의 waitresource)를 통해 어떤 KEY/OBJECT/PAGE에서 충돌했는지 확인하게 됩니다.

---

4. wait_resource로 원인 추적하기(핵심)

sys.dm_exec_requests, sys.dm_os_waiting_tasks, Deadlock XML 등에서 비슷한 패턴으로 확인할 수 있습니다.

4-1. wait_resource 기본 형태

대표적인 패턴은 아래와 같습니다.

• KEY: KEY: dbid:hobt_id (hash)
• OBJECT: OBJECT: dbid:object_id
• PAGE: PAGE: dbid:file_id:page_id
• (참고) RID: RID: dbid:file_id:page_id:slot_id

예시:

• KEY: 5:72057594044579840 (d8494abf8d90)
• OBJECT: 5:123456789
• PAGE: 5:1:12345

---

4-2. KEY: 인덱스/테이블까지 추적하기

예를 들어 KEY: 5:72057594044579840 를 봤다고 가정합니다.

• 5 : database_id
• 72057594044579840 : hobt_id (Heap or B-Tree ID)

이 hobt_id로 어떤 테이블/인덱스인지 추적할 수 있습니다.

DECLARE @hobt_id BIGINT = 72057594044579840;

SELECT
    DB_NAME()                AS db_name,
    p.object_id,
    OBJECT_NAME(p.object_id) AS object_name,
    i.index_id,
    i.name                   AS index_name
FROM sys.partitions p
JOIN sys.indexes i
  ON p.object_id = i.object_id
 AND p.index_id  = i.index_id
WHERE p.hobt_id = @hobt_id;

결과로 아래를 바로 확인할 수 있습니다.

• object_name: 어느 테이블인지
• index_name: 어느 인덱스인지

---

4-3. OBJECT: 테이블 바로 찾기

OBJECT: 5:123456789 형태는 비교적 단순합니다.

• 5: database_id
• 123456789: object_id

해당 DB에서 아래처럼 확인하면 됩니다.

DECLARE @object_id INT = 123456789;

SELECT
    OBJECT_SCHEMA_NAME(@object_id) AS schema_name,
    OBJECT_NAME(@object_id)        AS object_name,
    @object_id                     AS object_id;

또는 더 상세히 보려면:

DECLARE @object_id INT = 123456789;

SELECT
    s.name     AS schema_name,
    o.name     AS object_name,
    o.type_desc
FROM sys.objects o
JOIN sys.schemas s
  ON o.schema_id = s.schema_id
WHERE o.object_id = @object_id;

---

4-4. PAGE: 어느 테이블의 페이지인지 찾기(조금 고급)

PAGE: 5:1:12345 같은 형태는:

• 5: database_id
• 1: file_id
• 12345: page_id

페이지가 속한 오브젝트를 찾는 방법 중 하나는 sys.dm_db_database_page_allocations를 활용하는 것입니다(SQL Server 2012+).

참고: DETAILED 모드는 환경에 따라 비용이 있을 수 있으니, 운영 중에는 주의해서 사용합니다.

SELECT 
    alloc.object_id,
    OBJECT_NAME(alloc.object_id) AS object_name,
    alloc.index_id,
    i.name                       AS index_name,
    alloc.allocated_page_file_id,
    alloc.allocated_page_page_id
FROM sys.dm_db_database_page_allocations(
        DB_ID(), NULL, NULL, NULL, 'DETAILED'
     ) AS alloc
LEFT JOIN sys.indexes i
    ON alloc.object_id = i.object_id
   AND alloc.index_id  = i.index_id
WHERE alloc.allocated_page_file_id = 1
  AND alloc.allocated_page_page_id = 12345;

---

4-5. DMV에서 wait_resource + SQL까지 한 번에 보기

“지금 어떤 세션이 어떤 리소스를 기다리는지”와 “어떤 SQL을 실행 중인지”를 한 번에 보는 예시입니다.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_resource,
    r.wait_time,
    DB_NAME(r.database_id) AS database_name,
    t.text                 AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
  AND (r.blocking_session_id <> 0 OR r.wait_type LIKE 'LCK_M_%')
ORDER BY r.wait_time DESC;

---

5. BLOCK / DEADLOCK 줄이기 위한 튜닝 포인트

• 트랜잭션 범위를 최소화
• 꼭 필요한 구간만 BEGIN TRAN ~ COMMIT로 묶기
• UI 대기/사용자 입력/외부 API 호출을 트랜잭션 안에 넣지 않기
• 일관된 접근 순서
• 여러 테이블/리소스를 순차적으로 잠그는 작업이라면
  모든 쿼리가 같은 순서로 접근하게 설계(데드락 크게 감소)
• 적절한 인덱스 설계
• 인덱스 부재로 대량 풀 스캔이 발생하면 잠금 범위가 커짐
• wait_resource로 추적한 테이블/인덱스를 중심으로 실행 계획 점검
• 긴 쿼리 튜닝
• 오래 걸리는 쿼리 = 긴 시간 동안 잠금 유지
• 특히 UPDATE/DELETE는 실행 시간이 곧 잠금 지속시간이므로 우선 튜닝
• 격리 수준(Isolation Level) 고려
• 예: 읽기에서 READ COMMITTED SNAPSHOT 활용 시
  읽기-쓰기 블로킹이 줄어드는 경우가 있음
• 다만 트랜잭션 동작 방식이 바뀌므로 충분한 테스트 필요

---

정리

• 잠금 모드(S/X/U/Intent)를 이해해야 “왜 블로킹이 생겼는지”, “어떤 패턴에서 데드락이 생기는지”가 보입니다.
• Block은 “한쪽이 잠금을 잡고 다른 쪽이 기다리는 상태(대개 기다리면 풀림)”이고, 장기화되면 장애 원인이 됩니다.
• Deadlock은 “서로가 서로를 기다리는 순환 대기 상태”라 SQL Server가 victim을 강제로 롤백시켜 해소합니다.
• wait_resource의 KEY/OBJECT/PAGE 패턴을 해석하고, hobt_id/object_id/page allocation으로 추적하면 문제 테이블/인덱스까지 빠르게 좁힐 수 있습니다.

---