SQL Server DeadLock 시리즈가 어느덧 4편까지 왔네요. 4편에서는 지금까지의 내용을 정리하고 3편에 이어 현실에서 발생한 DeadLock에 대한 원인과 해결책에 대해서 이야기를 해보겠습니다.


SQL Server DeadLock 1편

http://resisa.tistory.com/184


트랜잭션과 Lock에 대한 기본 개념과 IsolationLevel 중에 실제로 사용가능한 ReadUncommitted와 Snapshot에 대하여 설명을 하였습니다. 그리고 DB Lock의 종류(X, U, S)에 대하여 알아보았습니다.

여기서 자체 질문은 S-Lock의 아래 내용입니다.


하나의 트랜잭션에서 S-Lock 잠금을 설정하면 다른 트랜잭션에서도 U-Lock, S-Lock 잠금이 가능합니다. 

=> 해당 글을 쓸 때보다 조금 더 알게 되었으니 표현을 아래와 같이 고급지게 해보겠습니다. 


리소스에 S-Lock이 획득되고 동일 리소스에 다른 트랜잭션에서 U-Lock, S-Lock으로 호환 가능합니다.

=> Q : 동일 리소스에 S-Lock이 획득되고 X-Lock 호환은 왜 불가능한 것일까요? 

=> A : 솔직히 모르겠습니다 ㅎㅎㅎ 그냥 정의라고 생각하고 외워야 할것 같은 불길한 느낌이 듭니다. 논리적으로 딱 맞아 떨어지는건 없지만 맞추기 식의 논리를 펼치면 S-Lock은 읽기의 경우에 획득하는 잠금으로 Select문이 실행되는 순간에 S-Lock이 획득되고 실행이 완료되면 S-Lock이 바로 해제가 됩니다. 즉 S-Lock은 Lock이 획득되는 시간이 매우 짧으며 Dirty 데이터를 읽지 않기 위해서는 그 짧은 시간 동안에 X-Lock과 호환이 가능하게 할 필요가 없어 보입니다.

=> 여기서 꼭 말하고 싶은 것은 트랜잭션과 Lock에 대한 기본 개념을 이해하고 S-Lock의 경우 트랜잭션 내에서 잠금이 바로 해제된다는 것입니다.


SQL Server DeadLock 2편

http://resisa.tistory.com/185


2편에서는 리소스(KEY, PAG, TAB)에 대한 개념을 설명하고 DeadLock 예제와 함께 잠금의 호환성에 대하여 알아보았습니다. 해당 예제가 현실하고는 맞지 않는 예제였지만 1편에서 말하고 싶었던 S-Lock에 트랜잭션 내에서의 개념을 말하고 싶은 예제였습니다. 추가적으로 데이터의 양에 리소스가 변경될 수 있다는 것도 중요한 사실입니다. 이번 시리즈에서는 자체 질문은 아래와 같습니다.

Q : Intent Lock은 뭔가요??

A : 1편, 2편에 참고했던 사이트에서 아래와 같이 정의하고 있습니다.


특정 로우에 Lock을 설정하면 그와 동시에 상위 레벨 개체(페이지, 익스텐트, 테이블)에 내부적으로 의도(Intent) Lock이 설정된다. Lock을 설정하려는 개체의 하위 레벨에서 선행 트랜잭션이 어떤 작업을 수행 중인지를 알리는 용도로 사용되며, 일종의 푯말(Flag)이라고 할 수 있다. 예를 들어, 구조를 변경하기 위해 테이블을 잠그려 할 때 그 하위의 모든 페이지나 익스텐트, 심지어 로우에 어떤 Lock이 설정돼 있는지를 일일이 검사해야 한다면 좀처럼 작업이 끝나지 않을 수 있다. 의도 Lock은 그런 현상을 방지해 준다. 즉, 해당 테이블에 어떤 모드의 의도 Lock이 설정돼 있는지만 보고도 작업을 진행할지 아니면 기다릴지를 결정할 수 있다.

=> 이해가 잘되시나요? 대충 어떤 느낌인지는 천천히 읽어보면 알 수 있지만 딱 와닿지는 않을 수 있다고 생각됩니다. 그래서 예를 들어보면 특정 하나의 로우에 Update문을 실행하면 KEY(리소스)에 X-Lock 잠금이 획득됩니다. 이전 트랜잭션이 완료되지 않은 상황에서 다른 트랜잭션에 Insert는 당연히 가능해야 할 것으로 보입니다. 다시 이전으로 돌아가서 Update문을 실행할 때 KEY뿐만이 아니라 PAG, TAB에도 잠금이 발생하는데 Intent-Lock이라는 개념이 있어야 IX로 잠금을 걸고 다른 트랜잭션에서 실행하는 구문과 잠금의 호환성에 따라서 동시성을 높일 수 있을 것입니다.


SQL Server DeadLock 3편

http://resisa.tistory.com/187


3편에서는 자주 사용하는 테이블 힌트를 정리해보고 현실에서 있을 만한 DeadLock 예제를 살펴보았습니다. 트랜잭션에서 Select ~ Update 구문에서 발생하는 DeadLock의 해결책으로 WITH(UPDLOCK) 힌트를 주어 가장 먼저 진입한 트랜잭션이 완료될 때까지 다른 트랜잭션은 모두 블럭(대기)을 하는 알고리즘으로 Non-Repeatable Read도 발생하지 않도록 하였습니다.


이제 4편 본론에 해당하는 내용으로 3편 예제를 확장시켜 프로세스(트랜잭션) 3개에서 발생하는 DeadLock 예제를 살펴보도록 하겠습니다.

현재까지는 테이블 1개에 대해서만 어떤 리소스에 어떤 잠금이 획득되는지 살펴보았는데 테이블을 2개로 늘려보도록 하겠습니다.

테이블에 대한 정의는 아래와 같습니다.

1. 충전 테이블 : 사용자에 대한 충전 포인트 정보를 확인

2. 포인트 테이블 : 사용자의 누적 포인트를 확인

=>  충전 테이블에는 충전을 할 때마다 충전 정보가 쌓이게 됩니다. 예를 들어 100원 충전, 200원 충전 이런 정보들이 쌓이고 포인트 테이블에서는 해당 사용자는 300원을 보유하고 있다는 정보를 가지고 있는 구조입니다.


비지니스 로직은 충전과 차감이 있으며 기능적으로 아래와 같습니다.


 

 충전

 차감

 비고

 접근 테이블

 충전, 포인트 테이블

 충전, 포인트 테이블

- 다른 테이블도 있지만 여기서는 2개의 테이블을 대상으로만 이야기 합니다.

 기능

 1. 충전 테이블 Insert

 2. 포인트 테이블에 Select, Update 

 1. 포인트 테이블 Select

 2. 차감 로직

 3. 포인트 테이블 Select, Update

- 차감 로직은 생략

- 기능은 하나의 트랜잭션으로 구현

=> 3편에서 말했던 Non-Repeatable Read는 차감 기능 1번에서 발생합니다. 여기서 이해해야 하는 것은 차감을 병렬적으로 실행하다보면 포인트 테이블에 대한 Select ~ Update로 인한 3편에서 말했던 문제가 동일하게 발생한다는 점입니다. 그래서 Select에 WITH(UPDLOCK)을 차감 기능 1번, 3번에 추가하였습니다.(충전 기능 2번과 차감 기능 3번은 동일한 내용으로 충전 기능 2번에도 동일하게 적용 됩니다.)

차감을 병렬적으로 실행해본 결과 문제가 해결되었습니다. 하지만 기존에 만들어 두었던 충전+차감을 병렬로 테스트하는 코드에서 DeadLock이 발생하는 것이 확인 되었고 DeadLock graph는 아래와 같습니다.



=> 헛. 프로세스(트랜잭션)가 하나 더 늘어났을 뿐인데 복잡해 보입니다. 눈을 크게 뜨고 익숙해지기 위해서 그림을 쳐다보면서(농담;; 집중해서 로직을 곰곰이 생각해보면서) 아래와 같이 정리하였습니다.


 

 기능

 설명

 비고

 프로세스 ID 57

 차감 2번

 57번은 포인트 테이블에 U 잠금을 획득하고 충전에 대하여 S 잠금을 요청하고 있습니다. 차감 기능이며 1번에서 U 잠금을 획득 2번에서 S 잠금을 요청하는 것으로 보입니다.


 프로세스 ID 59

 차감 1번

 59번은 포인트 테이블에 대한 U 잠금을 획득하거나 요청하고 있습니다.

 

 프로세스 ID 60

 충전 2번

 충전 테이블에 X 잠금은 충전 기능1번에서만 가능하며 포인트 테이블에 U 잠금을 요청하고 있으므로 충번 1번 기능이후 2번으로 보입니다.

 

=> 일단 그림만 봤을 때 가장 이상한 부분은 Owner에 대한 개념입니다. 동일한 KEY 리소스에 대하여 동시에 Owner를 하는 것처럼 보입니다. 이전에 그림만 보고서도 대부분 유추가 가능할 것이라고 했는데 정확하게 알기 위해서 파일의 내용을 살펴보겠습니다.


<process id="process2e0507088" taskpriority="0" logused="1048" waitresource="KEY: 7:72057594041270272 (1b7fe5b8af93)" waittime="3207" ownerId="1649980" transactionname="user_transaction" lasttranstarted="2017-06-27T12:01:39.290" XDES="0x2d8f3a3b0" lockMode="S" schedulerid="3" kpid="7508" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-27T12:01:39.297" lastbatchcompleted="2017-06-27T12:01:39.290" lastattention="1900-01-01T00:00:00.290" clientapp=".Net SqlClient Data Provider" hostname="SUNGKKIM-N" hostpid="7928" loginname="SMILEGATE\sungkkim" isolationlevel="read uncommitted (1)" xactid="1649980" currentdb="7" lockTimeout="5000" clientoption1="673185824" clientoption2="128056">

    <executionStack>
     <frame procname="SP명" line="327" stmtstart="21906" stmtend="22334" sqlhandle="

0x03000700805efa59d10a65019ea7000001000000000000000000000000000000000000000000000000000000

">
DeadLock 발생 SQL 구문
    </executionStack>
    <inputbuf>
Proc [Database Id = 7 Object Id = 1509580416]    </inputbuf>
   </process>

   <keylock hobtid="72057594041925632" dbid="7" objectname="TSPURCHASE.dbo.

HoldPoint" indexname="PK_HoldPoint" id="lock2d979fb80" mode="U" associatedObjectId="72057594041925632">
    <owner-list>
     <owner id="process2e0507088" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2e1c1a8c8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>

=> 일단 Owner에 대해서 알아보면 owner-list에서 requestType의 값으로 잠금을 획득하고 있는지 대기(블럭)인지 알 수 있습니다. 결과적으로 위의 xml 내용에서는 owner하고 있는 프로세스 spid의 값이 57번 것으로 알 수 있습니다.

=> 참고 : xml 파일의 내용 중에 중복된 부분은 제거하였고 살펴볼 필요가 있는 부분을 노란색으로 표시하였습니다.


그럼 Owner에 대한 의문점은 사라졌고 DeadLock이 발생하는 원인을 분석해보면 기존에 Select ~ Update문과 충전 테이블에 대한 X-Lock, S-Lock이 추가되면서 3개의 프로세스(트랜잭션)에서 서로 물고 물리면서 DeadLock이 발생하였습니다. 여기서 알 수 있는 사실은 일부 로직이 동일한 리소스에 잠금을 획득할 경우 병렬로 실행될 경우 DeadLock이 발생할 수 있다는 사실입니다.

다르게 표현을 해보면 충전 기능 1번에 의해서 충전 테이블에는 데이터가 들어가고 트랜잭션 완료되기 전에 차감 기능 2번에서 해당 테이블에 Dirty 데이터를 읽기 때문에 발생하는 현상입니다.


원인 파악을 했으니 해결을 해보려고 충전 테이블에 힌트를 써보았지만 다른 부작용이 너무 많았습니다. 테이블 2개에 잠금이 발생하다보니 DeadLock이 훨씬 빈번하게 일어났고 차감 로직도 복잡하여 일단 접었습니다. 다음으로 생각할 수 있는건 충전 기능 1번 전에 충전 기능 2번을 추가하는 것입니다. 원인이 충전 테이블에 데이터가 들어가는 동시에 다른 트랜잭션에서 해당 테이블에 접근하기 때문입니다. 하지만 해당 방법은 문제를 해결한다는 느낌이 들지 않고 필요없는 로직이 추가되는 느낌이라 접었습니다. 그러다 문득 DeadLock은 어쨌든 잠금에 의해서 발생하기 때문에 충전 기능 1번과 2번을 별도 트랜잭션으로 분리하면 되는 것 아닌가 하는 생각이 들었습니다. 분리를 하였을 경우 기능적으로 문제가 없는지에 대해서 고민을 해보았습니다.


분리했을 경우 문제가 되는 부분은 아래 2가지로 생각이 됩니다.

1. 충전 기능 2번이 실패했을 경우

2. 충전 기능 2번은 실패할 확률이 얼마나 되는지

=> 충전 기능 2번이 실패했을 때의 문제는 포인트 조회와 관련된 부분입니다. 또한 이 기능이 실패할 확률은 하드웨어 적인 부분이나 DB연결과 관련된 문제가 있을 경우에만 실패할 것으로 보입니다. 만약 실패했을 경우 재시도하는 로직까지 넣는다면 문제가 될 만한 확률은 더 작아집니다.


충전 기능 1번과 2번은 분리를 해서 테스트를 진행해보면 예상했던 것처럼 DeadLock이 사라지게 됩니다. 성능적인 측면에서 봤을 때는 DB 연결이 늘어나기 때문에 상대적으로 느려지지만 충전의 성능은 차감에 비하면 절대적으로 빠른 편입니다.


SQL Server DeadLock 시리즈는 이제 이것으로 마무리 하려고 합니다. 오래만에 블로그에 열정을 쏟게 해준 애드센스에게 심심한 감사의 말을 전하며 도대체 언제 달게 해줄꺼니? ㅎㅎㅎ


P.S : UPDLOCK이 생각하는 것처럼 동작하지 않아 원인을 Merge구문 때문이 아닌가 싶어 Select ~ Update, Insert로 동료분이 변경을 해주었는데 결과가 동일하였습니다;; 제가 DeadLock graph를 제대로 보지 않아서 쓸데없는 의심을 했습니다. 이 글은 그 분의 노력으로 탄생하게 되었습니다. 고맙습니다. ㅎㅎ

Posted by resisa