[MSSQL] Lock관련

데이터베이스(DB)를 운영하다 보면 가끔 이유 없이 조회 결과가 나오지 않거나, 타임아웃(TimeOut)에 걸려서 커넥션(Connection)이 끊어지는 경우가 종종 있습니다. 메모리나 CPU가 비정상적으로 높은 수치를 유지하고 있어서 DBMS가 정상 작동하지 않는다거나 하는 경우의 증상이 그러하며, 테이블 락(Table Lock)이 발생한게 그 원인이 되기도 합니다.
 

-- Lock 걸린 SPID 검색 (Mode 부분이 X, IX로 표시된 항목은 Lock)
-- Mode값 상세설명
-- - S(Shared Lock)공유잠금 : SELECT문때 데이터를 동시에 읽을 때 걸리는 형태, 동시에 SELECT가능
-- - U(Update Lock)업데이트잠금 : 업데이트 할 수 있는 리소스에 사용, 여러 사용자가 업데이트 할 때 발생하는 교착 상태 방지
-- - X(Exclusive Lock)배타잠금 : 데이터의 INSERT / UPDATE / DELETE시에 사용, 동시에 불가능 호환 불가, 동시에 같은행 변경 불가
-- - IS - 의도공유
-- - IU - 의도 업데이트
-- - IX - 독점의도
-- - BU(Bulk Update Lock)대량 업데이트 잠금 : 데이터를 테이블로 대량 복사 하는 경우와 TABLOCK 힌트가 지정된 경우 사용

exec sp_lock

-- 좀더 정확한 정보 확인
exec sp_who2 55

-- Lock 걸린 SPID를 입력하여 현재 실행중인 쿼리를 출력
dbcc inputbuffer(55)

-- Lock을 유발하는 SPID를 종료
KILL 55

-- Lock으로 의심되는 SPID 추출
SELECT
p.status, p.program_name, p.hostname, p.spid, p.blocked
, p.kpid, p.cpu, p.physical_io, p.waittype, p.waittime
, p.lastwaittype, p.waitresource, p.dbid, p.uid, p.memusage
, p.login_time, p.last_batch, p.ecid, p.open_tran, p.sid
, p.hostprocess, p.cmd, p.nt_domain, p.nt_username, p.net_address
, p.net_library, p.loginame, p.context_info, p.sql_handle, p.stmt_start
, p.stmt_end
FROM
master..sysprocesses as 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

[SQL Server에서 락(lock) 발생을 줄이는 방법] http://blog.naver.com/webman21/17824634

[Block 모니터링 ] http://sqlmvp.kr/140164272846

[DeadLock 모니터링] http://www.sqler.com/459038