467,915 Members | 1,460 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

Avoide DeadLock Condition

rakesh201180
[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
  • viewed: 2167
Share:
2 Replies
rakesh201180
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 2GB
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.

Similar topics

2 posts views Thread by P.Xman | last post: by
2 posts views Thread by Alex | last post: by
4 posts views Thread by Sefi | last post: by
13 posts views Thread by Jonathan Amsterdam | last post: by
3 posts views Thread by Pallav singh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.