Stored Procedure - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure

Author

Topic

poratips
Posting Yak Master

105 Posts

Posted - 2010-08-06 : 08:00:53

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) 
,@OrdNum	INT = 0	
,@OrdDt	DATETIME = NULL
,@OrigReq XML = ''
,@OrdReq			XML = ''
,@Response			XML = ''
,@SubmitFlag			BIT = 0
,@Exceptions			VARCHAR(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

Thanks for your help!

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 08:35:54

SELECT @XXX = ...
INSERT ... @XXX ...

or

SELECT @XXX = ...
UPDATE ... @XXX ...

would be much more reliable if done in one statement, rather than two. Or you can use hints to prevent other processes getting in the way, but I prefer a single statement approach.

If you just have Reads blocking Writes then consider setting READ_COMITTED_SNAPSHOT on the database (probably a good idea to do that anyway in most cases)

poratips
Posting Yak Master

105 Posts

Posted - 2010-08-06 : 09:15:44

Thanks Kristen.
Reall appreciate your prommpt resposne.

you are asking to use this way:


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,
						ResponseMessage = @Response,
						SubmitFlag = @SubmitFlag,
					        Exceptions = @Exceptions
		WHERE App = @App AND OrdNum = @OrdNum AND Ver = @Ver
		--IF @@ERROR <> 0 GOTO X
	END

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 09:34:14

is that different? (I've only looked briefly at it)

poratips
Posting Yak Master

105 Posts

Posted - 2010-08-06 : 11:22:31

I just combined all update in one but i am not much efficient in sql server so you might see obvious, please advice me.

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 11:37:20

Instead of


SELECT TOP 1 @Ver = Ver FROM OrdProcStatus 
		WHERE App = @App AND OrdNum = @OrdNum
		ORDER BY Ver DESC
		
		UPDATE OrdProcStatus 
					SET 
						OrdReqMessage = @OrdReq,
						ResponseMessage = @Response,
						SubmitFlag = @SubmitFlag,
					        Exceptions = @Exceptions
		WHERE App = @App AND OrdNum = @OrdNum AND Ver = @Ver

you need something like


		UPDATE OrdProcStatus 
					SET 
						OrdReqMessage = @OrdReq,
						ResponseMessage = @Response,
						SubmitFlag = @SubmitFlag,
					        Exceptions = @Exceptions
		WHERE App = @App AND OrdNum = @OrdNum AND Ver = @Ver
						(
							SELECT TOP 1 @Ver = Ver FROM OrdProcStatus 
							WHERE App = @App AND OrdNum = @OrdNum
							ORDER BY Ver DESC
						)

so that it is in one statement. Even then you may need a HINT to prevent changes to OrdProcStatus

poratips
Posting Yak Master

105 Posts

Posted - 2010-08-06 : 16:06:58

Thanks so much Kristen, i will try to implement.

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources