Why does upsert result in a deadlock?

I'm doing the below upsert statement, which I need to have run multiple times in a short timeframe. I wrote this statement based on examples from Aaron Bertrand's Please stop using this UPSERT anti-pattern post on SQLPerformance.com. The majority of my UPSERTS are INSERTS, so I used that example from his post.

BEGIN TRANSACTION;

INSERT AggregationMeasurement (ValueFloat, Date, AggregationConfigurationId)
SELECT @ValueFloat, @Date, @AggregationConfigurationId
WHERE NOT EXISTS
(
    SELECT 1 FROM dbo.AggregationMeasurement WITH (UPDLOCK, SERIALIZABLE)
    WHERE Date = @Date AND AggregationConfigurationId = @AggregationConfigurationId
);

IF @@ROWCOUNT = 0
BEGIN
UPDATE AggregationMeasurement SET 
    ValueFloat = @ValueFloat
WHERE Date = @Date AND AggregationConfigurationId = @AggregationConfigurationId;
END

COMMIT TRANSACTION;

However, every time this results in several deadlocks, as shown here:

```








INSERT AggregationMeasurement (ValueFloat, Date, AggregationConfigurationId)
SELECT @ValueFloat, @Date, @AggregationConfigurationId
WHERE NOT EXISTS
(
SELECT 1 FROM dbo.AggregationMeasurement WITH (UPDLOCK, SERIALIZABLE)
WHERE Date = @Date AND AggregationConfigurationId = @AggregationConfigurationId


EXEC sp_UpsertAggregateMeasurement @ValueFloat = @ValueFloat_tmp, @Date = @Date_tmp, @AggregationConfigurationId = @AggregationConfigurationI



Proc [Database Id = 5 Object Id = 1554104577]





INSERT AggregationMeasurement (ValueFloat, Date, AggregationConfigurationId)
SELECT @ValueFloat, @Date, @AggregationConfigurationId
WHERE NOT EXISTS

The first thing you want to do is make sure you have an index that meets the needs of your WHERE clause. In this case, it would need key columns on Date and AggregationConfigurationId. Doing this will ensure the locks are held for as brief a period as possible.

After that, if your UPSERT is predominately going to be UPDATES instead of INSERTS, you'd want revise your UPSERT statement to the below statement. Otherwise, the statement you are using looks good.

BEGIN TRANSACTION;

UPDATE AggregationMeasurement WITH (UPDLOCK, SERIALIZABLE)
SET ValueFloat = @ValueFloat
WHERE Date = @Date
    AND AggregationConfigurationId = @AggregationConfigurationId;

IF @@ROWCOUNT = 0
BEGIN
    INSERT AggregationMeasurement
        (ValueFloat, Date, AggregationConfigurationId)
    VALUES (@ValueFloat, @Date, @AggregationConfigurationId)
END

COMMIT TRANSACTION;

StackExchange Database Administrators Q#305233, answer score: 4