'2017/06'에 해당되는 글 5건

  1. 2017.06.30 SQL Server DeadLock 4편
  2. 2017.06.25 SQL Server DeadLock 3편 4
  3. 2017.06.22 SQL Server DeadLock 2편
  4. 2017.06.12 SQL Server DeadLock 1편
  5. 2017.06.04 .NET IsolationLevel에 대한 고찰 5

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
,

3편에서는 테이블 힌트에 대해서 살펴보고 2편에 이어서 DeadLock이 발생하는 예제와 해결 방법에 대해서 이야기 해보도록 하겠습니다.

테이블 힌트 중에 아마도 가장 많이 사용한 것은 WITH(NOLOCK)일 것입니다. 부끄럽게도 작년에 해당 힌트가 ReadUncommitted와 동일하다는 사실을 알았습니다. 조금만 생각해보면 당연히 동일할 수 밖에 없는데 DB개발에 관심이 부족했던 것으로 원인을 돌리며 힌트와 관련된 아래 글을 추천해드립니다.

https://technet.microsoft.com/ko-kr/library/ms187373(v=sql.105).aspx


해당 글을 여유가 있으신 분은 천천히 살펴보시고 3편이 조금 더 풍성하게 보이기 위해서 해당 글을 아래와 같이 정리하였습니다.


 테이블 힌트

 설명 

 새로운 배움 

 비고 

 NOLOCK

 ReadUncommitted와 동일하며 더티 읽기를 허용합니다. 동시성이 높아질 수 있지만 사용자에게 커밋되지 않은 데이터가 제공될 수 있습니다.

 NOLOCK이라는 이름 때문에 잠금이 없을 것이라고 생각하였는데 Sch-S(스키마)잠금 획득합니다.(걸린다고 표현하였는데 획득이 훨씬 그럴듯합니다;;)

 읽기(Select)에 대한 레벨이기 때문에 당연히 삽입, 업데이트, 삭제 작업에 대해서는 해당 옵션이 적용이 되지 않습니다.

 HOLDLOCK

 S-LOCK(공유 잠금)을 트랜잭션이 완료될 때까지 잠금을 유지하여 공유 잠금을 제한적으로 만듭니다.

 SERIALIZABLE 동일

 

 REPEATABLEREAD

 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽을 수 없도록 지정합니다.

 현재 트랜잭션이 완료될 때까지 현재 트랜잭션이 읽은 데이터를 다른 트랜잭션이 수정할 수 없도록 지정합니다.

 

 UPDLOCK

 업데이트 잠금을 사용하고 트랜잭션이 완료될 때까지 유지하도록 지정합니다.

 UPDLOCK은 읽기 작업을 위해 행 수준 또는 페이지 수준에서만 업데이트 잠금을 사용합니다.
 UPDLOCK이 TABLOCK과 함께 사용되는 경우 또는 테이블 수준 잠금이 여러 이유로 인해 사용되는 경우, 배타(X) 잠금이 대신 사용됩니다.

 

테이블 힌트가 생각보다 많고 정확하게 알고 쓰지 않으면 부작용으로 고생을 할 수 있습니다;;


참고 : 위의 글에서 많은 힌트를 아래와 같이 분류해주고 있습니다.

SQL Server는 FROM 절의 각 테이블에 대해 다음 각 그룹에서 두 개 이상의 테이블 힌트를 허용하지 않습니다.

  • 세분성 힌트: PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK, 또는 TABLOCKX.

  • 격리 수준 힌트: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE


그럼 DeadLock과 관련된 예제를 살펴보겠습니다.


-- 1번 세션

-- 실행1

BEGIN TRAN


DECLARE @Normal bigint = 0

SELECT @Normal=Normal+

FROM Test WHERE ClusteredIndex = 10


WAITFOR DELAY '00:00:10'


UPDATE Test SET Normal=@Normal 

WHERE ClusteredIndex = 10


COMMIT TRAN 

-- 2번 세션

-- 실행2

BEGIN TRAN


DECLARE @Normal bigint = 0

SELECT @Normal=Normal+

FROM Test WHERE ClusteredIndex = 10


UPDATE Test SET Normal=@Normal 

WHERE ClusteredIndex = 10


COMMIT TRAN

=> 이번 예제는 실행 후에 DeadLock이 발생하지는 않습니다. 

구문을 살펴보면 Select에서 ClusteredIndex = 10인 로우의 Normal 컬럼의 값에 +1을 해주고 그 값으로 Update구문을 실행주는 예제입니다. 실행을 해보면 Normal 컬럼에 최종 값이 11이 됩니다. 하지만 실제로 원하는 값은 12입니다. 원인을 파악하기 전에 트랜잭션 IsolationLevel에 따라 발생하는 3가지 현상을 정리한 내용을 살펴보겠습니다.


낮은 단계의 트랜잭션 고립화 수준 이용시 발생하는 현상 3가지

- Dirty Read (=Uncommitted Dependency)

커밋되지 않은 수정 중인 데이터를 다른 트랜잭션에서 읽을 수 있도록 허용할 때 발생

- Non-Repeatable Read (=Inconsistent Analysis)

한 트랜잭션 내에서 같은 쿼리를 두 번 수행할 때 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제함으로써 두 쿼리의 결과가 상이하게 나타나는 비 일관성 발생

- Phantom Read

한 트랜잭션 안에서 일정 범위의 레코드를 두 번 이상 읽을 때첫 번째 쿼리에서 없던 레코드가 두 번째 쿼리에서 나타나는 현상이는 트랜잭션 도중 새로운 레코드가 삽입되는 것을 허용하기 때문에 나타남 

=> 위의 예제는 Dirty Read일까요? Non-Repeatable Read일까요? 처음에 단순히 생각했을 때는 Dirty한 것을 읽었다고 생각했지만 전제 조건을 자세히 보면 '커밋되지 않은 수정 중인 데이터'입니다. 실행2에서 Select구문은 Dirty한 것이 아니라 아직 수정이 안된 정확한 데이터를 읽은 것입니다. 원인은 Non-Repeatable Read 현상입니다.

=> SQL Server에 기본 IsolationLevel은 ReadCommitted이고 커밋이 된 데이터만 읽는 것이 아니라 커밋되기 전의 수정 중이지 않은 데이터는 그 순간에 정상적인 데이터이기 때문에 당연히 읽을 수 있는 것입니다.


해당 문제를 해결하기 위해서 IsolationLevel을 올려 REPEATABLEREAD로 변경하거나 테이블 힌트를 주어서 실행을 해봅니다. 결과는 아래와 같습니다.



=> 윽.. DeadLock이 발생하였습니다. 그림이 익숙하지 않다면 표로 시간 순서 상으로 정리해보면 이해가 더 빨리 되실겁니다.


 시간순서 

 세션1(프로세스ID 52)

 세션2(프로세스ID 53)

 1

 Select 구문 : S-LOCK 획득 

 

 2

 

 Select 구문 : S-LOCK 획득

 3

 

 Update 구문 : X-LOCK 블럭

 4

 Update 구문 : X-LOCK 블럭

 

=> 시간순서 3에서 Update구문이 블럭이 됩니다. 이유는 REPEATABLEREAD 힌트의 '새로운 배움'을 보면 알 수 있는데 세션1에서 Select문으로 읽은 데이터를 세션2에서 세션1의 트랜잭션이 완료되거나 롤백될때까지 업데이트 할 수 없기 때문입니다.

=> 시간순서 4로 가면 세션1 입장에서도 시간순서 3과 마찬가지로 블럭이 됩니다. 세션1, 세션2 중 하나의 트랜잭션이 완료되거나 롤백되어야 하는데 서로 블럭되기 때문에 DeadLock 상태가 됩니다. 물론 똑똑한 SQL Server가 이를 감지하고 뒤늦게 실행된 세션1을 중지시킵니다.


그러면 Non-Repeatable Read가 발생하지 않고 DeadLock도 발생하지 않으려면 어떻게 해야할까요? 2편에서 보았던 잠금의 호환성을 간략화해서 살펴보면 해답이 있습니다.


잠금의 호환성 (Lock Compatibility)

Requested mode

Existing granted mode

 

S

U

X

Shared (S)

Yes

Yes

No

Update (U)

Yes

No

No

Exclusive (X)

No

No

No

(테이블 출처 : http://msdn.microsoft.com/en-us/library/ms172925.aspx)


=> S-LOCK이 걸렸던 부분을 U-LOCK으로 변경한다면 시간순서 2에서 Select구문이 블럭됩니다. 세션1의 트랜잭션이 완료되거나 롤백 될때까지 다음 구문 실행이 불가능하기 때문에(블럭) DeadLock에 빠질 수도 없고 다른 트랜잭션에서 수정이 발생이 완료되거나 롤백이 된 이후에 데이터를 읽기 때문에 Non-Repeatable Read도 발생하지 않습니다.

=> 잠금을 S-LOCK -> U-LOCK으로 올려서 성능에 문제가 발생하는 것이 아닌가 생각하실 수도 있는데 잠금의 종류(KEY, PAG, TAB)에 대한 잠금 설정(획득)이기 때문에 실상황에서는 문제가 되지 않을 것으로 생각됩니다. 더군다나 DB의 입장에서는 데이터의 무결성이 무엇보다 중요합니다.


추가적으로 개발 테스트 코드에서 병렬 테스트를 하기 위한 샘플 코드 첨부합니다.

Task.WaitAll(Enumerable.Range(0, 10).Select(t => 

   Task.Run(() =>

    {

        // 비지니스 로직

    })).ToArray());


오랜만에?! 시리즈로 써보았는데 글의 내용보다는 테스트를 해서 다시 확인하는 과정에서 시간이 오래 걸렸습니다. 정확한 정보(팩트)를 전달하려다 보니 사실위주의 당연한 이야기를 한것 같아 아 별거 없네 이런 생각도 들었지만 1편 보다는 2편, 2편 보다는 3편이 더 나은 것?! 같아 대충 만족하며 마무리 하려고 합니다.


Posted by resisa
,

SQL Server DeadLock 2편에서는 리소스에 대한 개념을 정리하고 1편에서 이야기했던 Lock 종류에 대한 내용을 예제를 통해서 살펴보겠습니다. 리소스라는 용어가 지칭하는 것들이 많아서 어떤 것인지 명확하지 않게 느껴집니다. 일단 리소스의 종류는 다음과 같습니다.

1. KEY (Key)

2. PAG (Page)

3. TAB (Table)

http://www.dbguide.net/db.db?cmd=view&boardUid=148215&boardConfigUid=9&categoryUid=216&boardIdx=138&boardStep=1


=> 1편에서 공유해드렸던 사이트를 참고하면 도움이 되며 KEY는 인덱스(Row)에 해당한다고 생각하시면 됩니다. PAG는 데이터 페이지 또는 인덱스 페이지처럼 일정 범위에 해당하며 TAB 등등이 있다 정도로 이해하시면 될 것 같습니다. 일반적으로 리소스에 LOCK이 걸리면 하나의 리소스에만 걸리는 것이 아니라 KEY -> PAG -> TAB처럼 여러 리소스에 각각 다른 종류의 잠금이 걸린다고 보시면 됩니다. TAB만 잠금이 걸릴수는 있지만 KEY만 잠금이 걸리는 것은 없다고 생각하시면 됩니다.

그럼 예제를 통해서 기본적인 SQL구문에 대한 잠금에 대해서 확인해 보겠습니다.


먼저 테스트를 위한 테이블 및 데이터, 인덱스를 아래와 같이 설정합니다.

--//테이블 삭제 스크립트

IF OBJECT_ID('dbo.Test','U') IS NOT NULL

    DROP TABLE dbo.Test

GO

 

--//테이블 생성 스크립트

CREATE TABLE dbo.Test

(

    ClusteredIndex            INT       NOT NULL

,   NonClusteredIndex            INT       NULL

,   Normal            INT       NULL

,   CreateDate        DATETIME2  NOT NULL

)

GO

 

--//인덱스 생성 스크립트

CREATE UNIQUE CLUSTERED INDEX ClusteredIndex ON dbo.Test(ClusteredIndex)

GO

CREATE INDEX NonClusteredIndex ON dbo.Test(NonClusteredIndex)

GO

 

--//100,000개 데이터 입력

--//데이터 입력

SET NOCOUNT ON

GO

DECLARE @i INT = 1

WHILE(@i < 100000)

BEGIN

    INSERT INTO dbo.Test VALUES(@i,@i,@i,GETDATE())

    SET @i = @i + 1

END

GO

SET NOCOUNT OFF

GO


=> 특별한 부분은 없고 클러스터드 인덱스와 넌클러스터드 인덱스, 그냥 일반 컬럼(int, datetime2)을 가지고 있는 테이블입니다.


1. 단순 Insert 구문

BEGIN TRAN


EXEC sp_lock 52

INSERT INTO dbo.Test VALUES(100001,100001,100001,GETDATE())

EXEC sp_lock 52


ROLLBACK TRAN


=> SSMS에서 '새쿼리'로 창을 열면 보이는 숫자는 세션번호입니다. sp_lock명령어에 해당 숫자를 넣으면 해당 세션에 대한 LOCK에 대한 정보를 아래와 같이 보여줍니다.


spid

dbid

Objid

IndId

Type

Resource

Mode

Status

52

5

0

0

DB

                                

S

GRANT

52

1

1467152272

0

TAB

                                

IS

GRANT

52

32767

-571204656

0

TAB

                                

Sch-S

GRANT


=> 첫번째 sp_lock에서는  '새쿼리'창을 통하면 아래와 같이 TAB(Table)과 DB에 S-LOCK, Sch-S(스키마 공유)가 기본으로 보이므로 해당 LOCK에 대해서는 무시하시면 됩니다.


spid

dbid

Objid

IndId

Type

Resource

Mode

Status

52

5

370100359

2

KEY

(d91e288f817d)                  

X

GRANT

52

5

370100359

2

PAG

1:430                           

IX

GRANT

52

5

370100359

1

PAG

1:439                           

IX

GRANT

52

5

370100359

1

KEY

(74a07545ba5b)                  

X

GRANT

52

5

370100359

0

TAB

                                

IX

GRANT


=> 두번째 sp_lock의 결과를 살펴보면 KEY, PAG, TAB에 X, IX, IX모드로 잠금이 걸린 것을 확인하실 수 있습니다. KEY가 2개가 보이는데 눈치가 빠른 분들은 아시겠지만 해당 테이블에 클러스터드 인덱스와 넌클러스터드 인덱스 2개가 존재하기 때문입니다. KEY에 해당하는 각각 PAG잠금이 보이며 TAB은 당연히 Test테이블 기준이므로 1개만 확인이 되고 있습니다.

=> KEY에 해당하는 리소스의 값은 해쉬값이며 PAG의 1:430은 {FileNumber}:{PageNumber}입니다.


2. 단순 Select 구문

BEGIN TRAN


SELECT * FROM Test WHERE ClusteredIndex = 1

EXEC sp_lock 52


ROLLBACK TRAN 


=> 결과는 1번의 첫번째 sp_lock과 동일한 결과입니다. 해당 부분이 1편에서 설명해드렸던 트랜잭션이 있다고 꼭 LOCK이 존재하는 것은 아닌 상태입니다. 물론 Select 구문이 실행되는 그 순간에는 S-LOCK이 걸리지만 INSERT 구문과는 다르게 LOCK이 트랜잭션 내에서 구문 실행이후 해제가 된다는 것입니다.


3. 단순 Update 구문

BEGIN TRAN


UPDATE Test SET Normal = 1 WHERE Normal = 1

EXEC sp_lock 52


ROLLBACK TRAN


spid

dbid

Objid

IndId

Type

Resource

Mode

Status

52

5

674101442

1

PAG

1:162         

IX

GRANT

52

5

674101442

1

KEY

(8194443284a0)   

X

GRANT

52

5

674101442

0

TAB

    

IX

GRANT


=> Update 구문 역시 KEY에 X-LOCK이 걸립니다. 당연한 사실을 언급하면 Update 구문이라고 U-LOCK이 걸리는 것은 아닙니다. ^^;; 또 한가지 특이한 점은 INSERT가 될때에는 인덱스가 2개여서 KEY가 2개 보였지만 Update 구문은 1개만 보인다는 점인데 이것은 KEY 잠금에 대한 인덱스 종류(클러스터드, 넌클러스터드)를 구분할 필요가 없음을 의미합니다. 심지어 Normal이라는 컬럼이 조건절에 있지만 인덱스에 잠금이 걸리는 것도 알 수 있습니다.


4. 단순 DELETE 구문

=> 이건 별도로 결과를 첨부하지 않고 INSERT와 동일하다고 생각하시면 됩니다. INSERT에는 KEY가 인덱스 개수만큼 생성되고 DELETE에는 인덱스 개수만큼 삭제됩니다.


그럼 SQL 구문과 함께 Lock에 대해서 살펴보았고 이제 드디어 이 글의 제목에 해당하는 DeadLock과 관련된 내용을 살펴보도록 하겠습니다.

-- 세션1

BEGIN TRAN


DECLARE @Key bigint = 1


-- 실행1

INSERT INTO Test VALUES (200001, @Key, 1, GETDATE())


-- 실행3

SELECT * FROM Test


-- 데이터 초기화

ROLLBACK TRAN

-- 세션2

BEGIN TRAN


DECLARE @Key bigint = 1


-- 실행2

INSERT INTO Test VALUES (200002, @Key, 1, GETDATE())


-- 실행4

SELECT * FROM Test


-- 데이터 초기화

ROLLBACK TRAN


=> Test 테이블의 구조를 다시 복기를 해보면 ClusteredIndex, NonClusteredIndex, Normal, CreateDate 컬럼을 가지고 있는 테이블입니다. ClusteredIndex는 자동증가 컬럼은 아니지만 유일한 값만 가진 컬럼이라고 생각을 하고 NonClusteredIndex는 중복은 되지만 조회 조건에 꼭 필요한 정보라고 정의하도록 하겠습니다. (예를 들어 충전 테이블의 키는 자동증가이고 충전 대상에 해당하는 사용자의 정보가 NonClusteredIndex라고 보시면 됩니다)

=> 사전 준비 작업으로 SQL Profiler를 실행하여 Locks의 Deadlock graph 이벤트를 체크합니다. SSMS에서는 세션2개를 열고 실행순서에 맞게 실행을 합니다. SQL 구문 사이에 WaitFor Delay 구문을 넣는 것보다는 세션을 2개 열어서 구문을 하나씩 실행하면서 블럭이 되는 구간이나 LOCK에 대한 정보등을 확인하면서 찾는 것이 좋다고 생각됩니다. 코드가 더 익숙한 개발자는 테스트 코드로 재현을 하려고 하실 수도 있는데 개인적으로 코드로 인한 버그로 인하여 미궁으로 빠질 수도 있기 때문에 DB에서 재현을 해보는 것이 좋다고 생각됩니다.


SQL Profiler화면에서 Deadlock graph를 선택하면 아래와 같은 그림이 보입니다.


=> 서버 프로세스 53번이 1번 세션이고 54번이 2번 세션입니다. 실행4번을 하고 나면 아래와 같은 메세지를 SSMS에서 확인을 하셨을 겁니다.


트랜잭션(프로세스 ID 54)이 잠금 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. 트랜잭션을 다시 실행하십시오.


=> 그림으로 표현되는 데이터는 XML포맷이며 어느 구문이 DeadLock 구문인지 정도를 제외하면 그림에서 표시해주는 정보만으로도 대부분 원인 파악이 가능합니다.

=> 54번 서버 프로세스가 교착상태(DeadLock)로 실행이 중지되었고 그림에도 X로 표시되어 중지되었음을 가시적으로 보여주고 있습니다.

=> 어떤 리소스의 어떤 Lock에 의해서 DeadLock이 발생하였는지 확인을 해보면 페이지 리소스에 각 프로세스(트랜잭션)에서 X-LOCK과 S-LOCK이 순환 참조하게 되면서 DeadLock이 발생합니다.

=> 해당 예제를 DeadLock 첫번째 예제로 선택한 이유는 트랜잭션 내의 Select구문에 의해서도 DeadLock이 걸릴 수 있다는 것을 보여주기 위함입니다.

=> sp_lock을 통해서 확인 및 추측을 해보면 Insert구문은 KEY에는 X-LOCK이 걸리고 PAG에는 IX-LOCK, TAB에도 IX-LOCK이 걸리는 단순한 구조입니다. Select구문은 sp_lock으로 확인이 어렵습니다. Select 구문이 실행 중인 상태에서 Lock이 발생하지만 구문이 완료되고 나면 트랜잭션 내에서 Lock을 유지하지 않기 때문입니다.

DeadLock이 페이지 잠금이 발생하는데 이 부분이 이해가 잘되지 않습니다. 그래서 Select 구문에 WITH(HOLDLOCK)구문을 추가하면 TAB에 S-LOCK 잠금을 확인할 수 있고 DeadLock 그래프에서는 키잠금으로 리소스가 변경되는 것을 확인할 수 있습니다. 해당 부분에 대해서 정리를 하면 아래 표와 같습니다.


 

 INSERT 

 1. SELECT

 2. SELECT WITH(HOLDLOCK)

 3. SELECT WITH(HOLDLOCK) WHERE ClusteredIndex = 다른 세션에서 추가한 값

 KEY 

 X

 알수없음

 

 S

 PAG

 IX

 알수없음

 

 IS

 TAB

 IX

 알수없음

 S

 IS

 잠금 리소스

 

 페이지(S)

 키(RangeS-S)

 키


잠금의 호환성 (Lock Compatibility)

Requested mode

Existing granted mode

 

 

 

 

 

 

IS

S

U

IX

SIX

X

Intent shared (IS)

Yes

Yes

Yes

Yes

Yes

No

Shared (S)

Yes

Yes

Yes

No

No

No

Update (U)

Yes

Yes

No

No

No

No

Intent exclusive (IX)

Yes

No

No

Yes

No

No

Shared with intent exclusive (SIX)

Yes

No

No

No

No

No

Exclusive (X)

No

No

No

No

No

No

(테이블 출처 : http://msdn.microsoft.com/en-us/library/ms172925.aspx)


=> 3번만 명확하게 이해가 됩니다. KEY 리소스에 INSERT는 X-LOCK, SELECT는 S-LOCK이기 때문에 세션1에서도 블럭, 세션2에서도 블럭 2개의 트랜잭션 모두 커밋이 될수 없기 때문에 교착상태가 맞습니다. 1번, 2번은 도저히 이해가 안됩니다. 그래서 Test테이블의 데이터를 모두 삭제하고 다시 1번의 Select구문으로 확인을 하니 3번과 동일한 결과로 변경됩니다. 어떤 리소스의 어떤 잠금이 걸리는지 아는 것도 중요하지만 얼만큼의 데이터를 가지고 있는지에(바꿔 말하면 SQL Server 옵티마이저가 어떻게 최적화 하는지) 따라서 잠금이 발생하는 리소스도 변경될 수 있다는 점입니다.


DeadLock에 대한 첫 예제를 마무리를 하려고 합니다. 이론적으로 이해를 100%하면 좋지만 아무래도 이런 부분들은 여러 가지 상황에 대한 경험이 필요합니다. 즉 관심을 가지고 여러가지 케이스를 테스트 해보아야 하고 실제 본인의 업무에도 적용을 해야 진짜 경험이 될 수 있다고 생각합니다. (당연한 말이네요 ㅎㅎㅎ)






Posted by resisa
,

개발자가 할줄은 알지만 은근히 모르는 DB개발과 관련된 글을 쓰려고 합니다. 저도 SP를 실제 프로젝트에 사용한 것이 얼마 안되었고 프로그램 개발에 더 많은 시간을 투자하였습니다. 그래서인지 신선한?! DB개발을 하니 신입때 첫 프레임워크를 사용하는 느낌입니다.

구글에서 DeadLock과 관련된 글들을 보고 제가 이해한 것을 기반으로 설명을 해보려고 합니다. 틀린 부분이 있다면 댓글을 달아주시면 좋습니다. (댓글이 없어서 사실 아무말이나 좋습니다 ㅎㅎ)

먼저 DeadLock이기 때문에 트랜잭션과 Lock 이야기로 시작해보겠습니다.
이전 글에서 이야기 했듯이 트랜잭션(IsolationLevel)에 대해서 일반적으로 ReadUncommitted로 설정을 하고 사용할 것이라고 하였습니다. 그렇게 생각하는 이유는 ReadCommitted나 RepeatableRead, Serializable 등등은 만족할만한 성능을 얻기가 힘들며 Lock을 잠그는 시간이 길어지면서 DeadLock이 발생할 확률도 높습니다.

당연한 사실이지만 트랜잭션이 발생한 상태에서 Lock도 발생하는 것입니다. 트랜잭션이 없다면 Lock도 없습니다. 그렇다고 트랜잭션이 있다고 Lock이 꼭 있는건 아닙니다. 바로 트랜잭션 IsolationLevel 옵션을 ReadUncommitted를 사용(일반적으로 Select구문일 경우)할 때입니다. ReadUncommitted는 그 이름처럼 다른 트랜잭션에서 커밋이 되기 전의 데이터를 읽을 수 있습니다. Lock없이 읽기 때문에 성능적인 측면이나 DeadLock이 발생하는 것을 방지하려는 의도로 사용되는 것입니다. 다만 커밋되기 전의 데이터 읽게 되므로 데이터의 무결성이 깨질 수 있습니다.
=> 위의 Lock이 없다는 말은 잘못된 표현이네요. 아무리 짧은 SQL 구문이라도 Lock이 발생합니다. 저 말의 의미는 실행시간이 매우 짧은 Select 구문일 경우 트랜잭션 내에서 ReadUncommitted일 경우 실행 후 Lock이 해제된다는 사실에 대한 강조하려다 보니 잘못된 표현을 한 것으로 보입니다. (2017-08-20)

하나 더 살펴보아야 할 IsolationLevel은 Snapshot입니다. 아래처럼 DB에 설정이 필요합니다.

-- 1. Snapshot 허용
ALTER DATABASE [DB명]
SET ALLOW_SNAPSHOT_ISOLATION ON

-- 2. ReadCommitted 옵션을 사용할 경우 Snapshot옵션으로 처리
ALTER DATABASE [DB명]
SET READ_COMMITTED_SNAPSHOT ON

=> 1번이 DB에 Snapshot옵션을 설정하는 명령어입니다. 2번 명령어는 SQL Server의 IsolationLevel ReadCommitted기본값이므로 해당 옵션을 사용할 때 Snapshot으로 처리하기 위한 명령어입니다. (확실한 것은 아니지만 2번으로 명령어를 사용하여 ReadCommitted로 테스트할 때와 1번 명령어만 사용하여 Snpashot옵션을 주어 처리할 때의 결과가 다른 적이 있어서 2번 명령어는 사용하지 않고 명시적으로 Snapshot옵션을 주는 것이 바람직해보입니다.)

그럼 Shapshot옵션을 주면 트랜잭션 사이의 격리성을 어떻게 관리하게 되는 것일까요? 트랜잭션이 시작되면 별도 DB(temp)에 트랜잭션 시퀀스 번호를 관리하여 트랜잭션 사이에 경합이 발생하면 경합이 발생한 트랜잭션 중 하나를 롤백(낙관적 동시성 모델)시켜서 데이터의 무결성을 보장하고 DeadLock을 방지해줍니다. 자세한 내용은 아래 MSDN을 참고하시면 됩니다. 여기서 중요한 것은 경합이 발생할 때 트랜잭션을 취소시키기 때문에 실패하는 트랜잭션이 발생하지만 동시성(성능)은 높은 옵션이라고 말할 수 있습니다. 다만 동시성이 높아지면 높아질수록 성공률은 낮아지게 되는 문제점이 있습니다.

https://msdn.microsoft.com/ko-kr/library/tcbchxcb(v=vs.110).aspx

그럼 정리를 해보면 트랜잭션 IsolationLevel중 성능을 고려하면 아래 2가지 중 하나를 선택할 수 밖에 없습니다.
1. ReadUncommitted => 단점은 더티 데이터로 인하여 데이터 무결성이 깨질 수 있습니다.
2. Snapshot => 동시성이 높아지면 성공률이 낮아집니다.

기본적으로는 ReadUncommitted를 사용하고 복잡한 트랜잭션에 대한 Lock을 관리하여 데이터의 무결성을 유지해야 합니다. Snapshot옵션은 동시성(DB)이 그렇게 높지 않은 시스템에서 안전하게 운영을 하고 싶을 경우에 사용하면 좋지 않을까 싶습니다.

DB Lock의 종류는 아래와 같습니다.
1. X-Lock(Exclusive, 배타) : Insert, Update, Delete와 같이 데이터 수정하는 작업을 말하며 같은 리소스에 대해서 동시에 실행될 수 없습니다. 즉 하나의 트랜잭션에서 X-Lock으로 잠금을 설정하면 다른 트랜잭션에서는 어떠한 잠금도 설정할 수 없습니다.
2. S-Lock(Shared, 공유) : 일반적으로 Select로 대표되며 데이터 읽기 작업을 합니다. 하나의 트랜잭션에서 S-Lock 잠금을 설정하면 다른 트랜잭션에서도 U-Lock, S-Lock 잠금이 가능합니다.
3. U-Lock(Update, 업데이트) : 설명을 짭게 하기가 힘들지만 U-Lock 잠금이 걸려 있어도 S-Lock만은 걸 수 있다 정도로 알면 될것 같습니다.
(이외에도 I-Lock, Schema, Bulk Update, Key-Range가 있지만 너무 많은 정보는 이해하는데 장애물이 되므로 패스하는 것이 좋습니다.)

일단은 이정도만 대략적으로 이해한 상태에서 간단한 예제를 통해서 DeadLock 상황을 만들어보고 원인과 해결 방법을 통해서 이해력을 높이면 충분히 DB도 아는 괜찮은 개발자라고 할 수 있을 것 같습니다. 저는 이미 괜찮은 개발자! ㅋㅋㅋ

참고 : Lock과 관련되어 상세하게 정리되어 있는 사이트입니다.
http://www.dbguide.net/db.db?cmd=view&boardUid=148215&boardConfigUid=9&categoryUid=216&boardIdx=138&boardStep=1


Posted by resisa
,

또?! 오랜만입니다 ㅎㅎ 지인이 애드센스 등록을 하셔서 저도 시도를 해보았더니 콘텐츠 부족으로 실패하여 이렇게 새로운 글을 쓰고 있습니다.

어떤 내용을 쓸까 고민을 하다가 블로그를 정리하면서 예전에 썼던 글 중에 그 당시 궁금했던 내용에 대한 답변을 해보고자 합니다.
(벌써 7년이 지났다니 시간이 참 빠릅니다;;)

TransactionScope 그리고 Stored Procedure (2010. 07. 25)
출처: http://resisa.tistory.com/110 [심플하게~]

(중략)...

1. 한 가지 의문점은 SP안에서는 트랜잭션의 Scope 범위(TransactionScopeOption) 지정하는 부분은 없어보이지만 IsolationLevel 대해서는 지정을   있습니다또한 TransactionScope에서도 IsolationLevel 대해서 지정할  있습니다그래서 IsolationLevel 따른 LTM SQLTransaction 관계가 어떻게 될지도 살짝 궁금해집니다. 하지만 실질적으로 이러한 것을 프로젝트에서 정확하게 알고 사용하는 개발자가 얼마나 될지도 의문이며 IsolationLevel 트랜잭션 사이의 잠금(lock) 대한 것이기 때문에 2. 성능보다는 안전성을 중요시하는 우리 나라의 대부분의 프로젝트에서는 ReadCommitted 사용하는 것이 개발자의 정신건강에 이로워 보입니다. ^^

예전에 썼던 글에 은근히 잘못된 내용이 있습니다;;  이유는 이해부족 입니다;;
저는 새로운 기술을 습득하는 과정에서 '정의'를 자신의 방식으로 표현하고 증명하는 과정을 반복합니다. '정의'와 '증명'을 잘못하면 위와 같이 잘못된 글이 나오기도 하지만 경험이 붙으면 증명하는 시간이 짧아지고 단순 실수도 줄어 이해하는 시간이 짧아지고 자신의 표현 방식으로 인하여 오랫동안 기억되는 장점이 있는 방식이라고 생각됩니다. 그럼 잘못된 부분부터 수정해보겠습니다.

=> 1번에서는 개념에 대해서 잘못 설명하고 있습니다. TransactionScopeOption은 TransactionScope클래스의 파라미터로 DB(SP)에는 없는 속성입니다.

=> 2번 대부분의 프로젝트에서 ReadCommitted로 설정할 것이라는 이유는 MSSQL의 기본 IsolationLevel이 ReadCommitted이기 때문입니다. 이 당시에는 IsolationLevel에 대한 이론적인 것만 알고 있었기 때문에 이런 결론을 냈었는데 업그레이드된?! 지금은 대부분의 프로젝트에서는 ReadUncommitted를 사용할 것이라고 생각합니다. 왜냐하면 ReadCommitted로 설정을 하면 DeadLock이 발생하는 구간이 많이 있을 수 있습니다. 그렇기 때문에 직접적으로 ReadUncommitted를 지정하지 않았더라도 이와 동일한 WITH(NOLOCK) 힌트를 많이 사용했을 것이라고 추측됩니다.

그럼 본론으로 들어가서 노란색 부분에 대한 이야기를 풀어보겠습니다.

먼저 트랜잭션을 아래와 같이 2가지 형태로 구분합니다.
1. 로컬 트랜잭션(SQL Transaction)
2. 분산 트랜잭션(DTC Transaction)

1. 로컬 트랜잭션을 세부화 하면 아래와 같이 나눌 수 있습니다.
1.1. TM(Transaction Manager)이 관여하는 SQL Transaction
1.2 TM이 관여하지 않는 SQL Transaction

개념적인 측면에서는 TM의 관여 여부로 나눌 수 있으며 개발적인 측면에서는 프로그램에서 트랜잭션을 관리하는지 DB에서 즉 SP에서 트랜잭션을 관리 여부로 나눌 수 있습니다.

다시 노란색 부분을 보면 SP에서 트랜잭션을 관리하는 부분들이 프로그램에서 트랜잭션을 관리하는 것과 합쳐졌을 경우에 트랜잭션 관리(Connection, IsolationLevel)에 대한 궁금증이였습니다.

프로그램에서 트랜잭션 관리를 하는 방법은 아래와 같습니다.
1. ADO.NET(System.Data.SqlClient.SqlConnection)를 사용하여 직접 BeginTransaction()를 호출하여 사용
2. System.Transactions.TransactionScope를 사용

트랜잭션을 관리하는 1번, 2번 방법의 공통점은 프로그램에서 트랜잭션을 관리하기 때문에 TM이 트랜잭션을  관리한다는 점입니다. 차이점이 바로 이 글의 핵심이 되는 답변으로 IsolationLevel과 관련되어 있습니다. TransactionScope에 IsolationLevel을 지정하여 사용할 때 SP의 IsolationLevel을 변경할 수 없다는 것입니다. IsolationLevel에 대한 위의 네임스페이스를 살펴보면 아래와 같습니다.

1. System.Data.SqlClient.SqlConnection - System.Data.IsolationLevel
2. System.Transactions.TransactionScope - System.Transactions.IsolationLevel

조심스럽게 예측해보면 TransactionScope의 트랜잭션은 DB 트랜잭션이라기보다는 프로그램에서 발생하는 트랜잭션(여기서는 DB 트랜잭션을 포함하는 상위의 트랜잭션의 개념으로 이해)이라고 할 수 있고 IsolationLevel은 트랜잭션 사이의 격리성이기 때문에 프로그램에서 발생하는 트랜잭션에 대한 격리성이지 DB 트랜잭션에 대한 격리성이 아니기 때문에 DB(SP) IsolationLevel을 변경할 수 없는 것입니다.

결론을 맺으면 프로그램에서 DB(SP) 트랜잭션 관리(IsolationLevel)를 하고 싶으면 SqlConnection을 사용하여 직접 트랜잭션을 발생(BeginTransaction())시켜야하며 TransactionScope을 통해서는 DB(SP) IsolationLevel까지는 관리할 수 없다는 것입니다.

Posted by resisa
,