Harap berhenti menggunakan anti-pola UPSERT (SQL Server)

Untuk calon siswa di kursus "MS SQL Server Developer" menyiapkan terjemahan artikel.



Kami juga mengundang Anda untuk menonton webinar terbuka tentang "Database Grafik di SQL Server" . Dalam pelajaran ini, para peserta, bersama dengan seorang ahli, akan melihat apa itu database grafik dan apa saja pilihan untuk bekerja dengan grafik dan hierarki di SQL Server.

---

Saya pikir semua orang sudah tahu pendapat saya tentang MERGE dan mengapa saya menjauh darinya. Tetapi di sini adalah anti-pola lain yang selalu saya temui ketika diperlukan untuk mengeksekusi UPSERT (UPdate inSERT - perbarui baris jika ada, dan masukkan jika tidak ada):

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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Ini terlihat cukup logis dan cocok dengan cara kita berpikir tentangnya:

• Apakah ada string untuk kunci yang diberikan?

  
  
  
  
  

  • YA : Kami memperbarui baris ini.

    
    
    
    
    

  • : .

    
    
    
    
    

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

, , , ( ):

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