Sample Header Ad - 728x90

Do I need a `HOLDLOCK` on a `MERGE` that's not sourcing from another table?

0 votes
1 answer
329 views
I need to know whether the following pattern requires me to use HOLDLOCK in a very highly concurrent environment. Particularly note that the source is not another table. It's basically parameters that have been passed to the query.
MERGE INTO table1 WITH (HOLDLOCK) AS target
USING (
	SELECT 
		@ID AS ID,
		...
) AS source
ON (target.ID = source.ID AND ...)
WHEN NOT MATCHED THEN
	INSERT (ID, ...)
	VALUES (source.ID, ...)
I've only put it in out of precaution, since I've been reading about the pitfalls of MERGE. In my own dev-testing though (still very highly concurrent), it doesn't seem to make a difference. I never run into a concurrency problem even without the HOLDLOCK. I can't use INSERT INTO table1 ... WHERE ... DOES NOT EXISTS (... SELECT table1 ...) pattern, because **that** runs into concurrency problems, even with HOLDLOCK in place. So the MERGE was my solution to it, I am just not sure if I need the HOLDLOCK on it.
Asked by hyankov (103 rep)
Sep 12, 2024, 09:05 PM
Last activity: Sep 13, 2024, 05:34 AM