Deadlock 의 진단과 해결

RDBMS 에서 Deadlock 이라는 단어가 종종 등장한다. 대부분의 DB 안내서에 자주 등장하는 이야기 지만.. 의외로 해당 문제 때문에 trouble shooting 을 수행하여야 하는 경우는 드뭅니다. 대부분의 dealock 이 개발 단계에서 탐지되고, 수정되기 때문이죠.

 

그렇다면, 여기서 '대부분' 이라는 키워드를 사용하는 경우는 어떤 경우이며, '대부분' 에 포함되지 않는 예외 Case 는 어떤 것들이 있는지, 그리고 그에 대한 해결은 어떻게 접근하는지에 대하여 생각해 보기로 하겠습니다..

 

Deadlock 의 정의에 대해서는 따로 설명하지 않겠습니다. 정의는 적당한 문서가 많이 있습니다..

Deadlock 의 탐지

 

1. SQL 이벤트

2. 과도한 CPU 의 사용

3. 작업 모니터에 CXPacket 의 기록

 

1 의 경우 대부분의 개발 과정에서 탐지 됩니다. 그리고 안되더라도... 왠만큼 작은 서비스가 아닌 담에야 초짜 DBA 또는 개발자에게 DML 이나 SQL Query 를 통째로 맡길 강심장 프로젝트 메니져는 많지 않습니다.

 

뭐 실제로 그렇다 하더라도, 그런 SQL 이야 좀 Deadlock 이 걸리면 어떠랴...

 

Lock 은 성능을 저하 시키는 가장 큰 부분이라 튜닝의 대상이지만, Deadlock 은 application time out 같은 장애 상황을 유발하는 오류이므로 튜닝이 아닌 Trouble shooting 의 대상이라는 것을 기억하여야 합니다.

 

해결의 방법은 SQL coding 에서 해결하는 부분이니 다른 참고 자료를 참고하시고...

 

개발 이후 운영 중에 발생하는 deadlock 의 탐지와 해결에 대해서 한번 이야기해 보겠습니다.

 

별로 사용하는 쿼리나 Data 도 없는데, CPU 의 사용이 급격하게 증가하고, 심지어 Application 의 Timeout 이 발생할 경우 deadlock 을 의심해 볼 수 있습니다.

 

많은 경우 CPU 를 대량으로 사용하거나 하는 경우는 일반적으로 DML 이라 쿼리 문을 잘못 작성한 경우가 많습니다.

 

하지만 작업 모니터 (activity monitor) 에서 CXPACKET 이 발견되는 경우는 Deadlock 때문입니다. 

 

 - 작업 모니터에서 대량으로 발견되는 CXPACKET... 이 정도면 대부분의 사용자들이 성능 저하 또는 Time out 을 경험하게 됩니다

 

그런데...개발 발견되지 않은 CXPACKET 이 왜 운영 환경에거 발견될까요?

 

MAXDOP

 

이유는 개발 환경보다 운영 환경의 HW Spec 이 높은 경우, 특히 CPU 의 Core 개수가 많은 경우 발생합니다.

 

대중의 CPU 가 리소스 점유율이 높은 Query 를 동시에 실행할 경우 CPU 자원의 사용 교착 상태로 인하여 해당 문제가 발생할 수 있습니다.

 

이 경우 SQL 인스턴스의 CPU 병렬 처리 값을 조정해 주면 간단히 해결됩니다.

 

서버 속성/고급/병렬처리에서 '최대 병렬 처리 수준' 의 값을 4 이하로 조정해야 합니다.

경우에 따라서는 2 또는 극단적인 경우 1이 될 수도 있으며, 실제적인 성능 저하 (예를 들어 10 msec 걸리는 query 가 500 msec 로 걸리는) 가 없다면, 해당 값을 조정해도 문제가 없습니다.

 

이는 영문 표기로 Max degree of Parallelism 으로 표기되며, 해당 정보는 MS 에서도 공식적으로 4 이하로 권고하고 있습니다. 기본 값은 0 으로 무한대로 사용하게 되어 있습니다. 특히 복잡한 쿼리를 많이 쓰는 경우 Deadlock 문제가 발생할 확률이 높습니다.