August 12, 2011 at 7:03 am
I have a stored procedure that perform a insert/update into a tabel depending on the input parameters. Im experiencing deadlocks when running this stored procedure.
The scenario is:
1. The stored procedure is called simultaneously and rapidly from a .NET application.
2. 99% of the times it is called, an insert is performed into the table
3. Now and again, a deadlock occurs. A deadlock I cannot reproduce in my test environment by executing the SP from different clients (SQMS). Using a profiler I can see, that 2 simultaneous calls to the SP sometimes result in extremely long duration times and that the pid matches that of the deadlock victim.
4. The table which is being inserted data into contains about 5 million rows
5. I do not run the SP within a transaction
6. Users rarely alter the same data
My questions are:
1. Why/hows does this deadlock occur. Should I look elsewhere in my db knowing that the SP does not run within a transaction?
2. What can I do to avoid the deadlock from occurring
I can provide schema if necessary, but Id rather get the theory right first and hopefully solve the problem myself.
SP code:
ALTER PROCEDURE [dbo].[MySP]
(
@id int =null,
@propertyId int=null,
@unitId int =null,
@respondentId int =null,
@default bit=0,
@doubleValue float=null,
@stringValue nvarchar(400)=null,
@respondentReferenceValue int =null,
@unitReferenceValue int =null,
@propertyPredefinedValue int=null,
@dateValue datetime=null
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @existingId int
-- save default value
IF @default=1
BEGIN
SELECT @existingId=id FROM propertyValue WHERE propertyId=@propertyId AND defaultValue=1
END
ELSE IF @unitId IS NOT NULL
BEGIN
SELECT @existingId=id FROM propertyValue WHERE propertyId=@propertyId AND unitId=@unitId
END
ELSE IF @respondentId IS NOT NULL
BEGIN
SELECT @existingId=id FROM propertyValue WHERE propertyId=@propertyId AND respondentId=@respondentId
END
if @existingId IS NULL
BEGIN
INSERT INTO propertyValue (propertyId,unitId,respondentId,defaultValue,doubleValue,stringValue,
respondentReferenceValue,unitReferenceValue,propertyPredefinedValue,dateValue)
VALUES
(@propertyId,@unitId,@respondentId,@default,@doubleValue,@stringValue,
@respondentReferenceValue,@unitReferenceValue,
@propertyPredefinedValue,@dateValue)
SELECT cast(SCOPE_IDENTITY() as int) AS id
END
ELSE
BEGIN
UPDATE propertyValue
SET doubleValue=@doubleValue,
stringValue=@stringValue,
respondentReferenceValue=@respondentReferenceValue,
unitReferenceValue=@unitReferenceValue,
propertyPredefinedValue=@propertyPredefinedValue,
dateValue=@dateValue
WHERE id=@existingId
SELECT @existingId
END
END
August 12, 2011 at 7:44 am
My two cents.
Inserting Data or updating data into a 5M row table should not cause dead locks. What you might want to do is to look at the execution plan of your stored proc. Since this is not in a transaction and there is no trigger (You have not mentioned any trigger) you have couple of options to look for. You probably know this but I thought I would just state it since we do not know all your business rules and applications that run on your DB and we do not know what the execution plan.
1. In this SP you are doing insert first and update second. make sure that there is no other SP that is doing an Update first and insert second.
2. You are either missing an Index or you have way too many unwanted index.
-Roy
August 12, 2011 at 8:09 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2011 at 8:24 am
Thanks for quick replies
I will try and reproduce the error (beginning of next week). I have switched on the 1222 flag as mentioned and hope to be able to post again soon with a result.
August 16, 2011 at 8:46 am
I have looked at the log and found that an index was causing the deadlock. As my SP did not involve any transactions, I found the client code and noticed the calling client method. It appears that the methods executes the SP within a transaction. I then removed the code related to the transaction and I am no longer able to reproduce the error. Case solved!
Thanks for helping out. I learned a lot about query hints, isolation levels and logging in the process so Im quite happy even though it took some time to figure out.
August 16, 2011 at 9:08 am
As long a you didn't break anything by removing the transaction... There are some operations that really should be done as atomic operations
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply