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은 읽기 작업을 위해 행 수준 또는 페이지 수준에서만 업데이트 잠금을 사용합니다. |
테이블 힌트가 생각보다 많고 정확하게 알고 쓰지 않으면 부작용으로 고생을 할 수 있습니다;;
참고 : 위의 글에서 많은 힌트를 아래와 같이 분류해주고 있습니다.
SQL Server는 FROM 절의 각 테이블에 대해 다음 각 그룹에서 두 개 이상의 테이블 힌트를 허용하지 않습니다.
|
그럼 DeadLock과 관련된 예제를 살펴보겠습니다.
-- 1번 세션 -- 실행1 BEGIN TRAN DECLARE @Normal bigint = 0 SELECT @Normal=Normal+1 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+1 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편이 더 나은 것?! 같아 대충 만족하며 마무리 하려고 합니다.
