如何实现条件Upsert存储过程?
我正在尝试实现基本的 UPSERT 功能,但有一点不同:有时我不想实际更新现有行。
本质上,我试图在不同存储库之间同步一些数据,而 Upsert 函数似乎是可行的方法。 所以很大程度上基于Sam Saffron对此问题的回答以及其他一些研究和阅读,我想出了这个存储过程:(
注意:我使用的是 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 我对此感到满意,但我想让实际更新以另一列的值为条件。 将其视为“锁定”一行,以便 Upsert 过程不能进行进一步的更新。 我可以像这样更改 UPDATE 语句:
UPDATE dbo.Item WITH (SERIALIZABLE)
SET Title = @pTitle,
Teaser = @pTeaser
WHERE ContentID = @pContentID
AND RowLocked = false
但是,当后续的插入尝试插入已经存在但由于被“锁定”而未更新的行时,它会因唯一约束违规(对于 ContentID 字段)而失败。
那么这是否意味着我不再有经典的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.
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:
(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
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
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".
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
我拼凑了以下脚本来证明我在过去几年中使用过的这个技巧。 如果您使用它,则需要对其进行修改以适合您的目的。 注释如下:
这里的技巧是您可以在 Update 语句中设置局部变量的值。 上面,仅当更新有效(即满足更新条件)时才会设置“标志”值; 否则,它不会改变(这里,保留为空),您可以检查它,并进行相应的处理。
至于事务并使其可序列化,在建议如何继续之前,我想更多地了解事务中必须封装的内容。
-- 附录,下面第二条评论的后续 -----------
Saffron 先生的想法是实现此例程的彻底而可靠的方法,因为您的主键是在外部定义并传递到数据库中的(即你没有使用身份列——我认为很好,它们经常被过度使用)。
我做了更多测试(在 ContentId 列上添加了主键约束,将 UPDATE 和 INSERT 包装在事务中,将可序列化提示添加到更新中),是的,这应该可以完成您想要的一切。 失败的更新会对索引的该部分施加范围锁,这将阻止任何同时尝试在列中插入该新值的操作。 当然,如果同时提交 N 个请求,“第一个”将创建该行,并且它将立即由第二个、第三个等更新——除非您在该行的某个位置设置了“锁”。 好招数!
(请注意,如果没有键列上的索引,您将锁定整个表。此外,范围锁可能会锁定新值“任一侧”的行 - 或者也许他们不会,我没有测试一下应该没关系,因为操作的持续时间应该是个位数毫秒。)
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:
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 -----------
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).
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.)
您可以切换更新/插入的顺序。 因此,您可以在 try/catch 中进行插入,如果违反约束,则进行更新。 不过感觉有点脏。
You could switch the order of the update/insert around. So you do the insert within a try/catch and if you get a constraint violation then do the update. It feels a little dirty though.
创建过程 [dbo].[usp_UpsertItem]
-- 此处添加存储过程的参数
@pContentID varchar(30) = null,
@pTitle varchar(255) = null,
@pTeaser varchar(255) = null
作为
开始
-- 添加 SET NOCOUNT ON 以防止额外的结果集
-- 干扰 SELECT 语句。
设置不计数;
结尾
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;
END
我会放弃交易。
另外,@@rowcount 可能会起作用,但使用全局变量作为条件检查会导致错误。
只需进行 Exists() 检查即可。 无论如何你都必须通过桌子,所以速度不是问题。
据我所知,不需要进行交易。
I'd drop the transaction.
Plus the @@rowcount probably would work, but using global variables as a conditional check will lead to bugs.
Just do an Exists() check. You have to make a pass through the table anyhow, so speed is not the issue.
No need for the transaction as far as I can see.