UPSERTアンチパターン（SQL Server）の使用を停止してください

コース「MSSQLServer Developer」の将来の学生のために、記事の翻訳を準備しました。



また、「SQLServerのグラフデータベース」に関する公開ウェビナーもご覧ください。このレッスンでは、参加者は専門家と一緒に、グラフデータベースとは何か、SQLServerでグラフと階層を操作するためのオプションは何かを確認します。

---

MERGEについての私の意見と、なぜ私がMERGEから離れているのかは、誰もがすでに知っていると思います。しかし、UPSERTを実行する必要があるときに私が常に遭遇する別のアンチパターンがあります（UPdate inSERT-存在する場合は行を更新し、存在しない場合は挿入します）：

IF EXISTS (SELECT 1 FROM dbo.t WHERE [key] = @key)
BEGIN
  UPDATE dbo.t SET val = @val WHERE [key] = @key;
END
ELSE
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val); 
END
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

それはかなり論理的に見え、私たちがそれについて考える方法に適合します：

• 指定されたキーの文字列はありますか？

  
  
  
  
  

  • はい：この行を更新します。

    
    
    
    
    

  • : .

    
    
    
    
    

, , , — . , (, , ). -, , :

, , , ( ):

• UPDATE , ( "", "" " "). , . (Paul White) , (Martin Smith) .

  
  
  
  
  

• , INSERT :

  
  
  
  
  

  • (deadlock) - ;

    
    
    
    
    

  • (key violation), ;

    
    
    
    
    

  • , .

    
    
    
    
    

— , . , XACT_ABORT , — , . , IF EXISTS ( ), . , , .

« ...»

(Dan Guzman) Conditional INSERT/UPDATE Race Condition, "UPSERT" Race Condition With MERGE.

(Michael Swart) Mythbusting: Concurrent Update/Insert Solutions, , , . MERGE Be Careful with the Merge Statement. .

, ( , ):

BEGIN TRANSACTION;
 
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;
 
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END
 
COMMIT TRANSACTION;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

? UPDLOCK ?

• UPDLOCK ( , ). 

  
  
  
  
  

• SERIALIZABLE ( , ). 

  
  
  
  
  

, 1000% . ( ) . , , , . , :

:

• , , . 

  
  
  
  
  

• , «» . ( ), UPDATE. 

  
  
  
  
  

, , - , «» .

, . , . , , .

, UPDATE ?

, UPDATE . , , INSERT, INSERT , UPDATE, UPSERT:

BEGIN TRANSACTION;
 
INSERT dbo.t([key], val) 
  SELECT @key, @val
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE)
      WHERE [key] = @key
  );
 
IF @@ROWCOUNT = 0
BEGIN
  UPDATE dbo.t SET val = @val WHERE [key] = @key;
END
 
COMMIT TRANSACTION;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

« », INSERT :

BEGIN TRANSACTION;
 
BEGIN TRY
  INSERT dbo.t([key], val) VALUES(@key, @val);
END TRY
BEGIN CATCH
  UPDATE dbo.t SET val = @val WHERE [key] = @key;
END CATCH
 
COMMIT TRANSACTION;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

. / . .

?

INSERT / UPDATE, (Justin Pealing) , , , ?

- (TVP, Table-Valued Parameters), UPDATE JOIN, INSERT, NOT EXISTS. , :

CREATE PROCEDURE dbo.UpsertTheThings
    @tvp dbo.TableType READONLY
AS
BEGIN
  SET NOCOUNT ON;
 
  BEGIN TRANSACTION;
 
  UPDATE t WITH (UPDLOCK, SERIALIZABLE) 
    SET val = tvp.val
  FROM dbo.t AS t
  INNER JOIN @tvp AS tvp
    ON t.[key] = tvp.[key];
 
  INSERT dbo.t([key], val)
    SELECT [key], val FROM @tvp AS tvp
    WHERE NOT EXISTS (SELECT 1 FROM dbo.t WHERE [key] = tvp.[key]);
 
  COMMIT TRANSACTION;
END
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

- , TVP (XML, - ..), JOIN . INSERT — UPDATE .

UPSERT- , , , , . , IF EXIST. (Paul White, sql.kiwi | @SQK_Kiwi) — .

MERGE (, - MERGE-), .

---

"MS SQL Server Developer".

« SQL Server».