选择/插入版本的 Upsert:是否有高并发的设计模式? [英] Select / Insert version of an Upsert: is there a design pattern for high concurrency?

问题描述

我想做 UPSERT 的 SELECT/INSERT 版本.以下是现有代码的模板:

I want to do the SELECT / INSERT version of an UPSERT. Below is a template of the existing code:

// CREATE TABLE Table (RowID INT NOT NULL IDENTITY(1,1), RowValue VARCHAR(50))

IF NOT EXISTS (SELECT * FROM Table WHERE RowValue = @VALUE)
BEGIN
   INSERT Table VALUES (@Value)
   SELECT @id = SCOPEIDENTITY()
END
ELSE
   SELECT @id = RowID FROM Table WHERE RowValue = @VALUE)

该查询将从多个并发会话中调用.我的性能测试表明，它会在特定负载下持续抛出主键违规.

The query will be called from many concurrent sessions. My performance tests show that it will consistently throw primary key violations under a specific load.

是否有针对此查询的高并发方法，可以使其在保持性能的同时仍避免插入已存在的数据?

Is there a high-concurrency method for this query that will allow it to maintain performance while still avoiding the insertion of data that already exists?

推荐答案

您可以使用锁来使事情变得可串行化，但这会降低并发性.为什么不先尝试常见条件(主要是插入或主要是选择")，然后再安全处理补救"操作?也就是说，JFDI" 模式...

You can use LOCKs to make things SERIALIZABLE but this reduces concurrency. Why not try the common condition first ("mostly insert or mostly select") followed by safe handling of "remedial" action? That is, the "JFDI" pattern...

预计大部分插入(棒球场 70-80%+):

Mostly INSERTs expected (ball park 70-80%+):

尝试插入.如果失败，则该行已经创建.无需担心并发性，因为 TRY/CATCH 会为您处理重复项.

Just try to insert. If it fails, the row has already been created. No need to worry about concurrency because the TRY/CATCH deals with duplicates for you.

BEGIN TRY
   INSERT Table VALUES (@Value)
   SELECT @id = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
    ELSE -- only error was a dupe insert so must already have a row to select
      SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
END CATCH

主要是选择:

类似，但尝试先获取数据.没有数据 = 需要插入.同样，如果 2 个并发调用尝试 INSERT，因为它们都发现缺少 TRY/CATCH 句柄的行.

Similar, but try to get data first. No data = INSERT needed. Again, if 2 concurrent calls try to INSERT because they both found the row missing the TRY/CATCH handles.

BEGIN TRY
   SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
   IF @@ROWCOUNT = 0
   BEGIN
       INSERT Table VALUES (@Value)
       SELECT @id = SCOPE_IDENTITY()
   END
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
    ELSE
      SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
END CATCH

第二个似乎在重复，但它是高度并发的.锁可以实现相同的效果，但会牺牲并发性...

The 2nd one appear to repeat itself, but it's highly concurrent. Locks would achieve the same but at the expense of concurrency...

为什么不使用 MERGE...

Why not to use MERGE...

如果您使用 OUTPUT 子句，它将只返回更新的内容.因此，您需要一个虚拟 UPDATE 来为 OUTPUT 子句生成 INSERTED 表.如果您必须通过许多调用(如 OP 所暗示的那样)进行虚拟更新，则需要大量的日志写入只是才能使用 MERGE.

If you use the OUTPUT clause it will only return what is updated. So you need a dummy UPDATE to generate the INSERTED table for the OUTPUT clause. If you have to do dummy updates with many calls (as implied by OP) that is a lot of log writes just to be able to use MERGE.

这篇关于选择/插入版本的 Upsert:是否有高并发的设计模式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！