Why is TSQL MERGE failing with a Primary Key Violation? Isn’t it atomic?

I have gotten this question a couple of times, so I thought I’d post the answer. 

No, MERGE doesn’t have strong isolation guarantees by default. By default MERGE will perform the "match" phase of the query without using exclusive locking. This increases the concurrency when the data to be merged is disjoint, ie where you won't be attempting to insert/update the same key value from multiple sessions. However when multiple sessions concurrently attempt to insert the same key value, you get a race condition where both sessions to see the row state before either tries to INSERT/UPDATE.

 

A simple MERGE like this:

 

MERGE mt AS target

USING (select 4 id, 4 a) AS source(id, a)

ON (target.id =source.id)

WHEN MATCHED

    THEN UPDATE SET target.a = source.a

WHEN NOT MATCHED THEN  

          INSERT (id, a)

          VALUES (source.id,source.a);

 

that takes the INSERT branch has a lock acquisition sequence that looks like this:

 

8 - IX 5 - OBJECT

7 - IU 6 - PAGE

8 - IX 6 - PAGE

15 - RangeI-N 7 - KEY

5 - X 7 – KEY

 

In particular the matching clause is evaluated while only holding a weak IX or IU lock.

 

That lock sequence is compatible with another copy of the same lock sequence until the last lock, the X lock on the key you are inserting which in incompatible. That will allow two MERGE statements executing at close to the same time to both evaluate the matching criteria, then one inserts the new row and the second gets a PK violation.

 

If you add some lock hints

 

MERGE mt with (paglock,updlock) AS target

USING (select 4 id, 4 a)AS source(id, a)

ON (target.id =source.id)

WHEN MATCHED

    THEN UPDATE SET target.a = source.a

WHEN NOT MATCHED THEN  

          INSERT (id, a)

          VALUES (source.id,source.a);

 

You can perform the matching read with a more restrictive U lock.

 

8 – IX 5 - OBJECT

4 - U 6 - PAGE

5 - X 6 – PAGE

 

Or with a more restrictive range lock

 

MERGE mt with (serializable)  AS target

USING (select 4 id, 4 a)AS source(id, a)

ON (target.id =source.id)

WHEN MATCHED

    THEN UPDATE SET target.a =source.a

WHEN NOT MATCHED THEN  

          INSERT (id, a)

          VALUES (source.id,source.a);

 

 

8 – IX 5 - OBJECT

7 – IU 6 - PAGE

14 - RangeS-U 7 - KEY

8 – IX 6 - PAGE

15 - RangeI-N 7 - KEY

5 - X 7 – KEY

 

nb The SERIALIZABLE hint is the same as HOLDLOCK, but I think SERIALIZABLE is more descriptive as it suggests that you will get range locking.

David

Comments

• Anonymous
  April 09, 2014
  Apparently even simpler update statements and such are also not atomic, but hey I never thought to try it with holdlock or serializable!

• Anonymous
  December 05, 2014
  Thank you!  2 questions... 1.) Would using only updlock work? or must use together with paglock? 2.) With serializable being more "restrictive", any benefit does it bring on top of using (paglock,updlock) like you first suggested?

  • Anonymous
    November 22, 2017
    SERIALIZABLE/HOLDLOCK will take range locks for rows that don't exist, where just UPDLOCK will only lock the rows (or pages) actually read.