August 2, 2010 at 3:51 pm
Hi,
I am running Store Procedure in sql server 2005 but sometimes i see deadlock error.
Could you please advice me what could be the problem and where i need to identifiy?
I am doing insert/update inside the procedure.
This is written by third party.
Please see the Store Procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[TrkOrd]
(
@CallType INT = 1
,@App VARCHAR(50)
,@OrdNumINT = 0
,@OrdDtDATETIME = NULL
,@OrigReq XML = ''
,@OrdReqXML = ''
,@ResponseXML = ''
,@SubmitFlagBIT = 0
,@ExceptionsVARCHAR(MAX) = ''
)
AS
BEGIN
---BEGIN TRAN T1
DECLARE @Ver AS SMALLINT
IF @CallType = 1
BEGIN
SELECT TOP 1 @Ver = ISNULL(Ver,0) + 1 FROM OrdProcStatus
WHERE App = @App AND OrdNum = @OrdNum
ORDER BY Ver DESC
IF @Ver is NULL
Set @Ver = 0
Insert INTO OrdProcStatus(App,OrdNum,OrdDt,OrigReqMsg,Ver)
VALUES(@App,@OrdNum,@OrdDt,@OrigReq,@Ver)
--IF @@ERROR <> 0 GOTO X
END
ELSE IF @CallType = 2
BEGIN
SELECT TOP 1 @Ver = Ver FROM OrdProcStatus
WHERE App = @App AND OrdNum = @OrdNum
ORDER BY Ver DESC
UPDATE OrdProcStatus
SET
OrdReqMessage = @OrdReq
WHERE App = @App AND OrdNum = @OrdNum AND Ver = @Ver
--IF @@ERROR <> 0 GOTO X
END
ELSE IF @CallType = 3
BEGIN
SELECT TOP 1 @Ver = Ver FROM OrdProcStatus
WHERE App = @App AND OrdNum = @OrdNum
ORDER BY Ver DESC
UPDATE OrdProcStatus
SET
ResponseMessage = @Response
,SubmitFlag = @SubmitFlag
WHERE App = @App AND OrdNum = @OrdNum AND Ver = @Ver
--IF @@ERROR <> 0 GOTO X
END
ELSE IF @CallType = 4
BEGIN
SELECT TOP 1 @Ver = Ver FROM OrdProcStatus
WHERE App = @App AND OrdNum = @OrdNum
ORDER BY Ver DESC
UPDATE OrdProcStatus
SET Exceptions = @Exceptions
WHERE App = @App AND OrdNum = @OrdNum AND Ver = @Ver
--IF @@ERROR <> 0 GOTO X
END
--COMMIT TRAN T1
SELECT 1 AS Success FOR XML RAW--, XMLDATA
--RETURN
--X:
--ROLLBACK TRAN T1
--SELECT 0 AS Success FOR XML RAW--, XMLDATA
END
Appreciated your feedback and guidence
August 2, 2010 at 5:12 pm
Besides the fact that the code is really very inefficiently (dare I say badly) written, the first obvious thing that could be causing the deadlock is the fact that there are first selects of OrderProcStatus.Ver and then inserts of the resulting value. You are probably getting what I call an "intra table deadlock", that is the deadlock is within one table and not across multiple tables as is most common. I would need to see the deadlock information to confirm this. You can enable trace flag 1204 if you want to see the deadlock information in the SQL Error log.
In an "intra table deadlock" what happens is one process is inserting a record, so it locks part of the table, then it tries to lock the index to update this, but a second process is already locking the index and trying to access the locked portion of the table.
This looks like a classic case of trying to make the columns App,OrderNum,Ver unique and ordered by incrementing the Ver value within a given pair or App,OrderNum values. It works, but it can give headaches. It would have been simpler to make Ver an Identity column. This would give the same final result with little loss of functionality. The only loss would be that within the App,OrderNum groups the Ver value would not be consecutive, but I doubt that is critical to the app,and could be managed with a few minor changes.
There's probably no simple solution for you, but you could ask the vendor to put in some Try/Catch error handling and do a retry in the event of a deadlock error. Be careful that they limit the number of retries as they could end up in an infinite loop.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 6, 2010 at 5:54 am
Thanks so much for your detailed answer. I am completely agreed with you that is poor written code.
Its our turn to rewrite the code or fine tune it.
Could you give me some guidence as i am not much sql server expert?
Once thing i would like to bring here as its running for other application and its not causing dead lock but its only causing dead lock for one perticular application.
any thoughts?
Thanks!
August 6, 2010 at 6:02 am
- Are you capturing deadlock info in your sqlserver errorlog ? (startup trace flag -T1222 )
- indeed your sproc cold have been written in a far more optimal way :hehe:
- are your queries supported by indexes ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 10, 2010 at 9:26 pm
Thanks, let me do some analysis and i will get back to you!
August 11, 2010 at 4:37 am
you can also use profiler to see where things are being holding up
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 12, 2010 at 8:47 am
Here is the "bible" for deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Note there are 2 additional parts to this blog series.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 13, 2010 at 11:50 am
Beside the trace flag, I would also configure an alert to notify when this occurs so you could capture the issue.
August 16, 2010 at 1:25 am
Twinsoft SME (8/13/2010)
Beside the trace flag, I would also configure an alert to notify when this occurs so you could capture the issue.
in perfmon ??? any link/article where the procedure is mentioned ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 20, 2010 at 5:24 am
Thanks.
If you provide me the link or any guide lines to set up alert that will be great!
Regards,
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply