SP_LOCK, KILL - 락 확인 및 강제종료

● SP_LOCK - 락 걸린 쿼리 확인 및 강제종료

​

MS-SQL에서 SELECT 실행 시 조회 결과가 나오지 않거나 타임아웃에 걸려 커넥션이 끊어지는 경우가 있습니다. 테이블에 락이 걸려서 이런 현상이 발생하곤 합니다. MS-SQL의 경우 여러 사용자 중 한명이 락이 걸린 경우 역시 조회가 되지 않습니다. 락 걸린 쿼리를 조회하여 강제로 kill하는 방법에 대해 알아보겠습니다.

락으로 의심되는 SPID 조회하기

락으로 의심되는 SPID를 조회하는 방법은 다음과 같이 3가지 방법이 있습니다.

​

1. SP_LOCK 사용하기

SQL Server에서는 락(Lock)을 확인할 수 있도록 내장 함수 SP_LOCK을 제공하고 있습니다. 사용 방법은 다음과 같습니다.

EXEC SP_LOCK

실행 결과에서 Mode 컬럼의 값이 X인 경우 락이 걸린 것인데 이때 해당 SPID를 확인합니다.

​

2. SP_WHO2 사용하기

또 다른 MS-SQL 내장 함수로 SP_WHO2를 제공하고 있습니다. 사용 방법은 다음과 같습니다.

EXEC SP_WHO2

실행 결과에서 BlkBy 컬럼에 값이 존재한다면 그것은 락(Lock)걸린 프로세스의 ID입니다.

​

3. 시스템 테이블 사용하기

다음과 같이 시스템 테이블을 이용해 확인하는 방법도 있습니다. 아래 쿼리를 실행하면 락(Lock)걸린 세션에 대한 정보만 출력됩니다.

 

SELECT   P.*

FROM     MASTER..SYSPROCESSES P 

WHERE    ( 

                  STATUS LIKE 'RUN%' 

         OR       WAITTIME > 0 

         OR       BLOCKED <> 0 

         OR       OPEN_TRAN <> 0 

         OR       EXISTS 

                  ( 

                         SELECT * 

                         FROM   MASTER..SYSPROCESSES P1 

                         WHERE  P.SPID = P1.BLOCKED 

                         AND    P1.SPID <> P1.BLOCKED ) ) 

AND      SPID > 50 

AND      SPID <> @@SPID 

ORDER BY 

         CASE 

                  WHEN STATUS LIKE 'RUN%' THEN 0 

                  ELSE 1 

         END , 

         WAITTIME DESC , 

         OPEN_TRAN DESC

 

LOCK 걸린 SPID 기준으로 실행되고 있는 쿼리 출력

SQL Server의 내장 함수를 이용해 락걸린 SPID 기준으로 현재 실행되고 있는 쿼리를 확인할 수 있습니다. 쿼리 내용을 확인하는 구문은 다음과 같습니다. 락으로 의심되는 SPID를 인자 값으로 넣어서 실행하면 됩니다.

DBCC INPUTBUFFER( [SPID] )

SPID를 이용하여 KILL하기

MS-SQL에서는 SPID를 기준으로 세션을 종료시킬 수 있습니다. 명령어는 다음과 같습니다.

KILL ( [SPID] )

​

[출처] [MS-SQL] SP_LOCK, KILL - 락 확인 및 강제종료|작성자 seek