3

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!

  • Does the deadlock still occur when you rewrite this as separate INSERT and UPDATE routines, also in the same transaction? MERGE is notoriously buggy. – Randolph West Aug 4 '16 at 23:43
  • Did the deadlock go away when you removed the unnecessary update of KeyPart_1 and KeyPart_2? – Mikael Eriksson Aug 5 '16 at 6:55
  • @MikaelEriksson: unfortunately, no. This problem only occurs in our production environment, so it is notoriously difficult to debug in an adhoc fashion. I'm going to spend some time to try to simulate it on a more accessible machine first. – Steve Guidi Aug 7 '16 at 13:43
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

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.