2009. 11. 24. 17:50

DeadLock 예제,재 실행하기

2009. 11. 24. 17:50 in Peformance Tuning


관련글 :

Lock::Trace Flag 1204

 

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
 

'Peformance Tuning' 카테고리의 다른 글

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

티스토리툴바