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

[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

[ck9663]

Do you really need to issue ROWLOCK? And do you have the necessary index on those tables?


-- CK