[MSSQL] LOCK 조회 및 쿼리 확인(+ 조치방법)

요즘들어 사내 ERP에서 LOCK 걸리는 경우가 발생하고있다...

정리하는 겸 작성한 글.

 

대부분 검색하시는 분들은 LOCK이 무엇인지 알지만 쿼리가 기억이 안나는 경우(...귀찮아서...?)라고 생각해서

글 순서는 아래와 같이 진행된다.

1. LOCK 조회

2. LOCK 쿼리 조회(+ kill, timeout)

3. LOCK 강제종료(+ timeout)

4. 조치 방법

1. LOCK 조회

LOCK 조회 관련한 쿼리는 아래와 같이 2개정도 사용한다.

sp_lock

(또는 EXEC sp_lock)

  : Mode가 X인 경우 Lock

sp_lock 실행 결과

 

SELECT * FROM SYS.sysprocesses WHERE blocked > 0

  : LOCK 상태인 경우 결과 출력(반대로 없는경우 아래와 같이 아무것도 안나온다.)

SELECT * FROM SYS.sysprocesses WHERE blocked > 0 실행 결과 컬럼 physical_io 이하 생략

 

sp_who2 [spid]

(또는 sp_who)

 : spid, Status, cputime ... 등 여러 정보를 볼 수 있다.

  LOCK 세션 확인을 위해서는 BlkBy에 .이 아닌 데이터가 들어있는지?, Status가 SUSPENDED로 수상한 세션인지 확인하면 된다.

LOCK이 걸린 경우 BlkBy에는 현재 세션과 다른 SPID가 들어가있는데 이 뜻은 "BlkBy에 있는 SPID를 가진 세션 때문에 지연되고 있다" 라고 생각하면 된다.

나는 처음에 sp_who2를 먼저 확인하고 혹시 모르니 나머지도 실행해보고 생각하는 편이다.

 

2. 로 넘어가기 전에 꼭 수상한 spid를 기억해두자.

 

2. LOCK 쿼리 조회

DBCC INPUTBUFFER([spid])

  : [spid]를 가진 세션의 실행중인 쿼리를 확인 할 수 있다.

자세한 쿼리는 EventInfo 칼럼에서 확인 가능하다.

 

DBCC INPUTBUFFER(51) 실행 결과

 

 

 

3. LOCK 강제종료(+ timeout)

kill [spid]

 : spid을 가진 세션을 강제 종료 처리

 

 

 

4. 조치방법

1 ~ 3번 진행 후 문제가 되는 쿼리까지 확인 완료하였다면,

혹시라도 트랜잭션 도중 문제가 발생했는지 확인하고 시간이 오래걸리는 경우.

영향을 받는 쿼리와 LOCK을 유발하는 쿼리 첫행에 Timeout을 걸어서 프로그램이 멈추지 않도록 조치를 취하자.

SET LOCK_TIMEOUT 50000

50000ms(50000ms = 50s)까지 시도 후 안되면 TIMEOUT 하여 쿼리 종료

(어찌되었든 TIMEOUT은 서버 자원 관련 관리겸 가능하면 꼭 사용해주자.)