UPDATE 트랜잭션에 의한 테이블 잠금 확인 및 조치

SQL Server 환경에서 가끔 SELECT 문을 수행하였음에도 결과가 반환되지 않고 오랜 시간 쿼리가 수행되는 현상이 발생할 때가 있습니다. 대부분 DML 활성 트랜잭션이 원인이 될 때가 많았습니다만 이를 확인하고 해결하는 방법에 대해서 간략히 정리하였습니다.


[환경]
SQL Server 2008 SP1


[현상]
아래와 같이 데이터 조회를 위해 SELECT 하였으나 조회되지 않고 '쿼리를 실행하는 중' 상태로 유지됩니다.
SELECT UserID FROM TblUser WHERE UserCode = '03'


[원인]
UPDATE 트랜잭션에 의해 테이블이 잠겨 있으므로 트랜잭션이 완료될 때까지 SELECT 쿼리를 완료할 수 없습니다. 기본값으로 Dirty Read 를 허용하지 않기 때문입니다.


[Action Plan]
트랜잭션이 완료되지 않고 sleeping 상태로 대기하고 있는 상태이므로 강제 종료합니다. UPDATE 트랜잭션을 발생한 쿼리를 찾아 원인 분석을 진행합니다. 

KILL 52

[분석결과]
1. 새 쿼리를 실행하여 현재 실행 중인 프로세스에 대한 정보를 확인합니다.

SELECT * FROM sys.sysprocesses WHERE spid = 53

/* 결과

spid   kpid   blocked       
-----------------------------
53     4696   52     

SPID '52' 에 의해서 요청이 차단되었음을 확인할 수 있습니다.
*/

2. 쿼리 확인
마지막으로 전송된 쿼리를 확인합니다.

DBCC INPUTBUFFER(52)

/* 결과
EventType      Parameters EventInfo
-------------------------------------------------------------------------------------------------
Language Event 0         

BEGIN TRAN
 UPDATE TblUser SET UserID = 'Bobby' WHERE UserCode = '03'

(1 row(s) affected)
*/

3. 가장 오래된 활성 트랜잭션 찾기

DBCC OPENTRAN

/* 결과
데이터베이스 'SupernaturalDB'의 트랜잭션 정보입니다.
가장 오래 전에 활성화된 트랜잭션:
    SPID(서버 프로세스 ID): 52
    UID(사용자 ID) : -1
    이름          : user_transaction
    LSN           : (22:174:1)
    시작 시간    : 10 16 2009 11:19:20:667AM
    SID           : 0x010500000000000515000000eeced709a49479f270770942e8030000
*/

4. UPDATE 트랜잭션이 Active 상태인가?

sp_who2 active

/*
UPDATE 트랜잭션이 Active 상태가 아닌 sleeping 상태
*/

5. 잠금 정보 확인

SELECT * FROM sys.dm_tran_locks

[참고자료]
DBCC OPENTRAN (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms182792.aspx


작성자 : Lai Go/ 작성일자 : 2009.10.16