Blocking이 발생하면 Application의 실행 속도가 현격히 저하되고, 심한 경우에는 클라이언트 입장에서 Hangup이 걸리는 경우가 있다. 즉, 한 사용자가 계속 자원을 사용하기 때문에 다른 사용자들이 무한정이 Waiting 상태가 되는 것이다. 이런 경우 다음과 같이 어떤 프로세스가 Blocking 을 하고 있는지 알아낼 수 있다.
SQL쿼리분석기에서 sp_who / sp_lock 프로시져를 통해서 어떤 프로세스에서 lock 상태인지 확인이 가능합니다. 물론 EM에서도 "잠금" 에서 확인이 가능하며 해당 프로세스를 종료시킬수도 있습니다.
[명령] Sp_who
[결과] Spid status giname hostname blk dbname 10 sleeping sale1
0 SALEDB 12 sleeping sale1
10 SALEDB 13 sleeping sale1
12 SALEDB
[분석] 위의 경우는 spid 10번에 해당하는 유저가 12번,13번에 대해 Blocking을 걸고 있는 경우이다. 일시적으로 이러한 Blocking현상은 발생할 수 있지만 만약 한 유저가 수십명에 대해 Blocking을 잡고 있으면, 클라이언트는 Hangup이 된 것처럼 느끼게 될 것이다.
이런 경우 해당 유저가 사용하는 프로그램을 검토해 볼 필요가 있으며, Locking을 줄이며 트랜잭션의 길이가 짧도록 수정할 필요가 있다. 만약 위의 경우 10번 프로세스를 죽이기 위해서는 다음 명령을 사용할 수 있다 : KILL 10 또한 Sp_lock을 사용하면 어떤 Table, page에 대해 어떤 Locking이 걸려 있는지 찾아볼 수 있다.
Dead Lock의 경우는 SQL Server에서 자동으로 하나의 프로세스를 Kill함으로 인해 Dead lock을 풀어주게 된다. Kill되는 프로세스를 Victim이라고 하며 이것은 Log에 기록되게 된다. Dead Lock이 발견된 경우는 해당 프로그램을 즉시 수정할 필요가 있다.
모니터링은 Windows 퍼포먼스 모니터나, SNMP를 이용하는 툴에서 하는게 좋지 않을까..합니다. 퍼포먼스 모니터등은 순간적으로 lock을 걸린후 트랜잭션 종료후 lock이 풀리기 때문에 퍼포먼스 모니터에서는 계속해서 값이 체크되니 혼동하지 마세요..
lock이나, blocking 해결은 처음부터 lock이나 blocking이 발생하지 않도록 쿼리점검을 확실하게 하는거와 더불어 트랜잭션을 최대한 짧게 설정하는것이지만, 일단 발생한 것에 대해서는 해당 프로세스를 죽이거나, 다음번 lock 발생시 시간제한을 통해서 종료 하는 설정을 해주는 것도 좋다.
SELECT @@LOCK_TIMEOUT
결과값이 -1 은 default값이며 제한(무기한 대기)이 없다.
SET LOCK_TIMEOUT 1800 (밀리초 이므로 3분)
이 설정은 해당 트랜잭션을 종료시키지 않고, 오류메시지를 반환하므로 응용프로그램에서 이오류에 대한 예외처리를 꼭 해줘야 합니다.
출처 : 데브피아
댓글 없음:
댓글 쓰기