ROWVERSION based optimistic concurrency with MERGE statements

Question

I have a simple MERGE statement that I'd like to add optimistic concurrency to.

DECLARE @Id         INT,
        @IsEnabled  BIT,
        @RowVersion ROWVERSION;

MERGE [dbo].Subscription WITH (HOLDLOCK) AS [Target]
USING
( SELECT @Id, @IsEnabled )
AS [Source]
( Id, IsEnabled )
ON
( [Target].Id = [Source].Id )
WHEN MATCHED THEN
    UPDATE SET IsEnabled = [Source].IsEnabled
WHEN NOT MATCHED THEN
    INSERT ( IsEnabled ) VALUES ( [Source].IsEnabled );

IF @Id IS NULL
BEGIN
    SELECT @Id = CAST(SCOPE_IDENTITY() AS INT);
END;

SELECT @Id AS Id;

We use ROWVERSION to provide the change tracking.

When the RowVersion values don't match then no changes should take place, and we should return a flag indicating that the concurrency check failed so the user can be asked to try again.

I found one suggested approach which performs an UPDATE, setting a column to it's existing value when it detects different ROWVERSIONs. This satisfies MERGEs desire to have at least on column updated, without actually changing the value.

DECLARE @Id         INT,
        @IsEnabled  BIT,
        @RowVersion ROWVERSION;

DECLARE @Result TABLE (WasChanged BIT, Id INT);

MERGE [dbo].Subscription WITH (HOLDLOCK) AS [Target]
USING
( SELECT @Id, @IsEnabled, @RowVersion )
AS [Source]
( Id, IsEnabled, RowVersion )
ON
( [Target].Id = [Source].Id )
WHEN MATCHED THEN
    UPDATE SET IsEnabled = CASE WHEN [Source].Rowversion = [Target].Rowversion          
                                THEN [Source].IsEnabled ELSE [Target].IsEnabled
                           END
WHEN NOT MATCHED THEN
    INSERT ( IsEnabled ) VALUES ( [Source].IsEnabled )
OUTPUT  CASE WHEN [Source].[Rowversion] = INSERTED.[Rowversion]
            THEN 0 ELSE 1
        END AS ConcurrencyCheckFailed, INSERTED.Id AS Id
INTO    @Result;

SELECT ConcurrencyCheckFailed, Id FROM @Result;

This does initially appear to be an okay workaround, but performing an UPDATE, even one that doesn't actually change any values, would still cause that row's ROWVERSION to be incremented for no good reason.

That changing of the ROWVERSION can lead to failed concurrency checks, and so the user having to retry. And since the change was for no good reason, those failures are very undesirable.

Am I taking the wrong approach trying to get MERGE to work how I want?

Or, is there a nifty way to use MERGE with ROWVERSION based optimistic concurrency?

The more I look at the MERGE statement, the more I think it's simply the wrong tool for this task. It doesn't seem to have any support for this way of working, and the solutions I find online are partially working workarounds rather than proper approaches.

ROWVERSION based optimistic concurrency with MERGE statements

0 Answers