Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
WITH (READPAST) instructs SQL Server to just skip rows that are locked by other sessions. Are you sure you want to do that? Also, how many rows in this table are you modifying? Show us the table schema (including indexes) and the MERGE statement you are running. –  Nick Chammas Jun 15 '12 at 0:43
    
@NickChammas thank you for the help, I updated the question with the details. I imagine READPAST would be be bad... –  John Buchanan Jun 15 '12 at 20:06
add comment

1 Answer

up vote 8 down vote accepted

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:

Scan Plan

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:

Seek Plan

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

share|improve this answer
1  
Thank you so much! This solved my real world problem perfectly! I really should have had that index any way. –  John Buchanan Jun 18 '12 at 17:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.