[MSSQL] Lock 걸린 쿼리 확인 및 해결방안

SSMS에서 쿼리 실행 시 TimeOut으로 Connection이 끊어지는 경우가 종종 있는데요.
실행되는 쿼리로 인하여 테이블에 Lock이 걸려서 처리속도가 지연되는 게 원인일 수 있습니다.

아래의 방법으로 Lock 걸린 쿼리 확인 후 강제 종료를 하여 문제를 해결할 수 있습니다.

1. SP_LOCK 권한 부여
내장함수 SP_LOCK 명령어를 사용하기 위해선 VIEW SERVER STATE 실행 권한이 있어야 합니다.

실행 권한이 없으면 아래와 같이 오류 메시지가 발생합니다.
'사용자에게 이 동작을 수행할 권한이 없습니다.' 

권한 부여를 위한 명령어는 다음과 같습니다.

GRANT ALTER SERVER STATE TO '권한을 줄 계정이름'

2. SP_LOCK 실행

: 현재 실행 중인 SPID(프로세스 세션 ID)의 잠금모드에 대한 정보를 확인할 수 있습니다.

잠금모드(MODE) 정보는 다음과 같습니다.

S = 공유. 보유 중인 세션이 리소스에 공유된 액세스를 할 수 있도록 권한을 부여합니다.
U = 업데이트. 업데이트될 리소스에 대해 업데이트 잠금을 획득하도록 합니다. 
X = 배타. 보유 중인 세션이 리소스에 배타적으로 액세스 할 수 있도록 권한을 부여합니다.
IS = 내재된 공유. 잠금 계층 구조의 일부 하위 리소스에 S 잠금을 설정하려는 의도를 표시합니다.
IU = 의도 업데이트. 잠금 계층 구조의 일부 하위 리소스에 U 잠금을 설정하려는 의도를 표시합니다.
IX = 의도 배타. 잠금 계층 구조의 일부 하위 리소스에 X 잠금을 설정하려는 의도를 표시합니다.

EXEC SP_LOCK

명령어 실행 후 아래와 같이 배타적 잠금(Mode : X)이 반복되는 SPID는 LOCK이 걸려 실행이 지연되고 있는

쿼리라고 의심하시면 됩니다.

3. SPID 실행 스크립트 확인

: DBCC INPUTBUFFER 명령어로 LOCK이 걸린 것으로 의심되는 SPID의 실행 스크립트를 확인합니다.

  쿼리를 확인하여 강제 종료해도 시스템에 문제가 없는지 검토합니다.

DBCC INPUTBUFFER ( [SPID] )
DBCC INPUTBUFFER (112)

5. Kill 명령어 실행.

: LOCK을 유발하는 SPID를 강제 종료시킵니다.

KILL [SPID]
KILL 112

 

6. 방지대책

트랜잭션이 없는 데이터를 조회할 때 아래의 명령어를 쿼리에 추가해주면 오류를 방지할 수 있습니다.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

읽어주셔서 감사합니다.