4

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.

  • 1
    Thank you for your recent well-asked questions. I have relocated partial answers left in comments to a Community Wiki answer that can be edited and maintained by the broadest possible set of users. Please help this site's focus on questions and answers by keeping your questions up to date (by editing) as requests for clarification are left in comments, and by asking separate follow-up questions rather than raising and discussing related points in comments. – Paul White Aug 24 '18 at 7:21
2

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.

  • David, thanks for your feedback. I asked this question because I see that some people were experiencing deadlocks even they used WITH (HOLDLOCK). Maybe that's not the case with my transaction. Regarding RecordID and using Auto-increment ID, that is what my Senior Level programmers use and what they thought me. I agree that is strange and since I'm building new system now where I'm responsible I can change and improve things. – espresso_coffee Aug 22 '18 at 14:01
1

Community wiki answer:

Anecdotally, MERGE has been reported to be a worse performer, and harder to performance tune, than separate INSERT, UPDATE, and DELETE statements.

Definitely keep the separate statement in a stored procedure, and consider wrapping them in a transaction (although this can introduce longer held locks). Avoid ColdFusion's transaction handling; trust SQL Server's instead.

The IF EXISTS method in the question seems inferior. The opening check does the same thing the WHERE clause in the UPDATE will do. The second form is a lot tidier. You need TRY/CATCH though for proper error and transaction handling (quick example and a more comprehensive approach).

You always have the potential to code yourself into a deadlock. MERGE wouldn't solve that. You shouldn't get any PK violations here but that's what TRY/CATCH is for. If two transactions somehow manage to try to insert the exact same key at the exact same nanosecond, one of them will win, and you can use TRY/CATCH to decide how you want to handle the other one - they can retry (in which case it will update), or you can force it to fail and make sure the user knows there was a conflict and can act accordingly. See Implementing SQL Server Transaction Retry Logic for failed transactions by Daniel Farina.

People think blocking is a bad thing but this is exactly what it's for - to prevent two people from trying to operate on the same data simultaneously. If you're worried about avoiding blocking (e.g. writers blocking readers and vice-versa) you should look at row-versioning isolation levels like read-committed snapshot isolation (RCSI) and snapshot isolation (SI).

Also take a look at Be Careful with the Merge Statement and SQL Server UPSERT Patterns and Antipatterns by Michael Swart.

0

Merge statements aren't a magic bullet to blocking or contention, especially with SQL 2008 (it got better in later versions). Best way to think of them is as syntactic sugar for separate insert/update statements. That being said, they do more succinctly describe the desired action than the other methods so I tend to prefer them when possible.

One method that I've found, especially given that this procedure appears to work on a single record at a time is to do something like what I've done below. I use a CTE for the Target of the Merge where I select the specific record I want, I also hint with ROWLOCK and UPDLOCk to help keep the engine from escalating locks.

;WITH CTE_Target AS
        (
        SELECT RecID, Status, Name, Code, ActionDT, ActionID
        FROM dbo.Master WITH (ROWLOCK, UPDLOCK, HOLDLOCK)
        WHERE RecID = @RecordID
        )
    , CTE_Source AS
        (
        SELECT RecordID, Status, Name, Code, ActionDt, ActionID
        FROM (VALUES (@RecordID, @Status, @Name, @Code, @ActionDt, @ActionID)
                ) AS P (RecordID,Status,Name,Code,ActionDt,ActionID)
        )
   MERGE CTE_Target AS Target
   USING CTE_Source AS Source 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;

But as I mentioned, it is still possible to get a deadlock and in a multi-threaded environment it can be difficult to resolve. One way is to make your statements faster which reduces the window for a deadlock to occur.

  • One thing that I would like to point out is ROWLOCK and UPDLOCK. I read that they can be bad for performance, is that the case with single row Insert/Update as well? Again, merge is something that I explore and would like to consider. In other hand if there is still chance to get deadlocks and server blocking I would like to hear what would be another option or approach for this kind of multi-thread transactions in SQL? I know that my SQL 2008 needs an upgrade but that is something that I do not control unfortunately. Thanks for your feed back. – espresso_coffee Aug 22 '18 at 13:05
  • As with anything, you need to test in your environment. ROWLOCK/UPDLOCk can be performance killers, especially if you are updating more than a few rows at a time. But they hint that you don't want the engine to escalate to a higher lock (such as page or table). What kind of race condition happens? Is it the same update at the same time? Or is it that multiple versions of the row are trying to update at the same time? – Jonathan Fite Aug 22 '18 at 15:17
  • I'm only updating one row at the time. The only reason I asked about ROWLOCK/UPDLOCK is because I heard other people complained about performance issues. Still there is a lot of arguments about MERGE and problems that come with that. – espresso_coffee Aug 22 '18 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.