MS-SQL Server

교착상태 (DeadLock) - 실전편

알 수 없는 사용자 2011. 4. 19. 01:24

실제 교착 상태가 발생 하였을 때 어떻게 해결을 해나가는지, 원인을 분석하기 위한 방법과 사례를 공유합니다. 

교착 상태가 무엇 인지에 대한 포스팅은 http://nexondbteam.tistory.com/66 에 게시되어 있습니다.

 

데드락을 인지하고, 원인 분석을 위해 아래 3가지 방법을 사용했습니다.

Perfmon

우리 팀의 경우 내부에서 개발한 Perfmon 로그를 UI를 제공하고 alert을 주는 툴을 통해 하여 매일 상황을 모니터링을 하고 있으므로, 필자는 거의 이 Perfmon로그로 발생 여부를 인지합니다.

 

성능 카운터명: SQLServer:LOCKs -> Number of Deadlocks/sec




ERRORLOG

DBCC TRACEON(1222, 1204, -1)

 

추적플래그 1204는 잠금의 유형과 리소스에 대해 간단설명이 나옵니다

좀 더 자세한 정보를 얻으려면 1222번이 추적을 추가해주세요.

 

 

SQL Server Profiler

추적 탬플릿의 이벤트 -> LOCKS -> Deadlock graph 만 추가해도 충분한 정보를 얻을 수 있으며,

추적 열의 TextData XML 데이터는 위 ERRORLOG의 추적플래그 1222번에서 출력되는 정보의 대부분을 담고 있습니다.


 



참고 URL: http://cafe.naver.com/sqlmvp/465

 






사례 1

INCLUDE INDEX 의 키가 아닌 포괄열에 포함된 컬럼에 따라 데드락 발생 될 수 있습니다.!

 

소스

--초기화

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tit_table_includeindex]') AND name = N'tii_IDX')

DROP INDEX [tii_IDX] ON [dbo].[tit_table_includeindex] WITH ( ONLINE = OFF )

go

IF OBJECT_ID('dbo.tit_table_includeindex') IS NOT NULL

DROP TABLE dbo.tit_table_includeindex

go

IF OBJECT_ID('dbo.tip_table_includeindex_update') IS NOT NULL

DROP PROC dbo.tip_table_includeindex_update

go

IF OBJECT_ID('dbo.tip_table_includeindex_select') IS NOT NULL

DROP PROC dbo.tip_table_includeindex_select

go

 

--테스트테이블생성

CREATE TABLE dbo.tit_table_includeindex

(

idx                int

,name            varchar(15)

,nickname    varchar(15)

,registdate    datetime

)

go

 

--인덱스추가

CREATE UNIQUE NONCLUSTERED INDEX [tii_IDX] ON dbo.tit_table_includeindex

(

idx

)

INCLUDE (

name

,nickname

)

go

 

--데이터넣기

INSERT INTO dbo.tit_table_includeindex values(1, 'name01', 'nickname01', GETDATE())

INSERT INTO dbo.tit_table_includeindex values(2, 'name02', 'nickname02', GETDATE())

INSERT INTO dbo.tit_table_includeindex values(3, 'name03', 'nickname03', GETDATE())

go

 

--UPDATE sp 생성

CREATE PROC dbo.tip_table_includeindex_update

@idx INT

as

UPDATE tit_table_includeindex

SET registdate = GETDATE()

, nickname = 'nickname11'

, name = 'name11'

WHERE idx = @idx

 

UPDATE tit_table_includeindex

SET registdate = GETDATE()

, nickname = 'nickname01'

, name = 'name01'

WHERE idx = @idx

go

 

--SELECT sp 생성

CREATE PROC dbo.tip_table_includeindex_select

@idx INT

as

SELECT  name, nickname, registdate

FROM tit_table_includeindex

WHERE idx = @idx

 

--실행: 서로 다른 세션에서 실행하시면 됩니다.

--@@spid = 53

SET NOCOUNT ON

 

IF OBJECT_ID('tempdb.dbo.#T1') IS NOT NULL

DROP TABLE #T1

CREATE TABLE #T1

(   

name            varchar(15)

,nickname    varchar(15)

,registdate    datetime

)

GO

 

WHILE(1=1)

BEGIN

INSERT INTO #T1

EXEC tip_table_includeindex_select 1

TRUNCATE TABLE #T1

END


 

--@@spid = 54

SET NOCOUNT ON

 

WHILE(1=1)

BEGIN

EXEC tip_table_includeindex_update 1

END





결과

spid 53 54사이의 교착상황이 발생하였고, 상대적으로 비용이 적은 53이 희생되었습니다.


이 시점의 blocking정보를 살펴 보면

spid 53 RID: 17:1:218:0 S를 획득하려고 이 전에 54에 의해 요청된 X 가 종료될 때까지 대기 중이고,

spid 54 KEY: 17:72057594041466880를 업데이트하기 위해 먼저 U를 획득한 뒤 X로 변환이 되어야 하지만 53 S를 획득한 상태이므로 대기 중입니다.

 spid  status  blocked  open_tran  waitresource  waittype  waittime  cmd  lastwaittype  cpu  physical_io
53 suspended 54 1 RID: 17:1:218:0 0x0003 333 SELECT LCK_M_S 266574 10
54 suspended 53 2 KEY: 17:72057594041466880
(010086470766)
0x0005 333 UPDATE LCK_M_X 323951 135

spid

ObjId

IndId

Type

Resource

Mode

Status

54

485576768

0

RID

1:218:0        

X

GRANT

53

485576768

0

RID

1:218:0        

S

WAIT

54

485576768

3

KEY

(010086470766) 

U

GRANT

53

485576768

3

KEY

(010086470766) 

S

GRANT

54

485576768

3

KEY

(010086470766) 

X

CNVT





해결방안

1.     격리 수준 조정: spid 53 select 구문에 with(nolock)을 추가하거나  SET TRANSACTION ISOLATION READ UNCOMMITTED/SNAPSHOT 로 격리수준을 조정하면 데드락이 발생하지 않습니다.

2.     인덱스 조정: 53 select 컬럼과 include index의 포괄열 컬럼을 맞추어 RID lookup을 하지 않는 다면 교착 상황을 피할 수 있습니다.

 

이 사례는 INCLUDE INDEX일 경우 포괄열을 update, select 하면서 교착 상태가 발생하였습니다. 

sp_index 결과로는 이게 INCLUDE INDEX 인지 알 수가 없고, 스크립트를 뜨거나 DMV로 어떤 컬럼 들이 포괄 열에 포함되었는지를 알 수 있습니다.

운영자 입장에서 내가 생성한 테이블이 아니라면 보통 sp_help로 테이블의 정보를 파악하였는데 앞으로는 좀 더 꼼꼼히 봐야겠습니다 .^^

 

대상 테이블의 sp_help 정보

Name

Owner

Type

Created_datetime

tit_table_includeindex

dbo

user table

2011-04-16 16:06:58.927


index_name

index_description

index_keys

tii_IDX

nonclustered, unique located on PRIMARY

idx


상세 인덱스 정보 보기

select object_name(a.object_id) tablename, a.name indexname, a.type_desc, a.is_unique, col_name(b.object_id, b.column_id) column_name, b.key_ordinal, b.is_included_column

from sys.indexes a

inner join sys.index_columns b on a.object_id = b.object_id and a.index_id = b.index_id

where a.object_id = object_id('tit_table_includeindex')