在SQL Server中向上插入反模式

Michael Swart在几年前的“神话破灭：并发更新/插入解决方案”(Mythbusting：Concurrent Update/Insert Solutions)一书中也讨论过这个主题，其中包括这样一个事实：保持初始逻辑不变，只提高隔离级别，就会将关键违规更改为死锁。他后来检查了自己对MERGE的热情，注意MERGE语句。确保你也阅读了这两个帖子上的所有评论。

在我的职业生涯中，我通过简单地调整到以下模式修复了许多死锁(去掉冗余检查，将序列包装在事务中，并使用适当的锁定保护第一个表访问)：

开始事务；更新dbo。T WITH(UPDLOCK，SERIALIZABLE)SET VAL=@VAL WHERE[KEY]=@KEY；*IF@@ROWCOUNT=0 BEGIN INSERT DBO。T([key]，val)value(@key，@val)；结束提交事务；

UPDLOCK用于防止语句级的转换死锁(让另一个会话等待，而不是鼓励受害者重试)。SERIALIZABLE用于防止在整个事务过程中底层数据发生更改(确保不存在的行继续不存在)。

它的代码稍多一些，但它的安全性为1000%，即使在最糟糕的情况下(该行还不存在)，它的性能也与反模式相同。在最好的情况下，如果您要更新已存在的行，则只定位该行一次会更有效率。将此逻辑与数据库中必须执行的高级操作结合起来，就稍微简单了一些：

如果键存在，并且两个会话试图同时更新它，它们将轮流成功更新该行，就像以前一样。

如果密钥不存在，一个会话将赢得并插入该行。另一个将不得不等到锁被释放后才能检查是否存在，并被强制更新。

在这两种情况下，赢得比赛的作者都会将他们的数据输给失败者在他们之后更新的任何内容。

请注意，高并发系统上的总体吞吐量可能会受到影响，但这是您应该愿意进行的权衡。你得到了很多死锁受害者或键违规错误，但它们发生得很快，这不是一个好的性能指标。有些人希望看到从所有场景中移除所有阻塞，但其中一些阻塞是您绝对想要的数据完整性。

很明显，上述解决方案针对更新进行了优化，并假设您正在尝试写入的键已经存在于表中，其频率与其不存在的频率一样低。如果您更愿意针对插入进行优化，并且知道或猜测插入比更新更有可能，则可以颠倒逻辑，仍然可以进行安全的更新操作：

开始事务；然后插入dbo。T([KEY]，VAL)SELECT@KEY，@VAL WHERE NOT EXISTS(从dbo中选择1。T WITH(UPDLOCK，SERIALIZABLE)WHERE[KEY]=@KEY)；如果@@ROWCOUNT=0开始更新dbo。T设置val=@val where[key]=@key；结束提交事务；

还有一种就这么做的方法，在这种方法中，您盲目插入并让冲突向调用方引发异常：

开始事务；开始尝试插入dbo。T([key]，val)值(@key，@val)；END TRY BEGIN CATCH UPDATE DBO。T set val=@val where[key]=@key；end catch to Commit Transaction；

这些例外的成本通常会超过第一次检查的成本；您必须对命中率/未命中率进行大致准确的猜测才能尝试。我在这里和这里都写过这个。

上面处理的是单例插入/更新决策，但是Justin peling问到，当您在处理多行而不知道其中哪些行已经存在时该怎么办？

假设您使用类似于表值参数的方式在中发送一组行，您将使用联接进行更新，然后使用NOT EXISTS进行插入，但是该模式仍然等同于上面的第一种方法：