Why is my MERGE query deadlocking while in a serialized isolation level transaction?

Question

I'm trying to avoid deadlock in with my MERGE query, which may be called by different threads and possibly overlap in execution with the same parameters. My experience with this query is very similar to the scenario described in this question, and I've listed the query below for reference.

CREATE PROCEDURE MergeIt
    @dataToMerge MyTableType READONLY
AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION

        MERGE INTO TargetTable WITH(HOLDLOCK) AS [target]
        USING @dataToMerge AS [source]
        ON [source].KeyPart_1 = [target].KeyPart_1 AND
           [source].KeyPart_2 = [target].KeyPart_2
        WHEN NOT MATCHED THEN
            INSERT(Data, KeyPart_1, KeyPart_2)
            VALUES([source].Data, [source].KeyPart_1, [source].KeyPart_2)
        WHEN MATCHED THEN
            UPDATE SET [target].Data = [source].Data,
                       [target].KeyPart_1 = [source].KeyPart_1,
                       [target].KeyPart_2 = [source].KeyPart_2;

    COMMIT TRANSACTION
RETURN 0

TargetTable has an identity column which serves as the primary key, and furthermore has a uniqueness constraint on the [KeyPart_1, KeyPart_2] column-tuple. MyTableType has a schema similar to TargetTable and also defines a primary key on [KeyPart_1, KeyPart_2] column-tuple.

I am trying to ensure that only one process be allowed to run this MERGE query at any given time, and I thought that the SERIALIZABLE isolation level enforces this. However, this doesn't appear to be the case. I've captured these XML log events that show which resources and locks are in play during the deadlock. One query has an exclusive lock (X), and the other an update lock (U). As I'm typing this, I see that it is not necessary to update the [KeyPart_1, KeyPart_2] column-tuple in the UPDATE clause, which might very well be contributing to the deadlock since that tuple will trigger an index update.

Are there any other suggestions as to how I can resolve this? I suppose I can blindly try to use TABLOCKX as the table hint, but I'd like to understand how the SERIALIZABLE isolation level failed here.

Thanks!

Answer 1

If you want to ensure that only one process is allowed to run this MERGE query (stored procedure) at any given time, then sp_getapplock is a good fit. It is very straight-forward, easy to understand and maintain, as opposed to obscure query hints. I'm not saying that it is impossible to achieve the same effect with hints. It is easier for me to understand the simple mutex.

This is a template of a stored procedure that I use. Adjust the timeout as needed. The caller should be aware of possible timeouts and retry if needed.

CREATE PROCEDURE MergeIt
    @dataToMerge MyTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION
    BEGIN TRY

        DECLARE @VarLockResult int;
        EXEC @VarLockResult = sp_getapplock
            @Resource = 'MergeIt_app_lock',
            @LockMode = 'Exclusive',
            @LockOwner = 'Transaction',
            @LockTimeout = 60000,
            @DbPrincipal = 'public';

        IF @VarLockResult >= 0
        BEGIN
            -- Acquired the lock
            MERGE INTO TargetTable WITH(HOLDLOCK) AS [target]
            USING @dataToMerge AS [source]
            ON [source].KeyPart_1 = [target].KeyPart_1 AND
               [source].KeyPart_2 = [target].KeyPart_2
            WHEN NOT MATCHED THEN
                INSERT(Data, KeyPart_1, KeyPart_2)
                VALUES([source].Data, [source].KeyPart_1, [source].KeyPart_2)
            WHEN MATCHED THEN
                UPDATE SET [target].Data = [source].Data
            ;

        END ELSE BEGIN
            -- timeout waiting for the lock
            -- TODO: handle the problem, e.g. return some error code,
            -- indicating that the caller should retry.
        END;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- TODO: handle the problem. Return some error code?
    END CATCH;

    RETURN <the error code>
END