Sample Header Ad - 728x90

Which approach to concurrency safe upserts is best for table-valued input if MERGE is forbidden?

5 votes
2 answers
796 views
[This classic concurrency safety article](https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/) is clearly designed for only upserting one row at a time. In my situation, I have a table-valued input and I want to upsert each row in a concurrency safe way. I know that this isn't always possible, but I want to get as close as possible. MERGE seems like a natural solution to it, but I distrust it and truly am in a situation where it is [bug prone](https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-to-use-merge/) . The remaining two approaches in Michael J. Swart's article are: 1) _Inside a Transaction With Lock Hints (Update More Common)_
CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
SET XACT_ABORT ON;
BEGIN TRAN
 
  UPDATE TOP (1) dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
     SET Etc = @Etc
   WHERE Email = @Email;
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      INSERT dbo.AccountDetails ( Email, Etc )
      VALUES ( @Email, @Etc );
  END 
COMMIT
2) _Inside a Transaction With Lock Hints (Insert More Common)_
CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
SET XACT_ABORT ON;
BEGIN TRAN
 
  INSERT dbo.AccountDetails ( Email, Etc )
  SELECT @Email, @Etc
  WHERE NOT EXISTS (
      SELECT *
      FROM dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
      WHERE Email = @Email
  )
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      UPDATE TOP (1) dbo.AccountDetails
      SET Etc = @Etc
      WHERE Email = @Email;
  END 
COMMIT
I could adapt either of these to use table variables (e.g. I suspect that IF (@@ROWCOUNT = 0) needs totally removing), but **does the usage of a table-valued input make it obvious that we should prefer either the first or second solution?** If not, then on what basis should the decision be made?
Asked by J. Mini (1237 rep)
Apr 11, 2025, 11:15 PM
Last activity: Apr 13, 2025, 10:35 AM