Merge Insert/Update prevent PK violations and deadlocks?

Question

I have stored procedure that handles Insert and Update transactions in one of my tables for now. I'm still testing to see if there is any potential problem with this solution and how I can improve the process. This SP takes few arguments then checks for matching ID and preforms Insert or Update. I have read this Article about primary key violation error, showing that MERGE is vulnerable to concurrency problems like a multi-statement conditional INSERT/UPDATE . Seems that they have solved some issues using WITH (HOLDLOCK). I'm new in stored procedure and merge world. I would like to here your opinion if this is reliable code for application with high transactions? I might have multiple users Inserting in the same table or running Update statement at the same time. Also is there any potential issue with parameter sniffing in this case? If it is should I consider using OPTION (RECOMPILE) or that only applies to SELECT search queries? Here is example of my SQL code:

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[SaveMaster]    Script Date: 08/21/2018 10:05:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      M, D
-- Create date: 08/21/2018
-- Description: Insert/Update Master table
-- =============================================
ALTER PROCEDURE [dbo].[SaveMaster] 
   @RecordID INT = NULL,
   @Status BIT = NULL,
   @Name VARCHAR(50) = NULL,
   @Code CHAR(2) = NULL,
   @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
    );
   RETURN @@ERROR;

Here is example on how I call Stored Procedure with server side language (ColdFusion 2016):

<cftransaction action="begin">
    <cftry>
         <cfstoredproc procedure="SaveMaster" datasource="#dsn#">
              <cfprocparam dbvarname="@RecordID" value="#trim(arguments.frm_recid)#" cfsqltype="cf_sql_integer" null="#!len(trim(arguments.frm_recid))#" />
          <cfprocparam dbvarname="@Status" value="#trim(arguments.frm_status)#" cfsqltype="cf_sql_bit" null="#!len(trim(arguments.frm_status))#" />
          <cfprocparam dbvarname="@Name" value="#trim(arguments.frm_name)#" cfsqltype="cf_sql_varchar" maxlength="50" null="#!len(trim(arguments.frm_name))#" />
          <cfprocparam dbvarname="@Code" value="#trim(frm_code)#" cfsqltype="cf_sql_char" maxlength="2" null="#!len(trim(frm_code))#" />
          <cfprocparam dbvarname="@ActionDt" value="#trim(NOW())#" cfsqltype="cf_sql_datetime" />
              <cfprocparam dbvarname="@ActionID" value="#trim(SESSION.UserID)#" cfsqltype="cf_sql_idstamp" null="#!len(trim(SESSION.UserID))#" />

          <cfprocresult name="MasterResult"/>
     </cfstoredproc>

         <cfset local.fnResults = {status : "200", message : "Record successully saved!", RecID : MasterResult.RecID}>

         <cfcatch type="any">
           <cftransaction action="rollback" />
             <cfset local.fnResults = {status : "400", message : "Error! Please contact your administrator."}>
         </cfcatch>
     </cftry>
</cftransaction>

As you can see I expect that Stored Procedure returns RecID that should be returned (Same ID that I pass in my stored procedure for existing records, or if does not exist then will be generated and returned like this for Insert SELECT SCOPE_IDENTITY() AS RecID; or Update like this SELECT @RecordID AS RecID). If anyone have any suggestions and know the best way to return RecID from SP that runs Insert/Update with Merge please let me know.

Answer 1

If anyone have any suggestions and know the best way to return RecID from SP that runs Insert/Update with Merge please let me know.

You can add an OUTPUT clause to your MERGE statement. That will allow you to return a result set containing the new ID and, if you like, what action it selected:

   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.RedIC,$action as Action;

I'm assuming coldfusion will be able to consume this result set. If not, switch to the variant of OUTPUT which populates a table variable instead (OUTPUT ... INTO) and use that to set OUTPUT parameters which you add to the procedure.