Is it Possible to use the functions without Holdlock

[nafberger]

sometimes the Holdlock creates trouble with other processes and creates deadlocks. whats the benefit of using it and can it be turned off?

[borisdj]

HOLDLOCK is used for Concurrency Issues:
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
MERGE *looks* like it will take care of concurrency for you, because implicitly it seems to a single, atomic statement. However, under the covers, SQL Server really does perform the different operations independently. This means that you could end up with race conditions or primary key conflicts when multiple users are attempting to fire the same MERGE statement concurrently. Dan Guzman went into a lot of detail in his blog post a few years ago, but basically this means that unless you use a HOLDLOCK hint on your MERGE target, your statement is vulnerable to race conditions. In reviewing customer code and questions out in the community, I don't recall ever coming across a HOLDLOCK hint naturally, except in cases where someone was demonstrating the very race condition I'm talking about.

It can work without it but I am not sure if then you could encounter these Concurrency Issues.
If you think your use case would better without if I could change this so that using HOLDLOCK would be an option in BulkConfig.

Also check: HOLDLOCK required in MERGE with UPDATE and UPSERT anti-pattern.

[borisdj]

BulkConfig WithHoldlock added.