실제 교착 상태가 발생 하였을 때 어떻게 해결을 해나가는지, 원인을 분석하기 위한 방법과 사례를 공유합니다.
교착 상태가 무엇 인지에 대한 포스팅은 http://nexondbteam.tistory.com/66 에 게시되어 있습니다.
Perfmon
우리 팀의 경우 내부에서 개발한 Perfmon 로그를 UI를 제공하고 alert을 주는 툴을 통해 하여 매일 상황을 모니터링을 하고 있으므로, 필자는 거의 이 Perfmon로그로 발생 여부를 인지합니다.
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로 테이블의 정보를 파악하였는데 앞으로는 좀 더 꼼꼼히 봐야겠습니다 .^^
|
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