Pare de usar o antipadrão UPSERT (SQL Server)

Para os futuros alunos do curso “MS SQL Server Developer” preparamos uma tradução do artigo.



Também o convidamos a assistir ao webinar aberto sobre "Bancos de dados gráficos no SQL Server" . Nesta lição, os participantes, junto com um especialista, verão o que são bancos de dados de gráficos e quais são as opções para trabalhar com gráficos e hierarquias no SQL Server.

---

Acho que todo mundo já conhece minha opinião sobre o MERGE e por que me afasto disso. Mas aqui está outro antipadrão que encontro constantemente quando é necessário executar o UPSERT (UPdate inSERT - atualizar uma linha se existir e inserir se não existir):

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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Parece muito lógico e se encaixa na maneira como pensamos sobre isso:

• Existe uma string para a chave fornecida?

  
  
  
  
  

  • SIM : Nós atualizamos esta linha.

    
    
    
    
    

  • : .

    
    
    
    
    

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

, , , ( ):

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