[MSSQL] 데드락 (교착상태) Dead Lock

-- 데드락이 걸린경우, SQL서버가 중재를 해서 하나의 프로세스를 Victim(희생양)으로 처리한다

-- 데드락 확인 방법

exec sp_lock;             -- mode가 x인 프로세스가 lock에 걸린 항목이다. spid를 복사해 둔다.

select * from master.dbo.sysprocesses where blocked != 0 and spid != blocked;

-- SQL SERVER 로그에서 데드락 상태확인을 위해 두가지 추적 플래거 사용가능

 DBCC TRACEON(1204, -1)    -- 어떤 쿼리에서 데드락 발생했는지 확인가능

 DBCC TRACEON(1222, -1)    -- 좀 더 상세한 정보 어떤 구문에서 발생했는지 확인가능 

-- 클라이언트에서 MSSQL로 보낸 최종 명령문 표시

dbcc inputbuffer( spid);

--성능 모니터에 SQLServer:Locks의 Number of Deadlocks/sec 카운터를 추가하여 모니터링

-- 관련정보 표시

exec sp_who spid;

exec sp_who2 spid;

-- 프로세스 죽이기

kill spid;

-- 데드락 해결 방법

-- 락 타임아웃 설정 (초기값은 -1)

-- 10초 지나면 취소되게 한다

SET LOCK_TIMEOUT 10000

-- 락 타임아웃 확인

SELECT @@LOCK_TIMEOUT

-- SELECT 할때 WITH (NOLOCK) 혹은 WITH (READUNCOMMITTED) 사용하여 

-- LOCK를 무시하고 데이터를 조회홤. (LOCK걸린 행도 보여짐)

-- 과거데이터를 조회할 경우 WITH (READPAST) 를 사용하여 LOCK걸린 행을 제외하고 데이터를 조회함.

SELECT * FROM TABLE1 WITH (NOLOCK);

SELECT * FROM TABLE1 WITH (READUNCOMMITTED);

SELECT * FROM TABLE1 WITH (READPAST);