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
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.
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.