DONGBUM KIM

Beat Yesterday

2 분 소요

MSSQL에서 의도치 않게 락이 걸리는 상황이 발생했다. 에러가 나는 프로시저를 찾아보니 쿼리에 WITH(UPDLOCK) 힌트가 붙어 있었다.

2026년 수정 안내 이 글에서 진단 도구로 소개한 sp_lock은 deprecated되어 현재는 쓰지 않는다. “Mode가 X인 것이 락이 걸린 것”이라는 설명도 부정확했다. 현재 쓰는 DMV 기반 방법으로 바꾸고, UPDLOCK 힌트에 대한 설명도 보강한다.

UPDLOCK은 무엇을 하는가

UPDLOCK은 읽는 시점에 갱신 락(U 락)을 걸어두는 힌트다. 나중에 이 행을 UPDATE할 것임을 미리 알리는 것이다.

용도는 하나다. 읽고 나서 그 값을 근거로 쓰는(read-then-write) 패턴에서 경합을 막는 것.

BEGIN TRAN

-- UPDLOCK이 없으면, 두 세션이 동시에 같은 값을 읽고
-- 둘 다 "재화가 충분하다"고 판단해버린다
SELECT @money = money
FROM   dbo.UserWallet WITH (UPDLOCK)
WHERE  user_id = @user_id

IF @money >= @price
    UPDATE dbo.UserWallet SET money = money - @price WHERE user_id = @user_id

COMMIT TRAN

UPDLOCK 없이 공유 락(S)만 걸고 읽으면, 두 세션이 동시에 읽은 뒤 각자 UPDATE로 승격하려 하면서 교착 상태(deadlock) 가 되기 쉽다. U 락은 서로 호환되지 않으므로 한쪽이 먼저 기다리게 되어 데드락 대신 단순 대기가 된다.

즉 UPDLOCK은 데드락을 줄이기 위해 쓰는 힌트다. 대신 직렬화되므로 처리량은 떨어진다.

인덱스가 없으면 테이블 전체가 잠긴다

WHERE 절에 인덱스가 없으면 SQL Server는 테이블 전체를 스캔하면서 지나가는 모든 행에 U 락을 건다. 원하는 한 행만 잠그려던 것이 테이블 전체 잠금이 되어버린다.

그래서 UPDLOCK을 쓰는 쿼리의 WHERE 조건 컬럼에는 반드시 인덱스가 있어야 한다. 처음 이 글을 쓸 때 인덱스를 추가해 해결했던 것이 이 이유였다.

갱신 대상 테이블 자체에 WITH(UPDLOCK)을 붙이는 것은 의미가 없다. UPDATE는 어차피 그 테이블에 U 락을 거쳐 X 락으로 승격하기 때문이다. UPDLOCK이 의미를 갖는 자리는 SELECT, 그리고 UPDATE ... FROM처럼 조인으로 함께 읽기만 하는 다른 테이블에 붙일 때다 — 그 테이블은 자동으로 X 락까지 승격되지 않으므로, 나중에 갱신할 계획이라면 명시적으로 UPDLOCK을 걸어 경합을 막아야 한다.

차단된 세션 찾기

sp_lock은 SQL Server 2008에서 deprecated 목록에 올랐고 출력도 읽기 어렵다. (대체재인 sys.dm_tran_locks는 DMV 프레임워크가 도입된 SQL Server 2005부터 있었다 — sp_lock이 deprecated된 시점과 혼동하지 않도록 주의.) 지금은 DMV를 쓴다.

1. 누가 누구를 막고 있는지 확인

SELECT  r.session_id
,       r.blocking_session_id
,       r.wait_type
,       r.wait_time
,       r.wait_resource
,       t.text AS running_query
FROM    sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text( r.sql_handle ) AS t
WHERE   r.blocking_session_id <> 0

blocking_session_id가 차단하고 있는 세션의 ID다. 이 값을 따라 올라가면 최초 원인 세션을 찾을 수 있다.

2. 그 세션이 무엇을 하고 있는지 확인

DBCC INPUTBUFFER( 차단하는_세션ID )

또는 다음이 더 자세하다.

SELECT  s.session_id
,       s.login_name
,       s.host_name
,       s.program_name
,       s.status
,       s.last_request_start_time
,       t.text
FROM    sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text( c.most_recent_sql_handle ) AS t
WHERE   s.session_id = 차단하는_세션ID

3. 어떤 락이 걸려 있는지 확인 (sp_lock 대체)

SELECT  l.request_session_id
,       l.resource_type
,       l.request_mode
,       l.request_status
,       OBJECT_NAME( p.object_id ) AS object_name
FROM    sys.dm_tran_locks AS l
LEFT JOIN sys.partitions AS p ON p.hobt_id = l.resource_associated_entity_id
WHERE   l.request_status = 'WAIT'

여기서 request_mode가 X인 것만 락인 것이 아니다. S(공유), U(갱신), IX(의도 배타) 모두 락이며, 락이 존재한다는 것 자체는 정상이다. 문제가 되는 것은 request_status가 WAIT인 것, 즉 얻지 못하고 기다리는 락이다.

KILL은 마지막 수단이다

KILL 세션ID

세션을 강제 종료하면 그 세션의 트랜잭션이 롤백된다. 롤백에 걸리는 시간이 원래 작업 시간보다 길 수도 있다. 진행 상황은 이렇게 확인한다.

KILL 세션ID WITH STATUSONLY

운영 중이라면 KILL 전에 먼저 확인할 것이 있다.

  • 그 세션이 정말 멈춘 것인가, 아니면 오래 걸리는 정상 작업인가
  • 롤백 시 데이터가 어떻게 되는가
  • 애플리케이션이 재시도하도록 되어 있는가

근본 해결은 KILL이 아니라 쿼리와 인덱스를 고치는 것이다. 같은 상황이 반복된다면 확장 이벤트(Extended Events)로 블로킹을 기록해두고 원인을 찾는 편이 낫다.

참고자료

댓글 남기기

참고