Does MERGE prevent deadlocks and server blocking?

I have started looking at MERGE as an option that I would use in my application to process UPSERT transactions. Seems that a lot of SQL Server experts are recommending this approach. However, while exploring this method I found some common problems that MERGE can cause, for example in
Use Caution with SQL Server's MERGE Statement by Aaron Bertrand.

My system is state wide and I use ColdFusion on the back end. It is multi-thread and we expect a huge volume of requests to our database. Each of these INSERT/UPDATE transactions will handle a single row transaction. That means the user is inserting or updating one row at the time. I use transaction with rollback in ColdFusion when I call stored procedure. Is that enough or would be better to have that in SQL instead?

Here is an example of my MERGE statement used in a stored procedure:

CREATE PROCEDURE [dbo].[SaveMaster] 
   @RecordID INT = NULL, -- Auto increment ID
   @Status BIT = NULL,
   @Name VARCHAR(50) = NULL, 
   @Code CHAR(2) = NULL, --Primary Key
   @ActionDt DATETIME = NULL,
   @ActionID UNIQUEIDENTIFIER = NULL    
AS
   MERGE dbo.Master WITH (HOLDLOCK) AS Target
   USING (SELECT @RecordID,@Status,@Name,@Code,@ActionDt,@ActionID) 
   AS Source (RecordID,Status,Name,Code,ActionDt,ActionID)
      ON Target.RecID = Source.RecordID
   WHEN MATCHED THEN
      UPDATE
    SET Target.Status = Source.Status,
        Target.Name = Source.Name,
        Target.Code = Source.Code,
        Target.ActionDt = Source.ActionDt,
        Target.ActionID = Source.ActionID
   WHEN NOT MATCHED THEN
    INSERT(
        Status,Name,Code,ActionDt,ActionID
    )VALUES(
        Source.Status,
        Source.Name,
        Source.Code,
        Source.ActionDt,
        Source.ActionID
    )
OUTPUT inserted.RecID,$action as Action;

You will see that I use WITH(HOLDLOCK) to prevent primary key violations and deadlocks. After I read the article that is attached in my post, it seems that even having HOLDLOCK still you

In the example above you will see that I use WITH(HOLDLOCK) to prevent
primary key violation and deadlocks.

That is necessary and (normally) sufficient to prevent PK violations with concurrent MERGE. Deadlocks depend on the other locks in your transaction, but if you have a simple clustered index key lock for your MERGE you should be fine.


I use @RecordID parameter to check if record exist and based on that
MERGE will execute Insert or Update. This field is auto incremented id
that I pass back and forth in order to make sure I always update
correct rows in the table.

That is quite strange and probably bad. You should use the Primary Key to "make sure you always update the correct rows". I don't see why you have that column at all, let alone using it in your MERGE. Just use the PK.

StackExchange Database Administrators Q#215585, answer score: 2