Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2007-08-30 : 20:19:41
|
| Folks: Need help....!! I have this stored procedure which is run by Multiple Users (around 25) 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 ! SCRIPT # 1: INSERT INTO Analysis..tblB_SecPrice WITH (ROWLOCK) (Security, Source, Price, AsOf, RunID) SELECT 'GN 466037', '|SCM050', 105.014, '08/29/07', 1569756SCRIPT # 2: (Procedure):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 commit tran t1 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2007-08-30 : 22:24:38
|
| For insert, you cannot have any row lock, as the record doesn't even exists. The main thing is the update, if your sp got run at the same time by more then 1 user, and the 2 update statements are updating the same record (from the @RunID) and at the end its trying to delete those reocrds, it might cause the deadlock. It more on the logic side rather then SQL. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-04 : 15:56:56
|
| fatal deadlock on your updates in tblB_SecPrice?have you tried to query if you'll be updating the same rows?--------------------keeping it simple... |
|
|
|
|
|
|
|
|
SP and Deadlock