Dan Guzman Blog

“UPSERT” Race Condition With MERGE

I mentioned in Conditional INSERT/UPDATE Race Condition that most “UPSERT” code is defective and can lead to constraint violations and data integrity issues in a multi-user environment .  In this post, I’ll show how to prevent duplicate key errors and data problems with the MERGE statement too.  You might want to peruse Conditional INSERT/UPDATE Race Condition before reading this for a background on these concurrency concerns.

Background on MERGE

Microsoft introduced the ANSI-standard MERGE statement in SQL Server 2008.  MERGE is very powerful in that it can perform multiple actions in a single statement that previously required separate INSERT/UPDATE/DELETE statements.  MERGE is also a good alternative to the proprietary UPDATE…FROM syntax allowed in the T-SQL dialect.

MERGE can (and in my opinion should) be used to address the requirement to either INSERT or UPDATE depending on whether the source data already exists.  One need only include the MERGE statement clauses WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT in order to take the proper action, all within a single statement.

 “UPSERT” MERGE Concurrency Test

Even though MERGE provides the means to perform multiple actions within a single statement, developers still need to consider concurrency with MERGE to prevent errors and data issues.  Let me illustrate using the table and stored procedure that I originally posted in Conditional INSERT/UPDATE Race Condition:

CREATE TABLE dbo.Foo

(

      ID int NOT NULL

            CONSTRAINT PK_Foo PRIMARY KEY,

      Bar int NOT NULL

);

GO

 

CREATE PROCEDURE dbo.Merge_Foo

      @ID int,

      @Bar int

AS

 

SET NOCOUNT, XACT_ABORT ON;

 

MERGE dbo.Foo AS f

USING (SELECT @ID AS ID, @Bar AS Bar) AS new_foo

ON f.ID = new_foo.ID

WHEN MATCHED THEN

    UPDATE SET f.Bar = new_foo.Bar

WHEN NOT MATCHED THEN

    INSERT (ID, Bar)

        VALUES (new_foo.ID, new_foo.Bar);

       

RETURN @@ERROR;

GO

I ran the script below from 2 different SSMS windows after changing the time to the near future so that both executed at the same time.  My test box had a single quad-core processor with SQL Server 2008 Developer Edition installed, which I expected to have enough multi-processing power to create the error.

WAITFOR TIME '08:00:00'

 

EXEC dbo.Merge_Foo

      @ID = 1,

      @Bar = 1

I got a primary key violation error, showing that MERGE is vulnerable to concurrency problems like a multi-statement conditional INSERT/UPDATE technique. However, I couldn’t reproduce the error with MERGE nearly as consistently as I could with the conditional INSERT/UPDATE in Conditional INSERT/UPDATE Race Condition.  This could be due to a number of reasons (e.g. faster processor, different SQL Server version, MERGE locking behavior) but I wanted to make sure I could reproduce the error reliably.  I created a more robust test to exercise MERGE on a loop:

CREATE TABLE dbo.Foo2

(

      ID int NOT NULL

            CONSTRAINT PK_Foo2 PRIMARY KEY,

      InsertSpid int NOT NULL,

      InsertTime datetime2 NOT NULL,

      UpdateSpid int NULL,

      UpdateTime datetime2 NULL

);

 

CREATE PROCEDURE dbo.Merge_Foo2

      @ID int

AS

 

SET NOCOUNT, XACT_ABORT ON;

 

MERGE dbo.Foo2 AS f

USING (SELECT @ID AS ID) AS new_foo

      ON f.ID = new_foo.ID

WHEN MATCHED THEN

    UPDATE

            SET f.UpdateSpid = @@SPID,

            UpdateTime = SYSDATETIME()

WHEN NOT MATCHED THEN

    INSERT

      (

            ID,

            InsertSpid,

            InsertTime

      )

    VALUES

      (

            new_foo.ID,

            @@SPID,

            SYSDATETIME()

      );

       

RETURN @@ERROR;

I ran the script below from 4 different SSMS windows after changing the time to the near future so that all executed at the same time.   

DECLARE

    @NextTime datetime,

    @ID int,

    @MillisecondDelay int;

SELECT

    @NextTime = '08:10:00',

    @ID = 1,

    @MillisecondDelay = 100;

--execute 10 times per second for 1 minute

WHILE @ID <= 600

BEGIN

    --pause and sync with other sessions

    WAITFOR TIME @NextTime;

    EXEC dbo.Merge_Foo2

        @ID = @ID;

    SELECT

        @ID = @ID + 1,

        --assume no more that 100ms per execution

        @NextTime = DATEADD(MILLISECOND, @MillisecondDelay, @NextTime);

END;

I was able to reproduce the primary key violation every time with this test script.

Addressing the MERGE Race Condition

The underlying issue with any conditional insert technique is that data must be read before the determination can be made whether to INSERT or UPDATE.  To prevent concurrent sessions from inserting data with the same key, an incompatible lock must be acquired to ensure only one session can read the key and that lock must be held until the transaction completes.

I showed how one might address the problem using both UPDLOCK and HOLDLOCK locking hints in Conditional INSERT/UPDATE Race Condition.  MERGE is slightly different, though.  I repeated the test with only the HOLDLOCK hint added:

ALTER PROCEDURE dbo.Merge_Foo2

      @ID int

AS

 

SET NOCOUNT, XACT_ABORT ON;

 

MERGE dbo.Foo2 WITH (HOLDLOCK) AS f

USING (SELECT @ID AS ID) AS new_foo

      ON f.ID = new_foo.ID

WHEN MATCHED THEN

    UPDATE

            SET f.UpdateSpid = @@SPID,

            UpdateTime = SYSDATETIME()

WHEN NOT MATCHED THEN

    INSERT

      (

            ID,

            InsertSpid,

            InsertTime

      )

    VALUES

      (

            new_foo.ID,

            @@SPID,

            SYSDATETIME()

      );

       

RETURN @@ERROR;

This test showed that simply adding the HOLDLOCK hint prevented the primary key violation error.  Unlike the conditional INSERT/UPDATE in Conditional INSERT/UPDATE Race Condition, MERGE acquired a key update lock by default so UPDLOCK was not needed.  Also, in contrast the multi-statement conditional INSERT/UPDATE technique, no explicit transaction is required because MERGE is an atomic DML statement.  The HOLDLOCK hint was still needed, though, because MERGE otherwise releases the update key lock before the insert.  I gleaned this by examining the locks from a Profiler trace of the MERGE without the HOLDLOCK:

EventClass

TextData

Mode

ObjectID

Type

SP:Starting

EXEC dbo.Merge_Foo2 @ID = 1

1314103722

Lock:Acquired

8 - IX

1330103779

5 - OBJECT

Lock:Acquired

1:173

7 - IU

0

6 - PAGE

Lock:Acquired

(10086470766)

4 - U

0

7 - KEY

Lock:Released

(10086470766)

4 - U

0

7 - KEY

Lock:Released

1:173

7 - IU

0

6 - PAGE

Lock:Acquired

1:173

8 - IX

0

6 - PAGE

Lock:Acquired

(10086470766)

15 - RangeI-N

0

7 - KEY

Lock:Acquired

(10086470766)

5 - X

0

7 - KEY

Lock:Released

(10086470766)

5 - X

0

7 - KEY

Lock:Released

1:173

8 - IX

0

6 - PAGE

Lock:Released

8 - IX

1330103779

5 - OBJECT

SP:Completed

EXEC dbo.Merge_Foo2 @ID = 1

1314103722

If another concurrent MERGE of the same key occurs after the update lock is released and before the exclusive key lock is acquired, a duplicate key error will result.

The trace below of the MERGE with the HOLDLOCK hint shows that locks aren’t released until the insert (and statement) completes, this avoiding the concurrency problem with MERGE.

EventClass

TextData

Mode

ObjectID

Type

SP:Starting

EXEC dbo.Merge_Foo2 @ID = 1

 

1314103722

 

Lock:Acquired

 

8 - IX

1330103779

5 - OBJECT

Lock:Acquired

1:173

7 - IU

0

6 - PAGE

Lock:Acquired

(10086470766)

4 - U

0

7 - KEY

Lock:Acquired

1:173

8 - IX

0

6 - PAGE

Lock:Acquired

(10086470766)

15 - RangeI-N

0

7 - KEY

Lock:Acquired

(10086470766)

5 - X

0

7 - KEY

Lock:Released

(10086470766)

5 - X

0

7 - KEY

Lock:Released

1:173

8 - IX

0

6 - PAGE

Lock:Released

 

8 - IX

1330103779

5 - OBJECT

SP:Completed

EXEC dbo.Merge_Foo2 @ID = 1

 

1314103722

 

 

Legacy Comments


Vadym
2009-02-03
re: “UPSERT” Race Condition With MERGE
I noticed that there must be something suspicious with concurency in this new operator when was reading "Introducing SQL Server 2008". And here you provide full test and description of this! Many thanks, very interesting

Jens
2009-02-08
re: “UPSERT” Race Condition With MERGE
Great Dan, this solved a lot of problems for me. But I have a very delicate issue when using Insert OVER DML with MERGE. I have a lot of similar stored procedures as in your article but I use Insert OVER DML to store/archive the old record. I will get the primary key violation error in this case and even I try to put a holdlock on the merge statement it will not work.

I also tried setting a HOLDLOCK on the Insert OVER DML statement but I will neither solve my problem.

Have searched everywhere for a solution, is this something you have the answer for ?

Thanks again for your article !

Dan Guzman
2009-02-08
re: “UPSERT” Race Condition With MERGE
Hi, Jens.

I see from your separate email that you were able to fix the problem. I'm sharing below so that others might benefit from your experience.

----------------------
I managed to sort out the problem. I just forgot to use the new SYSDATETIME(), I used GETDATE() and it return three fraction detail which is not enough since the column is a DateTime2(7) type. This will cause a constraint failure in my case because the constraint lies on this column togheter with the ID.

Easy to solve.

B.rgd

Jens

Ernesto Perales Soto
2012-08-22
re: “UPSERT” Race Condition With MERGE
I had a very similar problem to the one described here. I think most of people would believe that a MERGE is atomic (in the sense that the table contents won't change between the SELECT part and the INSERT/UPDATE part). Great post.