DB Lock

MS-SQL을 사용하다 보면 여러가지 이유로 Lock이 걸려서 더 이상 작업이 안되는 경우 가 있습니다.
이 경우 아래의 방법으로 확인하고 해결할 수 있습니다.


1.  sp_lock 실행
  Lock 내용 확인. Mode가 X 인 것들을 확인하고, spid 를 기억해 둔다.
  보통 spid 가 여러개 중복되어 출력되는 경우가 많다.
  예) exec sp_lock

2. dbcc inputbuffer(spid)
  클라이언트에서 MSSQL로 보낸 최종 명령문을 표시한다.
  1번에서 Mode 가 X 인 것의 spid를 입력하면 해당 내용이 출력된다.
  예) dbcc iniputbuffer(60)
 
3. sp_who spid
  1번에서 확인된 spid 를 입력하면 관련 정보를 표시해 준다.
  예) exec sp_who 60
 
4. kill spid
  Lock을 확인했으면 해당 spid 를 입력하여 해당 프로세스를 끝낸다.
  예) kill 60



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

정리하는 겸 작성한 글.
**********************************************************************************
 MSSQL LOCK 해결하는 방법

1. LOCK 확인하는 방법

테이블이 잠겼는지 확인하는 방법은 다음과 같은 방법으로 확인할 수 있다.

 


EXEC sp_lock 


Mode 컬럼 값이 X 이면 LOCKDL 발생한 프로세스이다.

 


EXEC sp_who2


BlkBy 컬럼이 . 이 아니면 LOCK이 발생한 프로세스이다.

 


SELECT * FROM SYS.sysprocesses WHERE blocked > 0


block 컬럼이 0 이상이면 LOCK이 발생한 프로세스이다.

 

2. 마지막으로 실행한 쿼리 조회하는 방법

 


DBCC inputbuffer ([SPID])


 

3. LOCK이 발생한 SPID를 강제 KILL(종료) 하는 방법 

 


KILL [SPID]

**********************************************************************************


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은 서버 자원 관련 관리겸 가능하면 꼭 사용해주자.)

 
출처: https://gamedevst.tistory.com/14 [프로그래밍.... + 일상?:티스토리]