사내 ERP나 물류 시스템을 운영하다 보면 꼭 한 달에 한두 번씩은 누군가 대량 업데이트를 걸어놓고 트랜잭션을 안 닫거나, 누군가의 PC가 응답 없음으로 뻗으면서 테이블에 락(Lock)을 물고 안 놔주는 상황이 터진다.

이럴 때 다른 사용자들 화면까지 줄줄이 타임아웃 나기 시작하면 마음이 급해져서 평소 쓰던 모니터링 쿼리가 기억이 안 날 때가 많다. 급할 때 바로 복사해서 쓰려고 자주 쓰는 블로킹 추적 순서만 간단히 남겨둔다.

1. 급한 대로 돌리는 sp_who2 (근데 좀 불편함)

제일 만만하게 치는 게 기본 저장 프로시저인 EXEC sp_who2인데, 세션이 수백 개씩 붙어 있는 운영 DB에서는 결과를 한눈에 보기가 너무 힘들다.

1

EXEC sp_who2;

결과창에서 BlkBy 컬럼에 숫자가 찍혀 있는 행을 찾으면 된다. 예를 들어 SPID 78번 행의 BlkBy에 54라고 적혀 있으면, 54번 세션이 78번 세션을 막고 있다는 뜻이다.

문제는 54번이 또 다른 세션(예: 61번)한테 막혀 있는 다중 블로킹 상황일 때는 눈으로 따라가기가 헷갈리고, 결정적으로 저 세션이 무슨 쿼리를 돌리고 있는지 바로 안 보인다는 점이다.

2. 블로킹 유발 세션 + 실행 중인 쿼리 한 번에 뽑기

그래서 실무에서는 sp_who2 치고 또 DBCC INPUTBUFFER를 치는 대신, 아예 아래 DMV 조합 쿼리를 스니펫으로 저장해 두고 바로 돌린다. 현재 누군가를 막고 있거나 막혀서 대기 중인 세션만 딱 추려서 보여준다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT 
    r.session_id AS [대기중인_SPID],
    r.blocking_session_id AS [막고있는_범인_SPID],
    s.host_name AS [접속PC명],
    s.program_name AS [프로그램명],
    r.wait_type AS [대기유형],
    r.wait_time / 1000.0 AS [대기시간(초)],
    SUBSTRING(t.text, (r.statement_start_offset/2) + 1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE r.statement_end_offset
        END - r.statement_start_offset)/2) + 1) AS [현재_실행중인_쿼리],
    t.text AS [전체_배치_쿼리]
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

이걸 돌리면 [접속PC명]에 현업 담당자 PC 이름이나 개발자 PC 이름이 바로 찍히고, WaitType에 LCK_M_X(배타적 잠금 대기)나 LCK_M_S(공유 잠금 대기) 같은 상태가 몇 초째 걸려 있는지 한눈에 나온다.

3. 정작 범인 세션(Root Blocker)이 놀고 있을 때 (sleeping 상태)

가끔 위 쿼리를 돌렸을 때 [막고있는_범인_SPID]가 예를 들어 54번으로 나왔는데, 정작 54번 자신은 현재 실행 중인 쿼리(sys.dm_exec_requests)에 안 잡힐 때가 있다.

이건 100% SSMS에서 누군가 BEGIN TRAN을 열어놓고 UPDATE나 DELETE만 날린 뒤, COMMIT이나 ROLLBACK을 안 치고 그냥 퇴근했거나 딴짓하고 있는(세션 상태가 sleeping인) 경우다.

이럴 땐 범인 SPID(예: 54)를 아래 쿼리에 넣어서 마지막으로 날린 쿼리가 뭔지 확인한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 54번 세션이 마지막으로 실행한 쿼리와 열린 트랜잭션 확인
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.open_transaction_count AS [열린트랜잭션수],
    t.text AS [마지막_실행_쿼리]
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id = 54; -- 여기에 범인 SPID 입력

open_transaction_count가 1 이상인데 상태가 sleeping이면 볼 것도 없이 트랜잭션 안 닫은 세션이다. 보통 TB_STOCK_MST나 TB_CLOSING_HIST 같은 마감 테이블에 수동으로 데이터 보정하다가 자주 이렇다.

4. 세션 강제 종료 (KILL) 및 주의점

담당자 확인해서 처리가 안 되는 상황이면 바로 세션을 날려준다.

1

KILL 54;

단, KILL을 날릴 때 하나 조심할 게 있다. 만약 저 54번 세션이 지난 20분 동안 천만 건짜리 UPDATE를 돌리던 중이었다면, KILL을 치는 순간부터 지금까지 바꾼 데이터를 다시 원래대로 되돌리는 롤백(Rollback) 작업이 시작된다.

배보다 배꼽이 더 크다고, 롤백하느라 오히려 디스크 I/O가 100%를 치면서 30분 동안 테이블이 아예 안 풀릴 수도 있다. 죽이기 전에 꼭 대기 시간과 쿼리 내용을 먼저 보고, 만약 이미 KILL을 날렸다면 아래 구문으로 롤백이 몇 퍼센트 진행됐는지 모니터링하면서 기다리는 수밖에 없다.

1

KILL 54 WITH STATUSONLY;

평소에 조회용 프로시저를 짤 때 더티 리드(Dirty Read)가 크게 문제 안 되는 단순 현황판 화면이라면 테이블 뒤에 WITH (NOLOCK) 힌트를 적절히 붙여두는 것도 이런 연쇄 먹통을 막는 현실적인 타협점이다. 물론 정산이나 재고 수량 맞추는 쿼리에 NOLOCK을 쓰면 안 되겠지만.