DEADLOCK

--SET DEADLOCK_PRIORITY -10
--SET DEADLOCK_PRIORITY -9
--SET DEADLOCK_PRIORITY -8
--SET DEADLOCK_PRIORITY -7
--SET DEADLOCK_PRIORITY -6
--SET DEADLOCK_PRIORITY -5
--SET DEADLOCK_PRIORITY -4
--SET DEADLOCK_PRIORITY -3
--SET DEADLOCK_PRIORITY -2
--SET DEADLOCK_PRIORITY -1
--SET DEADLOCK_PRIORITY 0
--SET DEADLOCK_PRIORITY 1
--SET DEADLOCK_PRIORITY 2
--SET DEADLOCK_PRIORITY 3
--SET DEADLOCK_PRIORITY 4
--SET DEADLOCK_PRIORITY 5
--SET DEADLOCK_PRIORITY 6
--SET DEADLOCK_PRIORITY 7
--SET DEADLOCK_PRIORITY 8
--SET DEADLOCK_PRIORITY 9
--SET DEADLOCK_PRIORITY 10

SELECT *, OBJECT_NAME(object_id)
FROM SYS.partitions
WHERE hobt_id = XXXX

◎ 自定義 DEADLOCK 的 ERROR HANDLING

CREATE PROCEDURE spTran1
AS
  BEGIN
      BEGIN TRY  
         BEGIN TRAN;
            UPDATE  TableA
            SET     [Name] += ' Tran1'
            WHERE   ID = 1;

            WAITFOR DELAY '00:00:4';--等4秒

            UPDATE  TableB
            SET     [Name] += ' Tran1'
            WHERE   ID = 1;

            COMMIT TRANSACTION;

            PRINT 'spTran1 executed Successful';
      END TRY      
      BEGIN CATCH
          IF ( ERROR_NUMBER() = 1205 )--1205是deadlock error flag

        BEGIN
            PRINT 'ERROR_NUMBER 1205, Deadlock. Rollback now.';
        END;

          ROLLBACK;--Rollback the transaction 
      END CATCH;       
   END
GO

EXECUTE spTran1;
GO