What are my locking options for a Merge statement?

Question

I have a stored procedure that performs a MERGE statement.

It seems like it locks the whole table by default when performing the merge.

I'm calling this stored procedure inside of a transaction where I'm also doing some other stuff and I wish it would only lock the rows affected.

I tried the hint MERGE INTO myTable WITH (READPAST) and it seemed to lock less. But there was a warning in the ms doc that said it could insert duplicate keys, bypassing even the primary key.

Here is my table schema:

CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'WANG')
INSERT INTO StudentDetails
VALUES(2,'JOHNSON')
GO

CREATE TABLE StudentTotalMarks
(
Id INT IDENTITY PRIMARY KEY,
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
GO

Here is my stored procedure:

CREATE PROCEDURE MergeTest 
    @StudentId int,
    @Mark int
AS  

WITH Params
AS
(
    SELECT @StudentId as StudentId,
        @Mark as Mark
)
    MERGE StudentTotalMarks AS stm
    USING Params p
    ON stm.StudentID = p.StudentId
    WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
    WHEN MATCHED THEN UPDATE SET stm.StudentMarks = p.Mark
    WHEN NOT MATCHED THEN
        INSERT(StudentID,StudentMarks)
        VALUES(p.StudentId, p.Mark);
GO

Here is how I'm observing the locking:

begin tran
EXEC MergeTest 1, 1

And then in another session:

EXEC MergeTest 2, 2

The second session waits for the first to complete before proceeding.

Answer 1

You need to give the query processor a more efficient access path to locate StudentTotalMarks records. As written, the query requires a full scan of the table with a residual predicate [StudentID] = [@StudentId] applied to each row:

The engine takes U (update) locks when reading as a basic defence against a common cause of conversion deadlocks. This behaviour means the second execution blocks when trying to obtain a U lock on the row already locked with an X (exclusive) lock by the first execution.

The following index provides a better access path, avoiding taking unnecessary U locks:

CREATE UNIQUE INDEX uq1 
ON dbo.StudentTotalMarks (StudentID) 
INCLUDE (StudentMarks);

The query plan now includes a seek operation on StudentID = [@StudentId], so U locks are only requested on target rows:

The index is not required to be UNIQUE to solve the issue at hand (though the INCLUDE is required to make it a covering index for this query).

Making StudentID the PRIMARY KEY of the StudentTotalMarks table would also solve the access path problem (and the apparently redundant Id column could be removed). You should always enforce alternate keys with a UNIQUE or PRIMARY KEY constraint (and avoid adding meaningless surrogate keys without good reason).