Das Miscellany

Musings on software, math, guitars and more ...

On Concurrent UPSERTS

How to do it right with SQL Server

30 Nov 2017

Concurrent UPSERTS

Introduction

It’s a very common usecase to have to either INSERT a new row into a table or UPDATE an existing row depending on whether the row already exists. This logic is commonly referred to as an UPSERT. Let’s see how we can handle this in T-SQL.

We assume that we have this table …

CREATE TABLE dbo.Foo (
  fooId int          NOT NULL,
  stuff varchar(256) NOT NULL

  CONSTRAINT Foo__PK CLUSTERED (fooId)
);

Some naive T-SQL code to handle the UPSERT might be …

IF EXISTS ( SELECT * FROM dbo.Foo WHERE fooId = @fooId )
  UPDATE dbo.Foo
     SET stuff = @stuff
   WHERE fooId = @fooId;
ELSE
  INSERT dbo.Foo ( fooId, stuff )
  VALUES ( @fooId, @stuff );

This will work fine for a single connection but with multiple connections and high concurrency this will start to fail frequently with primary key violations.

There are various ways to handle this badly. If you are interested in these then I encourage you to read several of the articles listed in the further reading section below. This one in particular summarizes things very well. However, if all you care about is how to do it well then I offer the following solution (from here).

Solution

I recommend using a MERGE statement With HOLDLOCK.

MERGE dbo.Foo WITH (HOLDLOCK) AS target
USING (SELECT @fooId AS fooId, @stuff AS stuff) AS source
ON    source.fooId = target.fooId

WHEN MATCHED THEN UPDATE
  SET target.stuff = source.stuff

WHEN NOT MATCHED THEN
  INSERT ( fooId, stuff )
  VALUES ( @fooId, @stuff );

No transaction is required here since MERGE is an atomic statement. MERGE takes out a key update lock by default so we don’t need to use an UPDLOCK hint (as is the case with some other possible solutions). We do need a HOLDLOCK hint though in order to ensure that SQL Server doesn’t release the key update lock before the INSERT.

References and Further Reading