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

Avoide DeadLock Condition

rakesh201180
P: 4
[IMG]file:///C:/DOCUME%7E1/ADMINI%7E1/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG]Need help....!! I have this stored procedure which is run by Multiple Users at the same time and it is causing deadlock sometimes. Sometimes it is causing deadlock at update and sometimes at insert. I am also using rowlock. Any changes need to be made or if I am doing anything wrong?


Thanks !



CREATE PROCEDURE [dbo].[spSecPriceUpdate]

(@RunID int)

AS

Begin Tran t1

UPDATE tblB_SecPrice WITH (ROWLOCK)
SET SecID = M.SecID
FROM tblB_SecPrice B, tblSecMaster M
WHERE B.Security = M.Label AND
B.RunID = @RunID


UPDATE tblB_SecPrice WITH (ROWLOCK)
SET PropertyID = M.IntA
FROM tblB_SecPrice B, tblMapIIS M
WHERE B.Source = M.StrC AND
M.MapID = 22 AND
B.RunID = @RunID


INSERT INTO tblResultFlt with (ROWLOCK)
(RunID, SecID, PropertyID, PropertyValue, AsOfDate)
SELECT RunID, SecID, PropertyID, Price AS PropertyValue, AsOf AS AsOfDate
FROM tblB_SecPrice B WITH (ROWLOCK)
WHERE B.SecID IS NOT NULL AND
B.PropertyID IS NOT NULL AND
B.Price IS NOT NULL AND
B.AsOf IS NOT NULL AND
B.RunID = @RunID


DELETE FROM tblB_SecPrice WITH (ROWLOCK)
WHERE SecID IS NOT NULL AND
PropertyID IS NOT NULL AND
Price IS NOT NULL AND
AsOf IS NOT NULL AND
RunID = @RunID
Jan 14 '09 #1
Share this Question
Share on Google+
2 Replies


rakesh201180
P: 4
I am trying while Loop for date increment
But that also cousing Deadlock.
Any one help any other thing to remove while loop

While(@Fromdate<@Todate)
begin


SET @FROMDATE=@FROMDATE+1
End

tHANKS
Jan 14 '09 #2

ck9663
Expert 2.5K+
P: 2,878
Do you really need to issue ROWLOCK? And do you have the necessary index on those tables?


-- CK
Jan 14 '09 #3

Post your reply

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