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

SQL Server如何处理死锁

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

　　【IT168 专稿】死锁产生的情形是由于两个事务彼此互相等待对方放弃各自的锁造成的。

　　当出现这种情况时，SQL Server会自动选择一个关掉进程，允许另一个进程继续执行来结束死锁。关闭的事务会被回滚并抛出一个错误的消息发送给执行该进程的用户。一般来说，事务需要最少数量的开销来回滚锁撤销的事务。

　　这篇文章将解释如何以一种友好的方式来处理死锁问题。

　　死锁

　　事务A企图更新表1并且同时从第2张表执行读或更新操作，而事务B其它更新表2并同时从表1执行读或更新操作。再这种情形下，事务A打开锁以便事务B需要完成它的任务，反之亦然;这样事务都不能完成直到其它事务释放锁为止。

　　死锁的解决方案

　　下面的示例展示了两个事务之间造成死锁的情形。

　　事务 A

BEGIN TRANSACTION

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

　　事务B

BEGIN TRANSACTION

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

　　如果两个事务都在同一时间执行，那么事务A会锁住并更新Customer表，而此时事务B会锁住并更新Orders表。延迟5 ms之后，事务A会寻找锁住的Orders表而该表已经被事务B锁住，此时，事务B会寻找被事务A锁住的Customer表。因此，两个事务一直都不能执行，死锁产生了，并且SQL server会为放弃的事务返回一个错误消息1205.

(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 52) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.

Rerun the transaction.

　　但是如果你不想看到系统默认的处理行为(放弃事务)应该如何实现呢?你可以改变它吗?是的，可以，通过重写下面展示的事务A和事务B即可实现。

　　事务 A

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
    IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
    BEGIN
        WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
        GOTO RETRY -- Go to Label RETRY
    END

END CATCH

　　事务 B

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
    IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
    BEGIN
        WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
        GOTO RETRY -- Go to Label RETRY
    END

END CATCH

这里我在两个事务的开始时使用了标签RETRY来实现。TRY/CATCH 方法用来在事务中处理异常。如果写在TRY块中的代码失败了，控制器会自动地跳转到CATCH块，让事务回滚，并且如果异常是由于死锁(Error_Number 1205)而产生的异常，事务将等待5毫秒。这里使用延迟是因为其它的事务(未放弃的事务)在延迟时间内能够完成它的操作并释放自己锁住的表。你可以根据事务的大小来增加延迟的时间。延迟之后，通过使用下面的元素事务开始从开始标签执行(RETRY: Label RETRY在事务的开始处)：

GOTO RETRY -- Go to Label RETRY

　　该语句用于转移名为RETRY的控制标签(开始处)。

　　现在，同时执行事务A和事务B。两个事务将会执行成功。再次看看出现事务异常发生的地方将会得到不通的信息。

(1 row(s) affected)
Rollback Transaction

(1 row(s) affected)

//(1 row(s) affected)

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

关注我们