Creative Commons License

Software Dev

프로그래밍기본
객체지향
프로젝트관리
알고리즘
데이타베이스

소프트웨어 개발에 필수적이고 필요한 주제에 대한 강의 및 공유

.

데이타베이스

Database 관련 기술을 다룹니다. 주로 MSSQL 기반이 될 것입니다. 비지니스 응용 개발자에게 DB 는 뗄 수 없는 존재죠...

감시하자, 데드락(Dead Lock)

작성자 : 박종명
최초 작성일 : 2009-09-08 (화요일)
최종 수정일 : 2009-09-08 (화요일)
조회 수 : 6658

개발자들이 DB 쿼리를 만들고 운용하는 회사가 많다
데드락 시나리오는 워낙 유명(?)해서 개념적으로는 다 알고 있지만, 실제로 발생했을 때 당황해 하기 쉽다
아래는 그 유명한(?) 데드락 유발 쿼리이다

세션 1>
BEGIN TRAN

UPDATE A SET ID = 2

WAITFOR DELAY '00:00:02'

UPDATE B SET ID = 2

COMMIT TRAN

세션 2>
BEGIN TRAN

UPDATE B SET ID = 3

WAITFOR DELAY '00:00:02'

UPDATE A SET ID = 3

COMMIT TRAN

위 쿼리를 두 개의 세션창에서 같이 실행하면 데드락이 발생하고,
데이터베이스는 시스템의 안정성을 위하여, 두 쿼리 중 하나를 강제로 실행 중지 시켜 버린다

LOCK는 항상 발생한다. LOCK은 DB의 기본 메커니즘이다. 다만 데드락은 발생해서는 안된다.
LOCK은 걸렸다 풀렸다 해야 하는데, 데드락은 서로가 자원을 쥐고 있어 LOCK이 풀리지 않는 교착상태이다

기본적인 LOCK 정보는 다음 프로시저를 통해 알 수 있다
SP_LOCK

이 프로시저를 실행한 결과에서,
Mode컬럼의 값이 공유잠금인 S나 내제된 공유 잠금인 IS 가 아닌
배타잠금 X, 내제된 배타잠금 IX 등이 동일한 SPID로 계속 유지되고 있다면, 의심 해봐야 한다
그리고 Status 컬럼이 GRANT 가 아니고 WAIT가 계속 나온다면 역시 의심해야 한다

다시 말하지만, LOCK 는 늘 발생하는 상황이다. 다만 LOCK 가 너무 오래 유지되는 것이 문제이기 때문에
한 두번이 아닌, 계속해서 의심되는 값이 나오는 것을 주의깊게 봐야 한다

그리고 유용하하게 사용할 만한 프로시저는
SP_WHO2 이다

이 프로시저의 결과 중 BlkBy 컬럼은 LOCK을 걸고 있는 프로세스 ID를 나타낸다.
이 역시 계속해서 이렇다면 의심해야 한다

이렇게 의심되는 SPID를 알아 냈다면, 해당 쿼리를 발췌 할 수 있다
DBCC INPUTBUFFER (54) -- 54번은 의심되는 SPID 

(다만 이 방법은 버퍼에 담긴 마지막 요청 쿼리 정보를 알려 준다)

지금까지 알아본 방법은 의심되는 시점에 확인해 볼 만한 상황이다.
평소에 데드락이 발생하는지 모니터링하는 것이 굉장히 중요하겠다


* 데드락 발생 예의 주시하기(모니터링 방법)
총 3가지 정도 방법이 있을 수 있겠다

1. 윈도우 성능 모니터링 이용
성능 카운터 중에 SQLServer:Locks -> Number of Deadlocks/sec 가 있다
이 놈을 모니터링 하면 데드락 발생 횟수를 알 수 있다

 

 

2. SQL Server 의 프로필러 이용
프로필러의 추적속성에 Locks 와 관련된 항목들을 모니터링 하자
아래 그림과 같이 Deadlock graph, Lock:Deadlock, Lock:DeadLock Chain 을 추가하자


 

3. SQL Server 의 로그 이용
DBCC TRACEON(1204, -1) 를 실행하면,
SQL Server 로그 파일에 데드락 정보가 쌓인다. 이 로그는 아주 유용하다
데드락이 발생한 시간 및 SPID는 물론이고 데드락 유발 쿼리, 이로 인해 실패한 쿼리를 모두 알 수 있게 해 준다
다음의 블로그를 참고하자
http://kuaaan.tistory.com/52

 

이름
비밀번호
홈페이지
AM <- 왼쪽의 문자를 오른쪽 박스에 똑같이 입력해 주세요