January 5, 2012 at 12:35 am
Hi,
I have two stored procedures:
CREATE PROCEDURE OuterSP
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- Some code here
EXECUTE InnerSP;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH
END
CREATE PROCEDURE InnerSP
BEGIN
DECLARE @Retry AS BIT = 1;
DECLARE @Tries AS TINYINT = 0 ;
WHILE(@Retry = 1)
BEGIN
BEGIN TRY
SET @Retry = 0;
-- Some code here;
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 1205)
BEGIN
SET @Tries = @Tries + 1;
IF (@Tries < 3)
SET @Retry = 1;
END
END CATCH
END
END
The inner procedure should try to execute some code three times if it fails. But when the exception occours (error number 1205), the transaction started in outer procedure becomes uncommitable. So, when some code is executed again in while loop an exception is thrown:
"Server: Msg 3930, Level 16. The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."
I tried to create a save point in inner stored procedure and rollback to save point if the error occurs but it seems that save point does not work with doomed transactions.
How can I make it to work?
Thanks,
ioani
January 5, 2012 at 9:37 am
To me, it seems like it would be cleaner to handle the transaction completely in the inner proc. Otherwise, if you can't, if it fails 3 times in the inner I think you're going to need to raise an error so the catch in the outer is executed.
Generally I don't like nesting procs. You may want to think about that.
January 5, 2012 at 9:41 am
To be clear I want to make sure this point is understood ...
With the code you have given, the catch on the outer proc is never going to be executed. The error is 'handled' in the inner proc and so there would never be an error code passed to the calling routine. That's why you would need to manually force that at the point you've tried your three times and are giving up. In Denali we'll have the option to have them bubble up, but for for now we need to do it ourselves.
January 5, 2012 at 9:43 am
Seems that you should figure out the reason you are getting deadlocks instead of kludging around them. There is an issue if you have to code something to try the same code at least 3 times because it is deadlocked. That screams of major issues and no amount of workarounds is going to "fix" this. IMHO you should be working on fixing the cause and not the affect.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply