-- 글제목 : 방어코드 프로시저 기본뼈대 + 데드락 강제 해결방안

(이건 원초적으로 해결하는게 더 좋긴한데 진짜 간혹! 데드락이 발생하는경우 NOT BAD 에요~)

-- 작성자 : 차주언(narsas@naver.com)
-- 작성일 : 2009.12.04

-- 네이버 검색으로 '방어코드' 검색해보셔도 좋은글 많네요~

-- 프로시저기본뼈대입니다

 CREATE PROCEDURE 프로시저명

                  @변수명데이터형식

AS

                  DECLARE @ErrNo int             

                                   -- 트랜잭션시작

                  BEGIN TRY

                                   BEGIN TRAN                            

                                                     -- 내용기술

                                   COMMIT TRAN                        -- 트랜잭션적용

                  END TRY

                  BEGIN CATCH

                                   SELECT @ErrNo = @@Error

                                   ROLLBACK TRAN  

                  END CATCH

                  RETURN (@ErrNo)

GO

<기타 추천 참고 자료>

-- 에러가발생한경우SELECT 해서바로보여주는프로시저입니다

create proc usp_showerrorinfo

as

       select

       error_number() as ErrorNumber

,      error_state() as ErrorState

,      error_severity() as ErrorSeverity

,      error_line() as Errorline

,      isnull(error_procedure(), 'Not in Proc') as ErrorProc

,      error_message() as ErrorMEssage

go

-- 1/0을수행하면에러발생하는데해당에러정보출력해주는프로시저입니다

create proc up_test

as

       begin try

             select 1/0

       end try

       begin catch

             exec usp_showerrorinfo

       end catch

go

-- 에러출력해보죠~

exec up_test

-- 에러PRINT 프로시저입니다

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

-- 실전!

-- 단순에러처리뿐아니라데드락까지!!

-- 데드락유발쿼리(두개세션에열어서실험해본다)

-- 데드락이발생해도재시도5번이나재시도합니다

USE mydb

go

CREATE TABLE sales

(

                  s_id int

,                itemid int

)

insert sales values(30,1)

insert sales values(30,2)

DECLARE @retry INT;

SET @retry = 5;

WHILE (@retry > 0)

BEGIN

    BEGIN TRY

       BEGIN TRANSACTION;

        UPDATE sales

        SET s_id = s_id + 1

        WHERE itemid = 2;

        WAITFOR DELAY '00:00:07';

        UPDATE sales

        SET s_id = s_id + 1

        WHERE itemid = 1;

        SET @retry = 0;

        COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

        IF (ERROR_NUMBER() = 1205)  -- 데드락으로죽으면

            SET @retry = @retry - 1;

        ELSE

            SET @retry = -1;                     -- 데드락이아닌딴에러

        EXECUTE usp_MyErrorLog;      

        IF XACT_STATE() <> 0                                  -- 0은현재활성트랜잭션이없습니다란뜻

            ROLLBACK TRANSACTION;

    END CATCH;

END; -- End WHILE loop.

GO