실전 데드락 사례 (3) - SQL 프로그래밍 배우기 (Learn SQL Programming)

SQL 활용 및 팁

특정프로그램 접속 금지
특정IP로부터 접근 허용
데이타베이스 삭제
IDENTITY 컬럼 활용
파워쉘 TCP포트 변경
SET NOEXEC 활용
TSQL 파일 체크섬 산출
편리한 MERGE문 활용
데드락 분석의 기초 (1)
단순한 데드락 사례 (2)
실전 데드락 사례 (3)
테이블 변수의 활용
백업/복원 진척 확인
Recursive CTE 사용
최대값 산출 SELECT
WHERE-조건별 다른쿼리
레코드에 일련번호 붙이기
SQL 2012 시퀀스 사용법
ROW 데이타 하나로 결합
ROW 데이타를 CSV로 결합
대량 레코드 분할 실행
OBJECT_ID() 함수 활용

특정프로그램 접속 금지

특정IP로부터 접근 허용

데이타베이스 삭제

IDENTITY 컬럼 활용

파워쉘 TCP포트 변경

SET NOEXEC 활용

TSQL 파일 체크섬 산출

편리한 MERGE문 활용

데드락 분석의 기초 (1)

단순한 데드락 사례 (2)

실전 데드락 사례 (3)

테이블 변수의 활용

백업/복원 진척 확인

Recursive CTE 사용

최대값 산출 SELECT

WHERE-조건별 다른쿼리

레코드에 일련번호 붙이기

SQL 2012 시퀀스 사용법

ROW 데이타 하나로 결합

ROW 데이타를 CSV로 결합

대량 레코드 분할 실행

OBJECT_ID() 함수 활용

데드락 실전 사례 연구 1

이번에는 실제로 동시 유저가 많은 Production 서버에서 실제 발생했던 데드락 사례를 들어 보자. 이 사례는 하나의 Stored Procedure (SP)를 동시에 수 많은 클라이언트가 접속했을 때만 간헐적으로 발생했던 데드락 케이스이다. SP 중 핵심이 되는 부분만 발췌하면 아래와 같다. 기본적으로 Job과 JobQueue라고 하는 두 테이블이 있는데, 이 두 테이블를 조인해서 다음 JobId를 구한 후 JobQueue를 UPDATE하는 문장이다. 이 문장은 보통 상황에서는 아무 문제 없이 돌아간다. 물론 부하가 낮은 테스트 머신에서도 아무 문제가 없다. 하지만 부하가 높은 Production에서 데드락이 거의 몇 초에 한번씩 발생하는데, 이의 원인은 무엇일까? 물론 이전 장에서 이미 설명하였듯이 기본적으로 SQL Profiler의 Deadlock 그래프를 검토하여 문제를 접근해 들어간다. 아래 문장의 문제점은 다음과 같다. WITH 안의 SELECT 문장을 실행한 한 SPID (예: 55)가 Job과 JobQueue 테이블에 대해 S Lock을 소유한다. 동시에 다른 SPID (56)가 동일한 SELECT 문을 실행하여 JobQueue에 대해 S Lock을 소유한다. 그런데 HOLDLOCK 이라는 Lock Hint가 사용되었으므로, 양쪽 SPID가 소유한 S Lock을 트랜잭션이 끝날 때까지 계속 유지된다. 이제 SPID 55가 UPDATE JobQueue 문을 실행하기 위해 JobQueue 테이블에 대해 X Lock을 가져야 하는데, SPID 56가 이미 S Lock을 가지고 있으므로, SPID 56가 S Lock을 해제할 때까지 기다린다. 한편, SPID 56은 SELECT 문 다음 문장인 UPDATE JobQueue를 실행하고자 하는데, SPID 55가 S Lock을 가지고 있으므로 이것이 해제되길 기다린다. 따라서 Deadlock이 발생할 수 있다.

예제

-- 키포인트: SELECT FROM 절에 HOLDLOCK 있음
-- JobJobQueueView는 Job과 JobQueue 테이블을
-- JobId로 조인한 View임.

WITH CTE1 (NextJobId) AS 
( 
   SELECT TOP 1 JobId 
   FROM JobJobQueueView WITH (HOLDLOCK) 
   WHERE (JobType = @JobType) 
    AND (DisabledCount <= 0) 
    AND (SentToMachine IS NULL) 
    ORDER BY Priority DESC, CreateTime ASC
)
UPDATE JobQueue 
SET @JobId = JobId, SentToMachine = @MachineName,StartTime = GETDATE()
FROM CTE1 
WHERE JobId = COALESCE(CTE1.NextJobId, -1);
-- @JobID is OUTPUT

실전 사례 연구 2

위의 사례 1의 문제점을 해결하기 위해 아래 예제와 같이 HOLDLOCK을 삭제했다고 가정해 보자. 이제 데드락은 더 이상 나타나지 않는다. 하지만, 또 다른 부작용이 발생하는데, 이는 다음 Next JobId 만을 가져와야 하는 SP에서 동일한 JobId를 여러번 리턴한다는 점이다. 이 문제는 다음과 같이 발생할 수 있다. WITH 안의 SELECT 문장을 실행한 한 SPID 55가 Job과 JobQueue 테이블에 대해 S Lock을 소유한다. 동시에 다른 SPID (56)가 동일한 SELECT 문을 실행하여 JobQueue에 대해 S Lock을 소유한다. 이렇게 동시에 SELECT 문장을 실행하면 동일한 Next JobId를 동시에 가질 수 있다. 그런데, 양쪽이 가지고 있던 S Lock은 HOLDLOCK이 없기 때문에, SELECT문의 완료와 동시에 해제 된다. 이후, 양쪽 중 한 쓰레드 예를 들어 SPID 55가 UPDATE JobQueue를 실행하기 위해 X Lock을 소유하고 SPID 56는 이 X Lock이 풀리기르 기다린다. SPID 55가 UPDATE를 끝낸 후, SPID 56도 같은 JobID에 대해 UPDATE를 다시 한번 실행하고 두 SPID는 동일한 JobId를 리턴한다. 또 다른 케이스는, SPID 55가 SELECT를 끝낸 후 대기 중에 있을 때, SPID 56가 SELECT와 UPDATE를 모두 끝내고 Lock을 해지하는 경우도 생각할 수 있다. 이 경우도 동일한 JobId를 리턴할 수 있다.

예제

-- 키포인트: SELECT FROM 절에 HOLDLOCK 삭제

WITH CTE1 (NextJobId) AS 
(
    SELECT TOP 1 JQ.JobId
    FROM Job J, JobQueue JQ
    WHERE J.JobId = JQ.JobId
      AND (DisabledCount <= 0)
      AND (SentToMachine IS NULL)
    ORDER BY Priority DESC, CreateTime ASC
)
UPDATE JobQueue
SET @JobId = JobId, SentToMachine = @MachineName, StartTime = GETDATE()
FROM CTE1
WHERE JobId = COALESCE(CTE1.NextJobId, -1);
-- @JobId is OUTPUT

실전 사례 연구 3

위의 케이스 1, 2의 문제점을 해결하기 위해 아래 문장와 같이 UPDLOCK을 사용할 수 있다. UPDLOCK 힌트는 다른 SPID가 S Lock을 획득하는 것을 허용하지만, X Lock이나 U Lock을 획득하지는 것을 허용하지 않는다. 아래 예제에서, SPID 55는 SELECT JobQueue WITH (UPDLOCK) 힌트를 가지고 있으므로, SELECT 싯점에서 U Lock을 소유한다. SPID 56는 동시에 SELECT를 실행할 때, U Lock을 획득해야 하는데, 이미 SPID 55가 소유하고 있으므로 이것이 해제될 때까지 대기한다. SPID 55는 UPDATE 문장을 실행하기 위해 U Lock을 X으로 전환하고 (이를 Lock Conversion 이라 한다) 갱신을 완료한다. SPID 55가 완료후 X Lock을 해제 하면, SPID 56는 U Lock을 획득하고 SELECT, UPDATE를 진행한다. 따라서 이렇게 데드락 및 중복 ID 문제를 해결할 수 있다.

예제

-- 키포인트: SELECT FROM 절에 
-- JobQueue 테이블에 대해 UpdLock

WITH CTE1 (NextJobId) AS 
(
    SELECT TOP 1 JQ.JobId
    FROM Job J, JobQueue JQ WITH (UPDLOCK)
    WHERE J.JobId = JQ.JobId
      AND (JobType = @JobType)
      AND (DisabledCount <= 0)
      AND (SentToMachine IS NULL)
    ORDER BY Priority DESC, CreateTime ASC
)
UPDATE JobQueue
SET @JobId = JobId, SentToMachine = @MachineName, StartTime = GETDATE()
FROM CTE1
WHERE JobId = COALESCE(CTE1.NextJobId, -1);
-- @JobId is OUTPUT

본 웹사이트는 광고를 포함하고 있습니다. 광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.