sql - 如何实现条件Upsert存储过程？

Question

我正在尝试实现基本的 UPSERT 功能，但有一点不同:有时我不想实际更新现有行。

本质上，我正在尝试同步不同存储库之间的一些数据，而 Upsert 函数似乎是可行的方法。所以很大程度上基于Sam Saffron's answer to this question以及其他一些研究和阅读，我想出了这个存储过程:

(注意:我使用的是 MS SQL Server 2005，因此 MERGE 语句不是一个选项)

CREATE PROCEDURE [dbo].[usp_UpsertItem] 
    -- Add the parameters for the stored procedure here
    @pContentID varchar(30) = null, 
    @pTitle varchar(255) = null,
    @pTeaser varchar(255) = null 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    BEGIN TRANSACTION

        UPDATE dbo.Item WITH (SERIALIZABLE)
        SET Title = @pTitle,
            Teaser = @pTeaser
        WHERE ContentID = @pContentID

        IF @@rowcount = 0
            INSERT INTO dbo.Item (ContentID, Title, Teaser)
            VALUES (@pContentID, @pTitle, @pTeaser)

    COMMIT TRANSACTION
END

对于基本的更新插入，我对此感到满意，但我想让实际更新以另一列的值为条件。将其视为“锁定”一行，以便 Upsert 过程不能进行进一步的更新。我可以像这样更改 UPDATE 语句:

UPDATE dbo.Item WITH (SERIALIZABLE)
SET Title = @pTitle,
    Teaser = @pTeaser
WHERE ContentID = @pContentID
AND RowLocked = false

但是，当后续的插入尝试插入已存在但由于被“锁定”而未更新的行时，它会因唯一约束违规(对于 ContentID 字段)而失败。

那么这是否意味着我不再有经典的更新插入，即我每次都必须选择该行来确定它是否可以更新或插入？我敢打赌情况就是如此，所以我想我真正需要的是帮助获得正确的事务隔离级别，以便该过程能够安全执行。

Answer 1

我拼凑了以下脚本来证明我在过去几年中使用过的这个技巧。如果您使用它，则需要对其进行修改以适合您的目的。评论如下:

/*
CREATE TABLE Item
 (
   Title      varchar(255)  not null
  ,Teaser     varchar(255)  not null
  ,ContentId  varchar(30)  not null
  ,RowLocked  bit  not null
)


UPDATE item
 set RowLocked = 1
 where ContentId = 'Test01'

*/


DECLARE
  @Check varchar(30)
 ,@pContentID varchar(30)
 ,@pTitle varchar(255)
 ,@pTeaser varchar(255)

set @pContentID = 'Test01'
set @pTitle     = 'TestingTitle'
set @pTeaser    = 'TestingTeasier'

set @check = null

UPDATE dbo.Item
 set
   @Check = ContentId
  ,Title  = @pTitle
  ,Teaser = @pTeaser
 where ContentID = @pContentID
  and RowLocked = 0

print isnull(@check, '<check is null>')

IF @Check is null
    INSERT dbo.Item (ContentID, Title, Teaser, RowLocked)
     values (@pContentID, @pTitle, @pTeaser, 0)

select * from Item

这里的技巧是您可以在 Update 语句中设置局部变量的值。上面，仅当更新有效(即满足更新条件)时才会设置“标志”值；否则，它不会改变(这里，保留为空)，您可以检查这一点，并进行相应的处理。

至于事务并使其可序列化，我想在建议如何继续之前详细了解事务中必须封装的内容。

-- 附录，下面第二条评论的后续内容 -----------

先生。 Saffron 的想法是实现此例程的彻底而可靠的方法，因为您的主键是在外部定义并传递到数据库中的(即您没有使用标识列 - 对我来说很好，它们经常被过度使用)。

我做了一些更多的测试(在 ContentId 列上添加了主键约束，将 UPDATE 和 INSERT 包装在事务中，将可序列化提示添加到更新中)，是的，这应该可以完成您想要的一切。失败的更新会对索引的该部分施加范围锁，这将阻止任何同时尝试在列中插入该新值的操作。当然，如果同时提交 N 个请求，“第一个”将创建该行，并且它将立即由第二个、第三个等更新——除非您在该行的某个位置设置了“锁”。好技巧!

(请注意，如果没有键列上的索引，您将锁定整个表。此外，范围锁可能会锁定新值“任一侧”的行 - 或者也许不会，我没有对此进行测试。应该没关系，因为操作的持续时间应该 [?] 为单位数毫秒。)