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.
- Anonymous