SQL Upsert (update or insert) pattern

Per discussed in previous article (deadlock issue), I want to list out insert or update pattern in SQL, and which pattern is more close to best practice in stored procedure.

1. Merge pattern: A SQL MERGE statement synchronizes a target table with a source table by performing INSERT, UPDATE, and/or DELETE operations in a single atomic statement. This is commonly referred to as an "upsert" operation.

MERGE INTO Products AS T -- Target table alias 'T'
USING UpdatedProducts AS S -- Source table alias 'S'
    ON (T.ProductID = S.ProductID) -- Join condition
WHEN MATCHED THEN
    UPDATE SET 
        T.ProductName = S.ProductName, 
        T.Price = S.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price) 
    VALUES (S.ProductID, S.ProductName, S.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Been said MERGE statement is an atomic operation. However, the MERGE statement, by itself and under default transaction isolation levels (READ COMMITTED), is vulnerable to concurrency issues and race conditions in a high volume transaction.
e.g. Primary Key Violations: Two concurrent MERGE statements might both determine that a row is NOT MATCHED and attempt to insert the same new record, causing one to fail with a unique constraint error.
e.g. Lost Updates: A transaction might read a row, another updates it, and the first then updates it again based on its original, now outdated, information.
To mitigate it, you can use 1. Increase Isolation Level: Change the transaction isolation level to SERIALIZABLE or REPEATABLE READ before executing the MERGE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Your MERGE statement here
COMMIT TRANSACTION;

But this will fall back to original deadlock issue: we don’t want to set isolation level to serializable, which cause deadlock. Another way is Wrap in an Explicit Transaction: While MERGE is atomic, wrapping it in an explicit BEGIN TRANSACTION and COMMIT block (with TRY...CATCH for error handling) provides better control and clearly defines the scope of related operations.
However, above approach is a little cumbersome and code readability is not good. Lets see other patterns.

Get Cookie Jen’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

2. Select then update pattern: This method uses control-flow logic to check for the row’s existence first.

IF EXISTS (SELECT 1 FROM Products WHERE ProductID = @TargetProductID)
BEGIN
    UPDATE Products SET Price = @NewPrice WHERE ProductID = @TargetProductID;
END
ELSE
BEGIN
    INSERT INTO Products (ProductID, ProductName, Price)
    VALUES (@TargetProductID, @NewProductName, @NewPrice);
END

OR use update first If no rows are affected (checked using the @@ROWCOUNT system variable), it then performs an INSERT. This is efficient because it only hits the database once if the row already exists.

UPDATE Products
SET ProductName = @NewProductName, Price = @NewPrice
WHERE ProductID = @TargetProductID;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Products (ProductID, ProductName, Price)
    VALUES (@TargetProductID, @NewProductName, @NewPrice);
END

However, all these multi-statement approaches should be wrapped in an explicit BEGIN TRANSACTION ... COMMIT block to ensure atomicity like Merge pattern as mentioned previously. This prevents other concurrent operations from causing primary key violations or data inconsistencies.

3. INSERT first, wrapped in Try/Catch. Catch the primary key constraint violation, then UPDATE.

BEGIN TRY
  BEGIN TRY
    INSERT INTO [syllabus] (id, url, overview)
      VALUES (@id, @url, @overview)
  END TRY
  BEGIN CATCH
    IF ERROR_NUMBER() = 2627 -- insert violated primary key constraint -> update the existing record
    BEGIN
      UPDATE [syllabus] SET url = @url, overview = @overview
      WHERE id = @id
    END
    ELSE
      THROW -- if some other error occurred, re-throw and let it be handled by the outer try-catch
  END CATCH
END TRY
  BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000),
            @ErrorNumber INT,
            @ErrorSeverity INT,
            @ErrorState INT,
            @ErrorLine INT,
            @ErrorProcedure NVARCHAR(200);
    SELECT  @ErrorMessage = ERROR_MESSAGE(),
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorLine = ERROR_LINE(),
            @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorProcedure, @ErrorLine);
END CATCH

This is more transaction-safe than previous approaches since you’re doing the insert first. If two calls for the same key happen simultaneously, whichever one goes first wins, the second insert will fail -> catch the primary key constraint violation -> do an update instead. (Still can wrap this in a transaction like previous example but I am not sure it is necessary)

The performance still depends on data is more leaning toward large volume of updates or insert, e.g. the cost of exception handling the primary key constraint violation might become a concern.