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 can end up with deadlocks. I'm not sure if that applies to my code above as well.
I use a @RecordID parameter to check if the row exists, and based on that MERGE will execute an insert or update. This column is an auto-incremented id that I pass back and forth in order to make sure I always update the correct row in the table. This approach is used in more than one transaction in my system.
I'm wondering if this is good enough or I should consider using Primary Key (Code is PK) instead? Or maybe have both RecordID and Code column included? Would that help in performance at all?
I'm exploring the options and the most important trying to prevent deadlocks and server blocking that seems like a big issue with these transactions in SQL world. If anyone have opinion or recommendation for my code please let me know.
Additional considerations
I have received some feedback that insert and update should be separate, and I should try to avoid MERGE for various reasons. Here is another approach that I found. I'm wondering if any of these two would be a good solution and if there is any potential problem with primary key violation or deadlocks:
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran
Or

2)

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

I know that my SQL Server 2008 needs an upgrade but that is something that I do not control unfortunately.
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.