SELECT 가 Deadlock?

안녕하세요. 이스트럭(강동운) 입니다.

SELECT 쿼리인데 deadlock이 왜! 라는 주제를 가지고 오늘 이야기를 풀어보도록 하겠습니다.

예제 테이블은 test라는 테이블이며, 컬럼은 총 3개가 있습니다.

UCLIDX: int 형이며, 유니크한 클러스터드 인덱스 입니다.

UNCIDX: int 형이며, 유니크한 넌클러스터드 인덱스 입니다.

CreateDate: 생성일

--//테이블 삭제 스크립트
IF OBJECT_ID('dbo.test','U') IS NOT NULL
	DROP TABLE dbo.test
GO

--//테이블 생성 스크립트
CREATE TABLE dbo.test
(
	UCLIDX            INT		NOT NULL
,	UNCIDX            INT		NOT NULL
,	CreateDate        DATETIME	NOT NULL
)
GO

--//인덱스 생성 스크립트
CREATE UNIQUE CLUSTERED INDEX CL_TEST_UCLIDX ON dbo.test(UCLIDX)
GO
CREATE UNIQUE INDEX NC_TEST_UNCIDX ON dbo.test(UNCIDX)
GO

--//10,000개 데이터 입력
--//데이터 입력
SET NOCOUNT ON
GO
DECLARE @i INT = 1
WHILE(@i < 100000)
BEGIN
	INSERT INTO dbo.test VALUES(@i,@i,GETDATE())
	SET @i = @i + 1
END
GO
SET NOCOUNT OFF
GO

Unique Clustered Index 의 UCLIDX와 Unique Non-Clustered Index UNCIDX 를 같은 값으로 넣었습니다.

일반적으로 우리가 책에서 접하는 데드락은... 아래와 같은 예제일 것입니다.

--//1번 세션에서 수행
BEGIN TRAN
UPDATE dbo.test SET Createdate = GETDATE() WHERE UCLIDX = 1

WAITFOR DELAY '0:0:5'  

UPDATE dbo.test SET Createdate = GETDATE() WHERE UCLIDX = 2


--//2번 세션에서 수행: 1번과 동시에 수행

BEGIN TRAN
UPDATE dbo.test SET Createdate = GETDATE() WHERE UCLIDX = 2

WAITFOR DELAY '0:0:5'  

UPDATE dbo.test SET Createdate = GETDATE() WHERE UCLIDX = 1

이렇게.. 1번 세션은 UCLIDX = 1을 업데이트 하는 X Lock을 잡고 있고, 2번 세션은 UCLIDX = 2를 업데이트 하고..

5초후에 서로 순환 교착상태를 만들면서 데드락 상황에 빠지게 되는 것이죠.

이는 위에서 설명드렸다 싶이 ... 데드락 파트에서 대부분의 책들이 위와 흡사한 예제를 보이고 있습니다.

이번에는 일반적인 순환교착이 아닌.. 변환 교착에 대해서 살펴보도록 하겠습니다.

우선 이 예제는.. 트랜잭션 격리수준 중에서.. REPEATABLE READ가 사용되었고, REPEATABLE READ와 READ COMMITTED의 차이 점은..

BEGIN TRAN 안에 있는 SELECT 절이 COMMIT 되기전까지 Shard Lock을 해제하지 않는 다는 차이가 있습니다.

(트랜 잭션 격리수준을 잘 모르시는 분은 http://www.sqler.com/365899 여기 참고하세요!)

따라서 아래 예제는.. BEGIN TRAN 안에 있는 SELECT 절이.. SELECT 가 끝났어도 계속 Shard Lock을 유지하고 있는 것이죠.

 

--//1번 세션에서 수행
BEGIN TRAN
SELECT CreateDate FROM dbo.test WITH(REPEATABLEREAD) WHERE UCLIDX = 10

WAITFOR DELAY '0:0:5'  

UPDATE dbo.test SET Createdate = GETDATE() WHERE UCLIDX = 10


--//2번 세션에서 수행: 1번과 동시에 수행
BEGIN TRAN
SELECT CreateDate FROM dbo.test WITH(REPEATABLEREAD) WHERE UCLIDX = 10

WAITFOR DELAY '0:0:5'  

UPDATE dbo.test SET Createdate = GETDATE() WHERE UCLIDX = 10

이 경우는... BEGIN TRAN 안에있는 REPETABLE READ 격리수준인 test 테이블의 UCLIDX = 10번 값이..

1번과 2번 모두 s lock을 잡고 있기 때문에..서로 놓아주지 않아서 update 가 불가능하게 되는 데드락에 빠지는 것이죠.

오늘 얘기하고자 하는 내용은 사실 여기부터 시작이라고 보실 수 있습니다.

그리고.. 변환 교착에 대해서도 이해를 하셔야 아래 내용을 이애하실 수 있습니다.

아래 예제를 보시면.. UPDATE문 한개와 SELECT문 한개가 있는데 deadlock이 발생하고 있습니다.

--// 아래 @endDate의 시간은 현재보다 미래로 맞춰서 수행하시길 바랍니다. +_+
--// IF문 아래 문장이 동시에 수행이 되어야 하기 때문에.... 이렇게 구성한 것이고,
--// PC가 느린경우 IF문을 탈 수 없어서 수행이 안될 수도 있습니다.(저희 집이 그렇더군요 ㅠㅠ)

--1번 세션에서 수행
DECLARE @nowDate DATETIME, @endDate DATETIME
SET @nowDate = GETDATE()
SET @endDate = CONVERT(DATETIME,'2013-02-16 02:20.000')
WHILE(1=1)
BEGIN
SET @nowDate = (SELECT GETDATE())
 IF(@nowDate = @endDate)
 BEGIN
  UPDATE dbo.TEST
  SET UNCIDX = 100000001
  , CreateDate = GETDATE()
  WHERE UCLIDX = 1
  BREAK;
 END
END


--2번 세션에서 수행
DECLARE @nowDate DATETIME, @endDate DATETIME
SET @nowDate = GETDATE()
SET @endDate = CONVERT(DATETIME,'2013-02-16 02:20.000')
WHILE(1=1)
BEGIN
SET @nowDate = (SELECT GETDATE())
 IF(@nowDate = @endDate)
 BEGIN
  SELECT * FROM dbo.test WHERE UNCIDX = 1
  BREAK;
 END 
END

1번 세션에서 수행된 것을 보면..Unique Clustered Index Key인.. UCLIDX 값으로 Clustered Index Seek 한 값을..

UNCIDX 와 CreateDate를 업데이트 하는 구문이 있고,

1번 실행계획

2번 세션에서 수행된 것을 보면.. Non Clustered Index Seek를 하며, Key Lookup을 하고 있습니다.

2번 실행계획

왜! SELECT 구문인데도.. DEADLOCK이 발생을 하는지 설명을 드리도록 하겠습니다.

1번 세션에 있는 업데이트 구문은.. 넌클러스터드 인덱스를 수정하고 있습니다.

따라서.. 클러스터드 인덱스에 있는 UNCIDX 값이 먼저 수정되고 나서.. 넌 클러스터드 인덱스가 수정되게 됩니다.

2번 세션에 있는 SELECT 구문은.. 넌클러스터드 인덱스를 Seek 하고나서.. Key Lookup을 통해 Clustered Index Seek를 하는 것이죠!

1번 세션은 클러스터드 인덱스를 수정하기 위해.. 해당 row에 X lock을 걸게 되며.. 동시에..

2번 세션은 데이터를 가져오기 위해 넌클러스터드 인덱스의 해당 row에 S lock을 걸게 됩니다.

이때, 1번 세션은 넌클러스터드 인덱스를 수정하기 위해 X Lock을 걸려고 하지만, 이미.. 2번 세션이 S Lock을 걸고 있기 때문에 기다리게 됩니다.

마찬가지로 2번 세션또한 Key Lookup을 하기 위해 Clustered Index Seek를 하려고 하지만.. 이미 1번 세션이 X Lock을 걸고 있기 때문에 데드락이 발생하게 됩니다.

이 문제를 해결하기 위해서는 여러가지 방법이 있습니다.

첫째, 2번 세션의 SELECT 쿼리에 WITH(NOLOCK)으로 s lock을 걸지 않도록 수정한다.

둘째, 2번 세션의 SELECT 쿼리를 Key lookup이 수행되지 않도록 한다.(인덱스 키를 변경하던지, INCLUDE INDEX를 활용하던지 조건절은 클러스터드 인덱스로 변경 등등의 방법이 있겠죠)

셋째, 이런 쿼리를 동시간에 수행되지 않도록 한다.

마지막으로 deadlock에 대한 상세 로그를 보시기 위해서는.. 추적플래그 1204를 활용하세요.

DBCC TRACEON(1204)

데드락이 생긴 로그는.. SQL Server ErrorLog에 남습니다.

EXEC sp_readerrorlog

감사합니다. ^^

혹시.. sp를 동시에 수행되기 위해 저런 무식한 방법말고 더 좋은 방법을 알고 계신분이 있으면 꼭 좀 알려주세요 +_+..

작성자: 이스트럭(강동운)

작성일: 2013-02-16

참고 URL

http://kuaaan.tistory.com/100

http://kuaaan.tistory.com/52