Why is SQL Server upsert with MERGE inserting multiple records?

Question

I have an upsert implemented with the MERGE statement in a stored procedure in Microsoft SQL Server 2017 Standard Edition.

The problem is I'm getting multiple inserts when I make concurrent calls to the stored procedure. I'm able to reproduce the behavior using JMeter with lots of concurrent threads. JMeter hits a Java web app which calls the stored procedure using JDBC. After deleting all the rows and running JMeter, it often creates just 1 row, but sometimes it creates 2 or more rows. I think I've seen it create up to 6 rows.

I thought this was impossible using MERGE. The answers to this question all say a transaction is unnecessary: Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?

Basically, I want the table to store the max size (LQ_SIZE) value for every day, along with the time (LQ_TIMESTAMP) when that max size occured. I'm doing two slightly unusual things in my upsert. 1. I'm matching on the timestamps cast to a date, so I'm inserting or updating the row for the day ignoring the time. 2. My WHEN MATCHED clause has an AND condition so it only updates the row if the new size is greater than the current size.

Here's my table and stored procedure with MERGE statement:

CREATE TABLE LOG_QUEUE_SIZE (
    LQ_APP_ID SMALLINT NOT NULL,
    LQ_TIMESTAMP DATETIME2,
    LQ_SIZE INT
);
GO

CREATE PROCEDURE LOG_QUEUE_SIZE (
    @P_TIMESTAMP    DATETIME2,
    @P_APP_ID       SMALLINT,
    @P_QUEUE_SIZE   INT
)
AS 
BEGIN
    -- INSERT or UPDATE the max LQ_QUEUE_SIZE for today in the LOG_QUEUE_SIZE table
    MERGE 
        LOG_QUEUE_SIZE target
        USING 
            (SELECT @P_APP_ID NEW_APP_ID, @P_TIMESTAMP NEW_TIMESTAMP, @P_QUEUE_SIZE NEW_SIZE) source
        ON 
            LQ_APP_ID=NEW_APP_ID 
            AND CAST(NEW_TIMESTAMP AS DATE) = CAST(LQ_TIMESTAMP AS DATE) -- Truncate the timestamp to the day
    WHEN MATCHED AND NEW_SIZE > LQ_SIZE THEN -- Only update if we have a new max size for today
        UPDATE 
        SET 
            LQ_TIMESTAMP = NEW_TIMESTAMP, 
            LQ_SIZE = NEW_SIZE
    WHEN NOT MATCHED BY TARGET THEN -- Otherwise insert the new size
        INSERT 
            (LQ_APP_ID, 
            LQ_TIMESTAMP, 
            LQ_SIZE)
        VALUES
            (NEW_APP_ID, 
            NEW_TIMESTAMP, 
            NEW_SIZE);
END

Using a transaction (with BEGIN TRAN...COMMIT around the MERGE) appears to prevent the problem, but the performance is terrible.

Why am I getting multiple inserts if MERGE is atomic? How can I prevent it?

T McKeown · Accepted Answer · 2020-03-26T12:57:22.917

3

MERGE, results in multiple SQL statements, that means it can result in possible concurrency conflicts. You should implement locking:

MERGE dbo.TableName WITH (HOLDLOCK) AS target
USING ... AS source ...;

https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

edited Mar 26 '20 at 12:57

answered Mar 25 '20 at 20:51

T McKeown

12,971
1
25
32

I see you're right, thanks. Similar answers to [this question](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server/2107205). Though, the performance is so awful, I will just live with the possible extra rows. IRL, this will rarely happen. – tbird Mar 25 '20 at 21:04

Why is SQL Server upsert with MERGE inserting multiple records?

1 Answers1