[MSSQL] Deadlock

MSSQL에서 Update 쿼리를 실행하던 중, 교착상태가 발생하여서 해당 이슈 분석하며 작성한 포스트입니다.

# 개요

특정 서비스에서 특정 테이블로 Update를 진행하던 과정에서 발생한 이슈입니다. 이 프로세스는 약 3개월 정도 돌고 있었고 며칠 전까지만 해도 이슈 없이 정상 동작하고 있었습니다.

## 질의문

보안상 원문을 그대로 가져올 수는 없고 형식만 가져오도록 하겠습니다.

IF EXISTS (	SELECT *
		FROM [MY_TABLE] WITH (nolock)
    		WHERE	index1 = ?,
          		AND index2 = ?,
          		AND index3 = ?)
	UPDATE [MY_TABLE]
    	SET index1 = ?, index2 = ?, index3 = ?, index4 = ?, index5 =?
   	WHERE	index1 = ?,
    	 	AND index2 = ?,
         	AND index3 = ?
ELSE
	INSERT INTO [MY_TABLE] VALUES (?,?,?,?,?)

동일한 프로세스가 하루에 두 번(오전, 오후)에 동작합니다. 데이터소스에 데이터가 없을 경우를 대비하여 반복적으로 동작하게 구성하였었습니다.  이상적인 상황에서는 오전에는 Insert만 동작하고, 오후에는 Update만 동작해야겠죠.

## 이슈 발생

처음 이슈가 발생했을 때는 오후(Update)에만 발생하였었는데, 시간이 지나니 오전(Insert)에도 발생하기 시작하였습니다.

 

# 분석

위 질의문을 토대로 판단해보면 IF 조건문 내에서 Select는 nolock으로 돌기 때문에 교착상태가 일어날 확률이 낮을 것 같습니다.

## Update

유력한 교착상태 원인 후보였었습니다. Update구문 내 Where절에서 테이블에 락을 거는 게 아닌가 생각하여 Where절에 중복되는 값이 있는지 체크를 진행했었습니다. (실제로 중복 데이터는 없었습니다.)

## Insert

오후 스케줄에서만 이슈가 발생했을때는 Insert를 의심하지 않았었는데, 오전에도 이슈가 발생하다 보니 근본적인 원인이 다른 쪽에 있을 거란 생각이 들었습니다.

## 메모리

데이터베이스 교착상태 참고에서 보시면 메모리 문제에서부터 교착상태가 유발될 수도 있는 것 같습니다. DB팀에 문의를 현재 넣어 둔 상태입니다.

 

# 분석 결과

## "CXPACKET" wait_type 다수 존재 (DB Resource)

CXPACKET
CXPACKET는 Parallel Process 대기유형이다. 이것은 Parallel Query에 실행 시 각 Process가 실행되는 구간에 Lock Issue가 있을 수도 있음을 의미하거나 특정 하드웨어의 병목으로 인해 다른 Process가 대기하고 있는 Process가 끝날 때까지 대기하고 있을 수도 있음을 의미한다. DBMS가 올바른 판단을 하여 병렬 처리를 했는지 아니면 환경을 만들어주지 않아서 할 수 없이 병렬처리를 하는 것인지 잘 판단해야 한다.

출처 : http://blog.daum.net/forfre/9

다수의 쿼리를 동시에 처리하는 과정에서 병목구간이 발생한 것 같습니다. 해결 방법으로 아래 옵션을 설정해 두었습니다.

SP_configure "max degree of parallelism" 수치 8 -> 1 변경

## 비효율적인 인덱스 (SQL Tuning)

Target이 되는 테이블에 인덱싱이 전혀 안 걸려 있었습니다. 선택도가 높은 컬럼을 기준으로 인덱스를 추가하였습니다.

 

# 마무리

DB 공부를 할 기회가 이런 식으로 종종 찾아오네요. 잘 까먹지 않게 로깅해둡니다.