SQL Server如何处理死锁-技术开发专区

SQL Server如何处理死锁

作者：IT168 Kim 编辑：胡铭娅 2009-11-09 11:00 来源：IT168�

　　使用RetryCounter

　　现在，我猜测你已经了解了在不放弃另一个事务的情况下如何处理死锁问题。现在咱们将话题转移到下一个关于死锁的话题。想象一下，如果有多于两个的进程需要同时对表Customer和 Orders表执行读或更新操作。下面，我已经修改了这两个事务，以展示如何使用RetryCounter来解决这个问题。

　　事务A

DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY

    UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
    WAITFOR DELAY '00:00:05'  -- Wait for 5 ms
    UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    PRINT 'Rollback Transaction'
    ROLLBACK TRANSACTION
    DECLARE @DoRetry bit; -- Whether to Retry transaction or not
    DECLARE @ErrorMessage varchar(500)
    SET @doRetry = 0;
    SET @ErrorMessage = ERROR_MESSAGE()
    IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
    BEGIN
        SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
    END
    IF @DoRetry = 1
    BEGIN
        SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
        IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
        BEGIN
            RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if
                -- still deadlock occurred after three retries
        END
        ELSE
        BEGIN
            WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
            GOTO RETRY    -- Go to Label RETRY
        END
    END
    ELSE
    BEGIN
        RAISERROR(@ErrorMessage, 18, 1)
    END

//END CATCH

　　事务 B

DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
    UPDATE Orders SET ShippingId = 12 Where OrderId = 221
    WAITFOR DELAY '00:00:05' -- Wait for 5 ms
    UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    PRINT 'Rollback Transaction'
    ROLLBACK TRANSACTION
    DECLARE @DoRetry bit; -- Whether to Retry transaction or not
    DECLARE @ErrorMessage varchar(500)
    SET @doRetry = 0;
    SET @ErrorMessage = ERROR_MESSAGE()
    IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
    BEGIN
        SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
    END
    IF @DoRetry = 1
    BEGIN
        SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
        IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
        BEGIN
            RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message
                -- if still deadlock occurred after three retries
        END
        ELSE
        BEGIN
            WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
            GOTO RETRY    -- Go to Label RETRY
        END
    END
    ELSE
    BEGIN
        RAISERROR(@ErrorMessage, 18, 1)
    END

//END CATCH

　　如果事务失败是由于死锁造成的(Error_Number 1205)，这里使用RetryCounter变量对于事务再次执行有一定的变化。在本示例中，如果事务是因为死锁造成的，则事务能够试着执行三次。这种场景对于如果事务正在寻找一个需要很长时间才能完成的操作是非常有用的。因此，事务可以尝试三次来检查是否该死锁是可用的。

第1页：SQL Server如何处理死锁第2页：使用RetryCounter

关注我们