SQL Server：在UPDATE 上具有可序列化隔离级别的死锁

最近我花了一些时间在高并发环境中客户站点上反复出现的死锁问题。在这个博客中，我将提供一些关于这个特定死锁案例的有用信息的反馈。

死锁

死锁图

这是一个死锁图，类似于客户在生产中的真实死锁图：

查看 XML 属性，我们可以注意到两个事务的隔离级别是可序列化的，并且它是导致死锁的同一个对象（存储过程）。

存储过程

这是存储过程的样子。

CREATE PROCEDURE dbo.[bigTransactionHistory]
	@TransactionID		bigint
	, @ProductID		int
	, @TransactionDate	datetime
	, @Quantity			int
	, @ActualCost		money
AS
BEGIN
	SET NOCOUNT ON;

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

	BEGIN TRAN
		IF EXISTS(SELECT 1 FROM [bigTransactionHistory] WHERE TransactionID = @TransactionID)
		BEGIN
			UPDATE dbo.[bigTransactionHistory]
			SET ProductID = @ProductID
				, TransactionDate =	@TransactionDate
				, Quantity =		@Quantity
				, ActualCost =		@ActualCost
			WHERE TransactionID = @TransactionID
		END
		ELSE
		BEGIN
			INSERT INTO [bigTransactionHistory](
				[TransactionID]
				, [ProductID]
				, [TransactionDate]
				, [Quantity]
				, [ActualCost])
			VALUES (
				@TransactionID
				, @ProductID
				, @TransactionDate
				, @Quantity
				, @ActualCost
			)
		END
	COMMIT
END

存储过程根据表内行的存在执行 UPDATE 或 INSERT。 使用“IF EXISTS(SELECT…)”执行存在性检查。

可序列化的隔离级别

该文档提到了此级别隔离的以下行为：

在当前事务完成之前，任何其他事务都不能修改当前事务已读取的数据。

在当前事务完成之前，其他事务不能插入键值落在当前事务中的任何语句读取的键范围内的新行。

它是“IF EXISTS”，它读取行并在事务期间持有 RangeS-S 锁。
如果存储过程在两个并发会话中同时针对相同的键运行，则可能会发生死锁。

RangeS-S 和 RangeX-X 是不兼容的锁，更多信息可查看文档。

解决方案

为了解决这个死锁，我更改了存储过程，删除了 IF EXISTS(SELECT…)。

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

	BEGIN TRAN
			UPDATE dbo.[bigTransactionHistory]
			SET ProductID = @ProductID
				, TransactionDate =	@TransactionDate
				, Quantity =		@Quantity
				, ActualCost =		@ActualCost
			WHERE TransactionID = @TransactionID
		IF @@ROWCOUNT = 0
		BEGIN
			INSERT INTO [bigTransactionHistory](
				[TransactionID]
				, [ProductID]
				, [TransactionDate]
				, [Quantity]
				, [ActualCost])
			VALUES (
				@TransactionID
				, @ProductID
				, @TransactionDate
				, @Quantity
				, @ActualCost
			)
		END
	COMMIT

UPDATE 始终执行。如果没有更新行（使用@@ROWCOUNT 检查），则插入新行，从而无需第一个 RangeS-S 锁和任何死锁可能性。

UPSERT

这种类型的场景是经典的，称为UPSERT。你可以从 Michael J. Swart 那里找到一篇很棒的文：《SQL Server UPSERT 模式和反模式》。

UPSERT 场景，来自 Michael J. Swart

有趣的是，一些 SQL 数据库管理系统（如 CockroachDB）实现了UPSERT语句。

这篇 UPSERT 文章在 UPDATE 语句中添加了一个 UPDLOCK 提示。
这个想法是为了防止潜在的转换死锁。我还没有遇到过这种类型的死锁，但是添加 UPLOCK 是防止任何死锁（以及相关的重试逻辑）的好方法。它将强制其他事务等待请求的 U 锁。

提示应该像这样添加到 UPDATE 语句中：

UPDATE dbo.[bigTransactionHistory] WITH (UPDLOCK)
SET ProductID = @ProductID
	, TransactionDate =	@TransactionDate
	, Quantity =		@Quantity
	, ActualCost =		@ActualCost
WHERE TransactionID = @TransactionID

结论

UPSERT 模式非常常见，但在 SQL 中正确编写并非易事。我希望这可以帮助您理解和解决这种死锁。

原文标题：SQL Server Deadlock on UPDATE with Serializable isolation level
原文作者：Steven Naudet
原文地址：https://www.dbi-services.com/blog/sql-server-deadlock-on-update-with-serializable-isolation-level/