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