SELECT 에 의한 DeadLock 발생

안녕하세요. 금일 발생하였던 문제에 대해서 공부를 할겸해서 포스트를 작성합니다.

본 내용은 이스트럭(강동운님)님의 SQLER 글에서 대부분 내용을 참고하였으며, 몇가지 설명과 그림을 붙였습니다.

오늘 모니터링 중에 데드락이 발생 한 SQL Server Error 로그가 있어서, 데드락 그랩을 잡아두고, 확인을 하였습니다. 

상황을 들어보니 개발자분이 실서버에 데이터를 확인하기 위해 많은 수의 Row를 SELECT하면서 with(nolock)을 빼고 계속해서 반복적으로 수행하였고, 

이때 UPDATE문이 있는 프로시저가 발동하면서 데드락이 발생하였습니다.

다행히 UPDATE되는 프로시저가 살고, SELECT가 죽어서 데이터를 이상이 없었습니다.

그런데, 저의 지금까지의 경험으로는 보통 데드락은 UPDATE <-> UPDATE간의 충돌로 알고있었습니다.

그런데 이 경우 SELECT <-> UPDATE간의 충돌이였습니다. 그래서 찾던 도중에 강동운님의 글을 발견하여, 공부하여 정리 할 겸 포스트를 작성 합니다. 

 

제가 이해한 데드락이 발생한 원인은 한쪽(UPDATE)는 인덱스 페이지를 수정하기 위해 잠금(X Lock)을 걸고 한쪽(SELECT)은 데이터를 가져오기 위해 잠금(S Lock)을 걸게 되면서 서로 교착이 발생하면서 데드락이 발생하는 원리 입니다. 

테스트를 위해서 테이블을 한개 생성 하겠습니다. SELECT 관련 데드락을 확인 하기에 앞서 일반적인 데드락을 테스트 해봤습니다.

-- 테이블 생성 CREATE TABLE DBO.Dead_Lock (  CL_Col                             INT ,NC_Col                             INT ,CreateDate                        DATETIME ) GO   -- 인덱스 생성 CREATE UNIQUE CLUSTERED INDEX CL_Dead_Lock_1 ON dbo.Dead_Lock(CL_Col) GO CREATE UNIQUE INDEX NC_Dead_Lock_2 ON dbo.Dead_Lock(NC_Col) GO   -- 데이터 삽입 SET NOCOUNT ON GO DECLARE @i INT = 1 WHILE(@i < 100001) BEGIN     INSERT INTO dbo.Dead_Lock VALUES(@i,@i,GETDATE())     SET @i = @i + 1 END GO SET NOCOUNT OFF GO   -- 건수 확인 SELECT COUNT(*) FROM dbo.Dead_Lock

일반적인 데드락은 다음의 스크립트 통해서 확인 할 수 있습니다. 

--//1번 세션에서 수행 BEGIN TRAN UPDATE dbo.Dead_Lock SET Createdate = GETDATE() WHERE CL_Col = 1 WAITFOR DELAY '0:0:5' UPDATE dbo.Dead_Lock SET Createdate = GETDATE() WHERE CL_Col = 2      --//2번 세션에서 수행: 1번과 동시에 수행 BEGIN TRAN UPDATE dbo.Dead_Lock SET Createdate = GETDATE() WHERE CL_Col = 2 WAITFOR DELAY '0:0:5' UPDATE dbo.Dead_Lock SET Createdate = GETDATE() WHERE CL_Col = 1

아래의 그림을 보면, 54번 세션(1번 세션)은 정상으로 처리되었고, 56번 세션(2번 세션)은 취소 되었습니다. 충돌이 발생하여, 데드락이 발생하였고, 이중에 56 세션번이 죽었습니다.

즉, 한쪽만 수행되었습니다. 54번 세션이 처음에 실행 할때 UPDATE를 위해 X Lock을 발동시킵니다. CL_Col = 1 은 X Lock이 잘 걸렸으나 잠시 후에 CL_Col = 2 를 X Lock을 걸려하니 56번 세션에서 이미 CL_Col = 2에 X Lock으로 점유하고 있습니다. 이렇게 해서 서로 순환 교착 상태를 만들다 보니, 데드락이 발생합니다.

l  순환 교착(cycle DeadLock) : 서로 다른 개체를 차단할 때 발생

위의 일반적인 데드락은 순환 교착 상태에서 발생하는 상황입니다. 하지만 SELECT <-> UPDATE의 경우 변환 교착 상태에서 발생합니다. 

l  변환 교착(Conversion DeadLock) : 같은 대상에 대해 둘 이상의 세션이 동시에 잠금을 변경하려고 할 때발생

다음의 쿼리를 통해서 테스트를 해보면 다음과 같습니다. 각 스크립트는 세션 창을 2개 열어서 각각 실행하셔야 합니다.

-- @endDate 은 현재시간보다 높게 설정 (계속 돌아가는 시간을 뜻함) -- 꼭 set nocount on을 해주세요. PC 습니다. SET NOCOUNT ON --1번 세션에서 수행 DECLARE @nowDate DATETIME, @endDate DATETIME SET @nowDate = GETDATE() SET @endDate = CONVERT(DATETIME,'2015-10-28 17:01.000') WHILE(1=1) BEGIN SET @nowDate = (SELECT GETDATE())  IF(@nowDate = @endDate)  BEGIN   UPDATE dbo.Dead_Lock   SET NC_Col = 100000001   , CreateDate = GETDATE()   WHERE CL_Col = 1   BREAK;  END END SET NOCOUNT OFF go   SET NOCOUNT ON --2번 세션에서 수행 DECLARE @nowDate DATETIME, @endDate DATETIME SET @nowDate = GETDATE() SET @endDate = CONVERT(DATETIME,'2015-10-28 17:05.000') WHILE(1=1) BEGIN SET @nowDate = (SELECT GETDATE())  IF(@nowDate = @endDate)  BEGIN   SELECT * FROM dbo.Dead_Lock WHERE NC_Col = 1   BREAK;  END END SET NOCOUNT OFF

쿼리를 돌리면, 다음과 같은 내용과 함께 SELECT 문이 실패한 데드락이 발생합니다.

데드락 그랩으로 잡았을 때 다음과 같은 상황을 볼 수 있습니다. 

이것을 그림으로 구성하면 다음과 같습니다. 이것을 쉽게 이해하려면 실행계획을 함께 보면 좋습니다.

2개의 트랜잭션 (세션1,세션2)가 서로 인덱스 페이지를 점유하고자 할때 생기는 변환 교착에 의해서 발생하는 데드락 입니다.

위의 그림을 설명하면, 우선 UPDATE를 하는 쿼리를 보면 UPDATE시에 NC_Col을 업데이트하고 있습니다. 

이렇게 되면, CLUSTERED 인덱스를 수정하고, 그 후에 NON CLUSTERED 인덱스를 수정합니다. 하지만 2번 트랜잭션 (2번 세션)은 SELECT 구문을 보시면 NON CLUSTERED 인덱스를 조회하고 하고 있습니다.  NON CLUSTERED 인덱스 SEEK 이후에 Key Lockup을 통해서 CLUSTERED 인덱스를 SEEK합니다. 

그 과정에서 1번 세션은 CLUSTERED 인덱스를 수정하기 위해서 해당 ROW에 X Lock을 걸게 됩니다. 이와 같이 2번 세션은 데이터를 SELECT하기 위해서 NON CLUSTERED 인덱스를 SEEK하려 시도 하지만 1번 세션이 X Lock을 걸고 있기에 데드락이 발생합니다. X Lock은 아무런 잠금과도 공유하지 않기에 S Lock을 하려고해도 공유하지 않습니다. 그러므로 데드락이 일어납니다. 음... 쉽게 말하면, 화장실은 1개인데 2명이 동시에 똥마려워서 싸우다가 1명이 이겨서 1명이 들어가는(?) 상황이죠. 

위의 순환 교착은 화장실을 1번 세션이 점유하고 있어서, 2번 세션이 바지에 싸는 상황이라고 생각하시면 쉽게 이해되실거 같습니다.

대부분이 SQLER에서 가져온 내용이라, 아래의 참조링크 글을 보셔도 무방 할 듯 합니다 ^^

감사합니다.

참조링크 : http://www.sqler.com/543496   - SELECT 데드락 관련 글

  http://www.sqler.com/365899   - 트랜잭션 격리수준

   http://www.sqler.com/589044   - 데드락 모니터링