DeadLock 예제,재 실행하기

Database Administrator/Peformance Tuning

DeadLock 예제,재 실행하기

ceusee 2009. 11. 24. 17:50

Lock::Trace Flag 1204

DeadLock 발생 원인 찾기

by ceusee | 2009/07/20 17:17

Deadlock 발생시 원인과 해결법. 1. Deadlock 이유를 알고 싶으면 trace 1204 를 켜 준다. DBCC traceon(1204,-1) DBCC Tracestatus(...

deadlock이 자주 발생하는 쿼리가 있다면, 해당 원인을 찾아야 한다.

이때 T 1204를와 T 1222를 켜줘서 실행 확인이 가능하다.

1204만을 적용되었을 때는 dbcc page 번호를 통해 발생원인이 되는 객체와 인덱스를 찾으면 된다.

몇번의 deadlock이 발생후 멈추는거라면 쿼리를 반복 실행해서 원하는 결과를 얻게 할 수 있다.

이때

TRY ... CATCH 문을 이용하고 ERROR_NUMBER() = 1205를 사용해서 처리 가능핟.

** DeadLock 발생 예제 **

1. 필요한 객체 생성

[code sql]
USE AdventureWorks;
GO

-- Verify that the table does not exist.
IF OBJECT_ID (N'my_sales',N'U') IS NOT NULL
    DROP TABLE my_sales;
GO

-- Create and populate the table for deadlock simulation.
CREATE TABLE my_sales 
    (
    itemid       INT PRIMARY KEY,
    sales        INT not null
    );
GO

INSERT my_sales (itemid, sales) VALUES (1, 1);
INSERT my_sales (itemid, sales) VALUES (2, 1);
GO

-- Create a stored procedure for printing error information.
CREATE PROCEDURE usp_MyErrorLog
AS
    PRINT 
        'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
        ', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
        ', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) + 
        ', Line ' + CONVERT(VARCHAR(5), ERROR_LINE());
    PRINT 
        ERROR_MESSAGE();
GO
[/code]

2. session # 1, #2에 동시에 해당 쿼리를 실행한다.

#1을 먼저 실행하게되면, #2에서 deadlock가 발생하며, 재시도 처리하고

그후 원하는 내용으로 update 된다.

[code sql]

USE AdventureWorks;
GO

-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
DECLARE @count INT;
SET @retry = 2;
SET @count = 0

-- Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- victim.
WHILE (@retry > 0)
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 1;

        WAITFOR DELAY '00:00:8';
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 2;

        SET @retry = 0;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH 
        -- Check error number.
        -- If deadlock victim error,
        -- then reduce retry count
        -- for next update retry. 
        -- If some other error
        -- occurred, then exit
        -- retry WHILE loop.
        IF (ERROR_NUMBER() = 1205)
        BEGIN
            SET @count = @count + 1;
            SET @retry = @retry - 1;
            SELECT @count as '[재 실행count]',  ERROR_MESSAGE()
            
        END
        ELSE
            SET @retry = -1;

        -- Print error information.
        EXECUTE usp_MyErrorLog;
  
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    END CATCH;
END; -- End WHILE loop.
SELECT @count as '[총 실행count]'
GO

[/code]

deadlock이 발생했을 경우 errorlog 기록된 내역

2009-11-24 17:30:10.780 spid4s Wait-for graph
2009-11-24 17:30:10.780 spid4s NULL
2009-11-24 17:30:10.780 spid4s Node:1
2009-11-24 17:30:10.780 spid4s KEY: 10:72057594054639616 (020068e8b274) CleanCnt:3 Mode:X Flags: 0x0
2009-11-24 17:30:10.780 spid4s  Grant List 1:
2009-11-24 17:30:10.780 spid4s    Owner:0x054AD9A0 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x0962185C
2009-11-24 17:30:10.780 spid4s    SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 24
2009-11-24 17:30:10.780 spid4s    Input Buf: Language Event: 
-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
DECLARE @count INT;
SET @retry = 2;
SET @count = 1;
--Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- vict
2009-11-24 17:30:10.780 spid4s  Requested By: 
2009-11-24 17:30:10.780 spid4s    ResType:LockOwner Stype:'OR'Xdes:0x09621290 Mode: X SPID:59 BatchID:0 ECID:0 TaskProxy:(0x097F8378) Value:0x54ae760 Cost:(0/208)
2009-11-24 17:30:10.780 spid4s NULL
2009-11-24 17:30:10.780 spid4s Node:2
2009-11-24 17:30:10.780 spid4s KEY: 10:72057594054639616 (010086470766) CleanCnt:2 Mode:X Flags: 0x0
2009-11-24 17:30:10.780 spid4s  Grant List 1:
2009-11-24 17:30:10.780 spid4s    Owner:0x054AF040 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x096212B4
2009-11-24 17:30:10.780 spid4s    SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 23
2009-11-24 17:30:10.780 spid4s    Input Buf: Language Event: 
-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
SET @retry = 2;

-- Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- victim.
WHILE (@retry > 0)
BEGIN
   
2009-11-24 17:30:10.780 spid4s  Requested By: 
2009-11-24 17:30:10.780 spid4s    ResType:LockOwner Stype:'OR'Xdes:0x09621838 Mode: X SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0983C378) Value:0x54adc40 Cost:(0/208)
2009-11-24 17:30:10.780 spid4s NULL
2009-11-24 17:30:10.780 spid4s Victim Resource Owner:
2009-11-24 17:30:10.780 spid4s  ResType:LockOwner Stype:'OR'Xdes:0x09621838 Mode: X SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0983C378) Value:0x54adc40 Cost:(0/208)
2009-11-24 17:30:10.780 spid15s deadlock-list
2009-11-24 17:30:10.780 spid15s  deadlock victim=process2e58e38
2009-11-24 17:30:10.780 spid15s   process-list
2009-11-24 17:30:10.780 spid15s    process id=process2e58e38 taskpriority=0 logused=208 waitresource=KEY: 10:72057594054639616 (010086470766) waittime=3978 ownerId=347267 transactionname=user_transaction lasttranstarted=2009-11-24T17:30:03.803 XDES=0x9621838 lockMode=X schedulerid=2 kpid=9648 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-11-24T17:30:03.783 lastbatchcompleted=2009-11-24T17:30:03.780 clientapp=Microsoft SQL Server Management Studio - 쿼리 hostname=최보라 hostpid=8252 loginname=sa isolationlevel=read committed (2) xactid=347267 currentdb=10 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2009-11-24 17:30:10.780 spid15s     executionStack
2009-11-24 17:30:10.780 spid15s      frame procname=adhoc line=24 stmtstart=38 sqlhandle=0x020000007245e338daab90ce8e17c9bb5e80187b4d642c14
2009-11-24 17:30:10.780 spid15s UPDATE [my_sales] set [sales] = [sales]+@1  WHERE [itemid]=@2     
2009-11-24 17:30:10.780 spid15s      frame procname=adhoc line=24 stmtstart=938 stmtend=1106 sqlhandle=0x020000002ac2d80354f3e91e11b6251c8c189c5d7eccce5d
2009-11-24 17:30:10.780 spid15s UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 5
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 1;     
2009-11-24 17:30:10.780 spid15s     inputbuf
2009-11-24 17:30:10.780 spid15s -- Declare and set variable
2009-11-24 17:30:10.780 spid15s -- to track number of retries
2009-11-24 17:30:10.780 spid15s -- to try before exiting.
2009-11-24 17:30:10.780 spid15s DECLARE @retry INT;
2009-11-24 17:30:10.780 spid15s DECLARE @count INT;
2009-11-24 17:30:10.780 spid15s SET @retry = 2;
2009-11-24 17:30:10.780 spid15s SET @count = 1;
2009-11-24 17:30:10.780 spid15s --Keep trying to update 
2009-11-24 17:30:10.780 spid15s -- table if this task is 
2009-11-24 17:30:10.780 spid15s -- selected as the deadlock 
2009-11-24 17:30:10.780 spid15s -- victim.
2009-11-24 17:30:10.780 spid15s WHILE (@retry > 0)
2009-11-24 17:30:10.780 spid15s BEGIN
2009-11-24 17:30:10.780 spid15s     BEGIN TRY
2009-11-24 17:30:10.780 spid15s        BEGIN TRANSACTION;
2009-11-24 17:30:10.780 spid15s         UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 5
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 2;
2009-11-24 17:30:10.780 spid15s         WAITFOR DELAY '00:00:03';
2009-11-24 17:30:10.780 spid15s         UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 5
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 1;
2009-11-24 17:30:10.780 spid15s         SET @retry = 0;
2009-11-24 17:30:10.780 spid15s         COMMIT TRANSACTION;
2009-11-24 17:30:10.780 spid15s     END TRY
2009-11-24 17:30:10.780 spid15s     BEGIN CATCH 
2009-11-24 17:30:10.780 spid15s         -- Check error number.
2009-11-24 17:30:10.780 spid15s         -- If deadlock victim error,
2009-11-24 17:30:10.780 spid15s         -- then reduce retry count
2009-11-24 17:30:10.780 spid15s         -- for next update retry. 
2009-11-24 17:30:10.780 spid15s         -- If some other error
2009-11-24 17:30:10.780 spid15s         -- occurred, then exit
2009-11-24 17:30:10.780 spid15s         -- retry WHILE loop.
2009-11-24 17:30:10.780 spid15s         IF (ERROR_NUMBER() = 1205)
2009-11-24 17:30:10.780 spid15s         BEGIN
2009-11-24 17:30:10.780 spid15s             SET @retry = @retry - 1;
2009-11-24 17:30:10.780 spid15s             SELECT @count as '[실행count]',  ERROR_MESSAGE()
2009-11-24 17:30:10.780 spid15s    process id=process2e58f28 taskpriority=0 logused=208 waitresource=KEY: 10:72057594054639616 (020068e8b274) ownerId=347255 transactionname=user_transaction lasttranstarted=2009-11-24T17:30:02.793 XDES=0x9621290 lockMode=X schedulerid=2 kpid=6876 status=suspended spid=59 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-11-24T17:30:02.793 lastbatchcompleted=2009-11-24T17:30:02.793 clientapp=Microsoft SQL Server Management Studio - 쿼리 hostname=최보라 hostpid=8252 loginname=sa isolationlevel=read committed (2) xactid=347255 currentdb=10 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2009-11-24 17:30:10.780 spid15s     executionStack
2009-11-24 17:30:10.780 spid15s      frame procname=adhoc line=23 stmtstart=38 sqlhandle=0x020000007245e338daab90ce8e17c9bb5e80187b4d642c14
2009-11-24 17:30:10.780 spid15s UPDATE [my_sales] set [sales] = [sales]+@1  WHERE [itemid]=@2     
2009-11-24 17:30:10.780 spid15s      frame procname=adhoc line=23 stmtstart=868 stmtend=1036 sqlhandle=0x020000008a4537232ab1f574eb75390b8f81a691db149df4
2009-11-24 17:30:10.780 spid15s UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 1
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 2;     
2009-11-24 17:30:10.780 spid15s     inputbuf
2009-11-24 17:30:10.780 spid15s -- Declare and set variable
2009-11-24 17:30:10.780 spid15s -- to track number of retries
2009-11-24 17:30:10.780 spid15s -- to try before exiting.
2009-11-24 17:30:10.780 spid15s DECLARE @retry INT;
2009-11-24 17:30:10.780 spid15s SET @retry = 2;
2009-11-24 17:30:10.780 spid15s -- Keep trying to update 
2009-11-24 17:30:10.780 spid15s -- table if this task is 
2009-11-24 17:30:10.780 spid15s -- selected as the deadlock 
2009-11-24 17:30:10.780 spid15s -- victim.
2009-11-24 17:30:10.780 spid15s WHILE (@retry > 0)
2009-11-24 17:30:10.780 spid15s BEGIN
2009-11-24 17:30:10.780 spid15s     BEGIN TRY
2009-11-24 17:30:10.780 spid15s         BEGIN TRANSACTION;
2009-11-24 17:30:10.780 spid15s         UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 1
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 1;
2009-11-24 17:30:10.780 spid15s         WAITFOR DELAY '00:00:8';
2009-11-24 17:30:10.780 spid15s         UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 1
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 2;
2009-11-24 17:30:10.780 spid15s         SET @retry = 0;
2009-11-24 17:30:10.780 spid15s         COMMIT TRANSACTION;
2009-11-24 17:30:10.780 spid15s     END TRY
2009-11-24 17:30:10.780 spid15s     BEGIN CATCH 
2009-11-24 17:30:10.780 spid15s         -- Check error number.
2009-11-24 17:30:10.780 spid15s         -- If deadlock victim error,
2009-11-24 17:30:10.780 spid15s         -- then reduce retry count
2009-11-24 17:30:10.780 spid15s         -- for next update retry. 
2009-11-24 17:30:10.780 spid15s         -- If some other error
2009-11-24 17:30:10.780 spid15s         -- occurred, then exit
2009-11-24 17:30:10.780 spid15s         -- retry WHILE loop.
2009-11-24 17:30:10.780 spid15s         IF (ERROR_NUMBER() = 1205)
2009-11-24 17:30:10.780 spid15s         BEGIN
2009-11-24 17:30:10.780 spid15s             SET @retry = @retry - 1;
2009-11-24 17:30:10.780 spid15s             SELECT ERROR_MESSAGE()
2009-11-24 17:30:10.780 spid15s         END
2009-11-24 17:30:10.780 spid15s         ELSE
2009-11-24 17:30:10.780 spid15s             SET @retry = -1;
2009-11-24 17:30:10.780 spid15s   resource-list
2009-11-24 17:30:10.780 spid15s    keylock hobtid=72057594054639616 dbid=10 objectname=AdventureWorks.dbo.my_sales indexname=PK__my_sales__7CA47C3F id=lock5446f00 mode=X associatedObjectId=72057594054639616
2009-11-24 17:30:10.780 spid15s     owner-list
2009-11-24 17:30:10.780 spid15s      owner id=process2e58f28 mode=X
2009-11-24 17:30:10.780 spid15s     waiter-list
2009-11-24 17:30:10.780 spid15s      waiter id=process2e58e38 mode=X requestType=wait
2009-11-24 17:30:10.780 spid15s    keylock hobtid=72057594054639616 dbid=10 objectname=AdventureWorks.dbo.my_sales indexname=PK__my_sales__7CA47C3F id=lock5447340 mode=X associatedObjectId=72057594054639616
2009-11-24 17:30:10.780 spid15s     owner-list
2009-11-24 17:30:10.780 spid15s      owner id=process2e58e38 mode=X
2009-11-24 17:30:10.780 spid15s     waiter-list
2009-11-24 17:30:10.780 spid15s      waiter id=process2e58f28 mode=X requestType=wait

'Database Administrator > Peformance Tuning' 카테고리의 다른 글

성능::엑셀이용분석하기 (0)	2010.06.03
read-ahead는 무었인가? (0)	2009.12.03
성능:: 강제 매개변수화 Forced Parameterization (0)	2009.11.13
SQL서버 성능counter (0)	2009.11.12

현재글DeadLock 예제,재 실행하기

Bora.Choi

세상을 따뜻한 눈으로 바라보기. 시작을 두려워 하지 말기.

SQL Server 2008, RML, SQL Server 2005, 일본어초급, Reporting Service, MSsql, index, sqlserver2008, ssis, 미러링, 성능, admin, ERRORLOG, Tuning, Performance, TSQL, Trace Flag, SQLServer, 복제, T-SQL,

Today :
Yesterday :

Bora.Choi