I have a stored procedure that performs a
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.