locking issue in MSSQL

Question

Issue: the holdlock script has locking the multi-thread(10) insert/update

We would like to revise the multi-thread script to from (HOLDLOCK) to (UPDLOCK, SERIALIZABLE)

Original script:

MERGE testtable WITH (HOLDLOCK) AS t

USING (SELECT @P0 hjmpTS, @P1 createdTS, @P2 modifiedTS, @P3 TypePkString, @P4 OwnerPkString, @P5 PK, @P6 sealed, @P7 uniqueid, @P8 p_itemcode, @P9 p_bucketuid, @P10 aCLTS, @P11 propTS) AS s ON ( t.PK = s.PK )

WHEN MATCHED THEN UPDATE

SET hjmpTS = s.hjmpTS, createdTS = s.createdTS, modifiedTS = s.modifiedTS, TypePkString = s.TypePkString, OwnerPkString = s.OwnerPkString, PK = s.PK, sealed = s.sealed, uniqueid = s.uniqueid, p_itemcode = s.p_itemcode, p_bucketuid = s.p_bucketuid, aCLTS = s.aCLTS, propTS = s.propTS

WHEN NOT MATCHED THEN INSERT

(hjmpTS, createdTS, modifiedTS, TypePkString, OwnerPkString, PK, sealed, uniqueid, p_itemcode, p_bucketuid, aCLTS, propTS) VALUES (s.hjmpTS, s.createdTS, s.modifiedTS, s.TypePkString, s.OwnerPkString, s.PK, s.sealed, s.uniqueid, s.p_itemcode, s.p_bucketuid, s.aCLTS, s.propTS);

Answer 1

The real issue is usually:

1. Merge acquires range locks
2. serializable semantics protect against phantom inserts
3. concurrent sessions in the same key ranges
4. lock escalation may occur
5. deadlocks/blocking become common

A good approach can be:

UPDATE testtable WITH (UPDLOCK)
SET
    hjmpTS = @P0,
    createdTS = @P1,
    modifiedTS = @P2,
    TypePkString = @P3,
    OwnerPkString = @P4,
    sealed = @P6,
    uniqueid = @P7,
    p_itemcode = @P8,
    p_bucketuid = @P9,
    aCLTS = @P10,
    propTS = @P11
WHERE PK = @P5;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO testtable
    (
        hjmpTS,
        createdTS,
        modifiedTS,
        TypePkString,
        OwnerPkString,
        PK,
        sealed,
        uniqueid,
        p_itemcode,
        p_bucketuid,
        aCLTS,
        propTS
    )
    VALUES
    (
        @P0,
        @P1,
        @P2,
        @P3,
        @P4,
        @P5,
        @P6,
        @P7,
        @P8,
        @P9,
        @P10,
        @P11
    );
END

Advantages:

1. Lower locking footprint
2. better concurrency
3. fewer deadlocks than merge

Answer 2

I did some testing, and the answer is that it does not really matter. That is, the UPDLOCK does not make any difference. MERGE takes UPDLOCK on its own initiative anyway.

As for HOLDLOCK vs. SERIALIZABLE, they are, as Marcin says, synonymous. I much prefer SERIALIZABLE since that is clearer. HOLDLOCK exists out of legacy. Then again, HOLDLOCK is easier to spell. :-)

Answer 3

Try replacing HOLDLOCK with UPDLOCK, SERIALIZABLE in the MERGE statement:

MERGE testtable WITH (UPDLOCK, SERIALIZABLE) AS t

USING (SELECT @P0 hjmpTS, @P1 createdTS, @P2 modifiedTS, @P3 TypePkString, @P4 OwnerPkString, @P5 PK, @P6 sealed, @P7 uniqueid, @P8 p_itemcode, @P9 p_bucketuid, @P10 aCLTS, @P11 propTS) AS s ON ( t.PK = s.PK )

WHEN MATCHED THEN UPDATE

SET hjmpTS = s.hjmpTS, createdTS = s.createdTS, modifiedTS = s.modifiedTS, TypePkString = s.TypePkString, OwnerPkString = s.OwnerPkString, PK = s.PK, sealed = s.sealed, uniqueid = s.uniqueid, p_itemcode = s.p_itemcode, p_bucketuid = s.p_bucketuid, aCLTS = s.aCLTS, propTS = s.propTS

WHEN NOT MATCHED THEN INSERT

(hjmpTS, createdTS, modifiedTS, TypePkString, OwnerPkString, PK, sealed, uniqueid, p_itemcode, p_bucketuid, aCLTS, propTS) VALUES (s.hjmpTS, s.createdTS, s.modifiedTS, s.TypePkString, s.OwnerPkString, s.PK, s.sealed, s.uniqueid, s.p_itemcode, s.p_bucketuid, s.aCLTS, s.propTS);

HOLDLOCK is equivalent to SERIALIZABLE at the table level during the transaction and might cause stronger blocking behavior in concurrent multi-threaded MERGE operations. Using UPDLOCK, SERIALIZABLE should narrow the locking intent to update locks but still protect against phantom inserts during the MERGE match evaluation.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin