HOLDLOCK required in MERGE with UPDATE

Question

Within a .NET 6 application the EntityFramework is used to keep track of entities and to update related fields. To improve bulk updates, the EfCore.BulkExtensions package has been added.

During a .BulkUpdate a SQL statement is generated and executed (by the described package). I noticed the WITH (HOLDLOCK) part. I've read some documentation about the hint and possible race conditions, but do they occur in a MERGE with only the UPDATE command? In short, is this WITH (HOLDLOCK) hint necessary in case the MERGE statement only includes an UPDATE command or can it safely be removed?

Sample query:

MERGE TargetProducts WITH (HOLDLOCK) AS Target
USING SourceProducts    AS Source
ON Source.ProductID = Target.ProductID
WHEN MATCHED THEN UPDATE SET
    Target.ProductName  = Source.ProductName,
    Target.Price        = Source.Price;

Paul White · Answer 1 · 2022-09-28T10:33:01.683

The HOLDLOCK hint applies SERIALIZABLE isolation semantics to the hinted table. This is necessary for example when performing an 'upsert' (update if exists, insert otherwise) or inserting a row only if no row with the same key currently exists.

In either case, the issue is the MERGE testing to see if a particular row does not exist, and that condition continuing to be valid until the insert is completed.

Without serializable semantics, there is no row to lock to provide the necessary guarantee. Range key locking provides the solution in SQL Server by locking the key range any new row would be added to.

This is not a 'bug' with MERGE. Separate INSERT and UPDATE statements are subject to the same fundamental considerations and also require an isolation level hint for correct behaviour under high concurrency.

There is no need for a HOLDLOCK hint if the MERGE operates on one or more existing rows, as your update does.

As a side note, HOLDLOCK is a terrible name for this hint and only maintained for backward compatibility. I wish the Entity Framework team had used the more modern SERIALIZABLE synonym instead.

Related Q & A: SQL Server 2014 Concurrent input issue

HOLDLOCK required in MERGE with UPDATE

1 Answers1