locking issue in MSSQL

Question

locking issue in MSSQL

Anonymous

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);

Viorel 127.3K Reputation points

2026-05-20T18:08:31.2066667+00:00

Maybe it is not necessarily bad that such a locking occurs.
Lakshmi Narayana Garikapati 1,335 Reputation points Microsoft External Staff Moderator

2026-05-25T11:20:46.2466667+00:00

Hi Sam Chung ,

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we'll assist further

Thanks,

Lakshmi
Lakshmi Narayana Garikapati 1,335 Reputation points Microsoft External Staff Moderator

2026-05-28T12:16:00.1333333+00:00

Hi Sam Chung ,

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we'll assist further

Thanks,

Lakshmi

Answer recommended by moderator

Erland Sommarskog 137.5K MVP Volunteer Moderator

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. :-)

0 comments

2 additional answers

Your answer

Viorel 127.3K Reputation points

2026-05-20T18:08:31.2066667+00:00

Maybe it is not necessarily bad that such a locking occurs.
Lakshmi Narayana Garikapati 1,335 Reputation points Microsoft External Staff Moderator

2026-05-25T11:20:46.2466667+00:00

Hi Sam Chung ,

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we'll assist further

Thanks,

Lakshmi
Lakshmi Narayana Garikapati 1,335 Reputation points Microsoft External Staff Moderator

2026-05-28T12:16:00.1333333+00:00

Hi Sam Chung ,

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we'll assist further

Thanks,

Lakshmi

Answer 1

Deepesh Dhake 1,245

The real issue is usually:

Merge acquires range locks
serializable semantics protect against phantom inserts
concurrent sessions in the same key ranges
lock escalation may occur
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:

Lower locking footprint
better concurrency
fewer deadlocks than merge

Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-05-21T06:38:15.41+00:00

No, this is not a good solution. If two processes are running this in parallel with the same values which is not already present in the table, both will find that the UPDATE did not hit any rows, and one of them will die with a PK violation. Which is probably not what you want.
Deepesh Dhake 1,245 Reputation points

2026-05-21T14:12:00.8866667+00:00
You are right. I missed the race condition window. Good catch.

The scenario you describe is exactly the problem.

You can try following analysis

SELECT session_id, blocking_session_id, wait_duration_ms, last_wait_type FROM sys.dm_exec_requests WHERE session_id > 50;

If there are 0 blocked sessions the serializable is fine. If there are 2 blocked session then serializable is acceptable with minor contention. If there are more than 5 blocked session and blocking is more 200 ms then consider SNAPSHOT.

Answer 2

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