65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL SERVER - 如何处理死锁

Nitin Chilka
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.88/5 (47投票s)

2009 年 9 月 22 日

CPOL

3分钟阅读

viewsIcon

299396

一篇关于如何处理死锁的文章

引言

死锁是指两个事务互相等待对方释放各自锁定的情况。

当这种情况发生时，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 毫秒后，事务 A 查找 Orders 表上的锁，该锁已被事务 B 锁定，而事务 B 查找 Customer 表上的锁，该锁已被事务 A 锁定。因此，两个事务都无法继续进行，发生死锁，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：事务开头的标签 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

这里使用的 RetryCounter 变量使事务在由于死锁而失败时（Error_Number 1205）再次执行的机会。在这个例子中，如果事务由于死锁而失败，它可以尝试执行多达三次。如果事务寻找的锁长时间未被其他事务释放，这种场景将非常有用。因此，事务可以尝试三次以检查所需的锁是否可用。

历史

  • 2009 年 9 月 20 日：初始版本
© . All rights reserved.