-- Title : [2k5] SQL 2005 프로파일러에서 데드락 그래프 보기
-- Reference : sqlworld.pe.kr
-- Key word : dead lock graph profiler
**********************************************************************************************/
/*
-- 1. 프로파일러 이벤트 선택 및 실행
*/
/*
-- 2. 데드락 발생
*/
USE tempdb;
GO
DROP TABLE t1, t2;
GO
CREATE TABLE dbo.T1 (
keycol INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL,
col2 VARCHAR(50) NOT NULL
);
INSERT INTO dbo.T1(keycol, col1, col2) VALUES(1, 101, 'A');
INSERT INTO dbo.T1(keycol, col1, col2) VALUES(2, 102, 'B');
INSERT INTO dbo.T1(keycol, col1, col2) VALUES(3, 103, 'C');
GO
CREATE TABLE dbo.T2 (
keycol INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL,
col2 VARCHAR(50) NOT NULL
);
INSERT INTO dbo.T2(keycol, col1, col2) VALUES(1, 201, 'X');
INSERT INTO dbo.T2(keycol, col1, col2) VALUES(2, 202, 'Y');
INSERT INTO dbo.T2(keycol, col1, col2) VALUES(3, 203, 'Z');
GO
/* 간단한 교착 상태 구현 */
-- Connection 1
BEGIN TRAN
UPDATE dbo.T1 SET col1 = col1 + 1 WHERE keycol = 2
-- Connection 2
BEGIN TRAN
UPDATE dbo.T2 SET col1 = col1 + 1 WHERE keycol = 2
-- Connection 1
SELECT col1 FROM dbo.T2 WHERE keycol = 2
COMMIT TRAN
-- Connection 2
SELECT col1 FROM dbo.T1 WHERE keycol = 2
COMMIT TRAN
/*
-- 3. 프로파일러에서 Deadlock graph 선택 및 확인
*/