如何实现有条件的 Upsert 存储过程? [英] How to implement a conditional Upsert stored procedure?

问题描述

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

I'm trying to implement your basic UPSERT functionality, but with a twist: sometimes I don't want to actually update an existing row.

从本质上讲，我正在尝试在不同存储库之间同步一些数据，而 Upsert 函数似乎是要走的路.所以主要基于 Sam Saffron 对这个问题的回答，以及其他一些研究和阅读，我想出了这个存储过程:

Essentially I'm trying to synchronize some data between different repositories, and an Upsert function seemed like the way to go. So based largely on Sam Saffron's answer to this question, as well as some other research and reading, I came up with this stored procedure:

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

(note: I'm using MS SQL Server 2005, so the MERGE statement isn't an option)

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，我对此感到满意，但我想让实际更新以另一列的值为条件.将其视为锁定"一行，以便 Upsert 过程不会进行进一步的更新.我可以像这样更改 UPDATE 语句:

I'm comfortable with this for a basic Upsert, but I'd like to make the actual update conditional on the value of another column. Think of it as "locking" a row so that no further updates may be made by the Upsert procedure. I could change the UPDATE statement like so:

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

但是随后的 Insert 在尝试插入已经存在但由于锁定"而未更新的行时，会因违反唯一约束(对于 ContentID 字段)而失败.

But then the subsequent Insert would fail with a unique constraint violation (for the ContentID field) when it tries to insert a row that already exists but wasn't updated because it was "locked".

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

So does this mean that I no longer have a classic Upsert, i.e. that I'll have to select the row every time to determine whether it can be updated or inserted? I'm betting that's the case, so I guess what I'm really asking for is help getting the transaction isolation level correct so that the procedure will execute safely.

推荐答案

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

I slapped together the following script to proof this trick I used in years past. If you use it, you'll need to modify it to suit your purposes. Comments follow:

/*
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 语句中的局部变量中设置值.上面，flag"值仅在更新有效(即满足更新条件)时才会设置；否则，它不会被改变(这里，保留为空)，你可以检查它，并进行相应的处理.

The trick here is that you can set values in local variables within an Update statement. Above, the "flag" value gets set only if the update works (that is, the update criteria are met); otherwise, it won't get changed (here, left at null), you can check for that, and process accordingly.

关于事务并使其可序列化，在建议如何继续之前，我想更多地了解事务中必须封装的内容.

As for the transaction and making it serializable, I'd like to know more about what must be encapsulated within the transaction before suggesting how to proceed.

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

-- Addenda, follow-up from second comment below -----------

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

Mr. Saffron's ideas are a thorough and solid way of implementing this routine since your primary keys are defined outside and passed into the database (i.e. you're not using identity columns--fine by me, they are often overused).

我做了一些更多的测试(在列 ContentId 上添加了主键约束，将 UPDATE 和 INSERT 包装在一个事务中，将可序列化的提示添加到更新中)，是的，这应该做你想做的一切.失败的更新会在索引的那部分设置范围锁，这将阻止在列中插入该新值的任何同时尝试.当然，如果同时提交 N 个请求，第一个"将创建该行，并且它会立即被第二个、第三个等更新——除非您在该行的某处设置了锁".好办法！

I did some more testing (added a primary key constraint on column ContentId, wrap the UPDATE and INSERT in a transaction, add the serializable hint to the update) and yes, that should do everything you want it to. The failed update slaps a range lock on that part of the index, and that will block any simultaneous attempts to insert that new value in the column. Of course, if N requests are submitted simultaneously, the "first" will create the row, and it will be immediately updated by the second, third, etc.--unless you set the "lock" somewhere along the line. Good trick!

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

(Note that without the index on the key column, you'd lock the entire table. Also, the range lock may lock the rows on "either side" of the new value--or maybe they won't, I didn't test that one out. Shouldn't matter, since the duration of the operation should [?] be in single-digit milliseconds.)

这篇关于如何实现有条件的 Upsert 存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！