데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

SELECT 가 Deadlock?

이스트럭(강동운) 2013.02.16 04:46 Views : 11693

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


오랬만에 글을 쓰는군요.. ^^;; 그간 mongodb 하느라고 잠시 SQL Server를 놨다가.. 다시 돌아왔습니다!


바빠서 SQLER 눈팅만 했네요 ^^;; 앞으로 글도 많이 올리도록 하겠습니다.



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


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

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

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

CreateDate: 생성일



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
--//테이블 삭제 스크립트
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--//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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--//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이 발생하고 있습니다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
--// 아래 @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 --// DEADLOCK이 발생하지 않으면 UNCIDX => NCLIDX로 변경해보세요!
  , 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

No. Subject Author Date Views
1533 SSIS - 검사점 jevida(강성욱) 2013.04.19 8352
1532 SSIS - 패키지 구성 [1] jevida(강성욱) 2013.04.19 10120
1531 SSIS - 프로세스 실행 태스크 [2] jevida(강성욱) 2013.04.19 10349
1530 SSIS - 변수 – 다양한 사용자 변수를 만들어 보자 [3] jevida(강성욱) 2013.04.19 10802
1529 SQL Server Version별 Sample Database Download 경로 [2] 쓸만한게없네(윤선식) 2013.04.17 6810
1528 InstrCount함수와 InstrCountRev함수를 한번 만들어봤습니다. [5] Light 2013.03.27 6094
1527 구분자에 의해 구분되어 반환되는 split함수가 없어서 한번 만들어 봤습니다. [2] Light 2013.03.27 7537
1526 Microsoft.ACE.OLEDB.12.0 을 이용한 엑셀 2010 직접쿼리하기 [3] 열이 2013.03.20 42137
1525 Online restore [1] jevida(강성욱) 2013.03.15 6308
1524 SQL Server User Connections [1] jevida(강성욱) 2013.03.15 10597
1523 스크립트 구성 요소 – [데이터 대상] 사용 [1] jevida(강성욱) 2013.03.13 6763
1522 스크립트 구성 요소 – [데이터 변환] 사용 jevida(강성욱) 2013.03.13 6128
1521 SSIS - 스크립트 구성 요소 – [데이터 원본] 사용 jevida(강성욱) 2013.03.12 7832
1520 SSIS - 피벗 해제 변환 - 피봇된 데이터를 테이블 형태로 변환 하자! jevida(강성욱) 2013.03.12 7199
1519 SQL Server 단일 사용자 설정 시 SSMS로 로그인하기 [1] 쓸만한게없네(윤선식) 2013.02.25 12284
» SELECT 가 Deadlock? [2] 이스트럭(강동운) 2013.02.16 11693
1517 한글 초성 관련 [4] 건우아빠 2012.12.26 13133
1516 .NET 4.5 (VS2012) 설치 후 SSMS 에서 원격 MSSQL 2008 등이 접속 안될 경우! [2] 컴포지트 2012.12.11 17398
1515 SQL프로시저들의 매개변수를 한번에 샥 보여줍니다 [3] 차주언 2012.12.10 9268
1514 SSIS - 피봇 변환 - 데이터를 행렬 변환 하자! jevida(강성욱) 2012.11.30 10004





XE Login