Deje de usar UPSERT anti-pattern (SQL Server)

Para los futuros estudiantes del curso "MS SQL Server Developer" se preparó una traducción del artículo.



También lo invitamos a ver el seminario web abierto sobre "Bases de datos de gráficos en SQL Server" . En esta lección, los participantes, junto con un experto, analizarán qué son las bases de datos de gráficos y cuáles son las opciones para trabajar con gráficos y jerarquías en SQL Server.

---

Creo que todos ya conocen mi opinión sobre MERGE y por qué me mantengo alejado de ella. Pero aquí hay otro anti-patrón con el que me encuentro constantemente cuando se requiere ejecutar UPSERT (UPdate inSERT: actualiza una fila si existe e insértala si no existe):

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 bastante lógico y encaja con la forma en que lo pensamos:

• ¿Hay una cadena para la clave dada?

  
  
  
  
  

  • SÍ : Actualizamos esta línea.

    
    
    
    
    

  • : .

    
    
    
    
    

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

, , , ( ):

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