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

问题描述

我想要执行UPSERT的SELECT / INSERT版本。下面是现有代码的模板：

  // CREATE TABLE表（RowID INT NOT NULL IDENTITY RowValue VARCHAR（50））
 
如果NOT EXISTS（SELECT * FROM Table WHERE RowValue = @VALUE）
 BEGIN 
 INSERT表值（@Value）
 SELECT @ id = SCOPEIDENTITY（）
 END 
 ELSE 
 SELECT @id = RowID FROM Table WHERE RowValue = @VALUE）
  

查询将从许多并发会话中调用。我的性能测试表明，它将在特定负载下一致地抛出主键违规。

这个查询是否有高并发方法，将允许它保持性能，仍然避免插入已经存在的数据？

解决方案

您可以使用LOCK来使事情成为SERIALIZABLE，但这会降低并发性。为什么不先尝试常见条件（大多数插入或大多选择），然后安全处理补救操作？也就是说，JFDI模式...

大部分INSERT预期（球公园70-80％+）：

只是尝试插入。如果失败，则表示已创建行。无需担心并发，因为TRY / CATCH为您处理重复。

  BEGIN TRY 
 INSERT表值（@Value）
 SELECT @id = SCOPEIDENTITY $ b END TRY 
 BEGIN CATCH 
如果ERROR_NUMBER（）<> 2627 
 RAISERROR等
 ELSE  - 只有一个dupe插入错误，所以必须已经有一行选择
 SELECT @id = RowID FROM Table WHERE RowValue = @VALUE 
 END CATCH 
  

大部分SELECT：

尝试首先获取数据。无数据=需要INSERT。再次，如果2个并发调用尝试INSERT，因为他们都发现该行缺少TRY / CATCH句柄。

  BEGIN TRY 
 SELECT @id = RowID FROM Table WHERE RowValue = @VALUE 
 IF @@ ROWCOUNT = 0 
 BEGIN 
 INSERT表值（@Value）
 SELECT @id = SCOPEIDENTITY ）
 END 
 END TRY 
 BEGIN CATCH 
如果ERROR_NUMBER（）<> 2627 
 RAISERROR etc 
 ELSE 
 SELECT @id = RowID FROM Table WHERE RowValue = @VALUE 
 END CATCH 
  

第二个似乎重复，但它是高度并发的。锁定会实现相同但牺牲并发...

编辑：

如果使用OUTPUT子句，它只会返回更新的内容。因此，您需要一个虚拟UPDATE来为OUTPUT子句生成INSERTED表。如果你必须做许多调用（由OP暗示）的虚拟更新，这是很多日志写入只是以能够使用MERGE。

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?

解决方案

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...

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

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 = SCOPEIDENTITY()
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

Mostly SELECTs:

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 = SCOPEIDENTITY()
   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...

Edit:

Why not to use 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屋！