By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

why deadlock in this SPROC

P: 2
I am running a stored procedure on SQL2005.

This is what the stored procedure contains

SELECT * FROM Table1 WHERE Id = @IdParam;

If (@@ROWCOUNT = 0)
BEGIN
INSERT INTO Table1 (@IdParam, Value1, Value2, Value3);
END
ELSE
BEGIN
UPDATE Table1 SET Col1 = Value1, Col2 = Value2, Col3 = Value3 WHERE Id = @IdParam;
END

This stored procedure is not called in a transaction.
When 2 or more processes are trying to execute the stored procedure it causes a dead lock sometimes. I am not able to understand why this would cause a dead lock. Any ideas?
Mar 25 '09 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
It's probably because of your UPDATE statement. Make the update more specific so that it will only affect few records and run faster.


-- CK
Mar 25 '09 #2

P: 2
I am only updating once record. My Id Column is the primary key.
Mar 25 '09 #3

ck9663
Expert 2.5K+
P: 2,878
I can't see anymore reason. Try removing the BEGIN and END. You only have a single statement.

Also, try to monitor the jobs.

-- CK
Mar 25 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.