October 11, 2022 at 9:09 am
Hello everyone
In my code, I often use nested stored procedures. The processing of my stored procedures all perform several operations in the database, so they are encapsulated in a transaction. My stored procedures children can also be performed as a parent. I therefore start all my procedures stored as a child by a BEGIN TRAN after the BEGIN TRY and in case of error, I test the following condition before rollback: IF @@trancount = 1 and xact_state()<>0. My question is, when I perform an EXECUTE of a stored procedure, is that the value of @trancount can be changed by another EXECUTE of a stored procedure, outside of internal or external stored procedure calls.
Example:
the stored procedure A calls the stored procedure B
the stored procedure C does not use any other stored procedure
The processing of each stored procedure is encapsulated in a transaction
In the same user session, the case may occur:
I execute the stored procedure A, the value of @@trancount changes to 1 and then to 2 after the BEGIN TRAN of the stored procedure B
Now, let’s imagine that my procedure is not finished but, at the same time, the SQL Server engine executes the procedure stored C on the same session. Is the value of @trancount after the BEGIN TRAN of the stored procedure C is 1 or 3?
October 11, 2022 at 10:37 am
>>Now, let’s imagine that my procedure is not finished but, at the same time, the SQL Server engine executes the procedure stored C on the same session.
If you are running nested stored procedures they run synchronously so this will not happen. When you BEGIN TRAN @@trancount will increment and when you COMMIT @@trancount will decrement. Only when @@trancount = 1 will a COMMIT actually commit to the log. ie You need to be careful about long running transactions and the consequences for concurrency.
Transaction handling is not straight forward in SQL Server. Try reading the whole of this series by Erland Sommarskog:
https://www.sommarskog.se/error_handling/Part1.html
October 11, 2022 at 12:51 pm
Thank you for your answer but I think I may have misspoken, I will rephrase my question: is this what is possible to see at the beginning of a stored procedure (before BEGIN TRAN) that the value of @trancount is greater than 0 because another independent transaction is in progress in the same session? Or, in the same session, all processing is done sequentially. I ask this question because as I explained before, to finish a transaction I perform the following test before a rollback: if xact_state()<>0 and @@trancount=1 then rollback... Only if the value can be different from 1, it won’t work...
October 11, 2022 at 1:32 pm
No. Other sessions won't change your session's transaction count (except perhaps if they caused sufficient blocking to make your connection/transaction time out).
A trigger could execute in/as a result of your transaction, and could rollback your transaction or create a nested transaction that could alter transaction count.
October 11, 2022 at 1:53 pm
Yes I know that other sessions will not change the trancount value. What I want to know is whether, in the same session, several independent stored procedures could be performed at the same time... This would have the consequence of having the possibility to start the execution of a stored procedure with a trancount value greater than 0...
October 11, 2022 at 2:53 pm
What I want to know is whether, in the same session, several independent stored procedures could be performed at the same time..
Obviously not. (Except if you are bold enough to try a multi-threaded CLR procedure. Even then it will connect as a different session.)
This would have the consequence of having the possibility to start the execution of a stored procedure with a trancount value greater than 0...
A SP can start with a @@trancount > 0 for a number of reasons.
Most of the time the only reason to have nested stored procedures is to avoid recompiles when IF type logic cannot be done in an application. In this case nested transactions can be avoided either by having the transaction controlled by the outer procedure or by each sub procedure handling it's own transactions.
I suspect you should not be using nested transactions and again I strongly recommend you read Erland Sommarskog's papers.
October 11, 2022 at 2:58 pm
Thank you for your answer. It’s now clearer for me.
October 12, 2022 at 8:31 am
After reading the excellent information and resources you provided me, I applied the following model in all my nested stored procedures:
SET XABORT, NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
-- treatment
COMMIT
END TRY
BEGIN CATCH
IF XACT_STATE()<>0
BEGIN
ROLLBACK
EXECUTE sp_i_ts_error -- logging error into table
END
;THROW
END CATCH
After several tests, this model seems to meet my needs but I have doubts because I understood from reading all the information you transmitted to me that the management of errors in nested stored procedures is complex... I wanted to get your opinion on my model because the other models I saw on the forums are not similar. Most of them test in the child stored procedure if a transaction does not already exist... My needs are the following:
- If an error is encountered in a child stored procedure, the complete processing must be cancelled. I think this need will be met through the use of XABORT in ON
- Then I want the error to go back to the parent procedure and this error to be recorded in a log table
Do you think my model is good?
Thanks again in advance
October 12, 2022 at 8:32 am
This was removed by the editor as SPAM
October 12, 2022 at 9:34 am
I think you should check for @@TRANCOUNT and not XACT_STATE().
Also remove all TRYs, CATCHes and TRANsactions in the SubProcs. Something like the following should then work reasonably well:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.YourOuterProc
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRY
BEGIN TRAN;
EXEC dbo.SubProc1;
EXEC dbo.SubProc2;
EXEC dbo.SubProc3;
COMMIT;
END TRY
BEGIN CATCH
EXEC dbo.ExceptionHandler;
END CATCH
GO
with the ExceptionHandler being something like:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.ExceptionHandler
@ProcID int = NULL
,@ExtraMessage nvarchar(1000) = NULL
AS
SET NOCOUNT, XACT_ABORT ON;
DECLARE @ErrorNumber int = ERROR_NUMBER()
,@ErrorSeverity int = ERROR_SEVERITY()
,@ErrorState int = ERROR_STATE()
,@ErrorProcedure nvarchar(126) = ERROR_PROCEDURE()
,@ErrorLine int = ERROR_LINE()
,@ErrorMessage nvarchar(2048) = ERROR_MESSAGE();
IF @@TRANCOUNT > 0
ROLLBACK;
IF @ProcID IS NOT NULL
SET @ErrorProcedure = N'[' + OBJECT_SCHEMA_NAME(@ProcID) + N'].[' + @ErrorProcedure + N']';
IF @ExtraMessage IS NOT NULL
SET @ErrorMessage = @ErrorMessage + NCHAR(13) + NCHAR(10) + @ExtraMessage;
-- Email alert
DECLARE @mailbody nvarchar(4000) =
N'Error ' + CAST(@ErrorNumber AS nvarchar(20)) + N' occurred in procedure '
+ COALESCE(@ErrorProcedure, N'NA') + N' at line ' + CAST(@ErrorLine AS nvarchar(20))
+ N' with a severity of ' + CAST(@ErrorSeverity AS nvarchar(20))
+ N' and a state of ' + CAST(@ErrorState AS nvarchar(20))
+ N'.' + NCHAR(13) + NCHAR(10) + NCHAR(13) + NCHAR(10)
+ @ErrorMessage;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'xxx'
,@recipients = 'xxx@xxx.com'
,@importance = 'High'
,@subject = 'xxx'
,@body = @mailbody;
-- write to log
INSERT INTO dbo.Exceptions
(
ExceptionDate, ErrorNumber, ErrorSeverity, ErrorState
,ErrorProcedure, ErrorLine, ErrorMessage
)
SELECT CURRENT_TIMESTAMP, @ErrorNumber, @ErrorSeverity, @ErrorState
,COALESCE(@ErrorProcedure, N'NA'), @ErrorLine, COALESCE(@ErrorMessage, N'No Message');
RAISERROR
(
N'Error %d occurred in procedure %s at line %d. %s'
,@ErrorSeverity
,@ErrorState
,@ErrorNumber
,@ErrorProcedure
,@ErrorLine
,@ErrorMessage
);
GO
October 12, 2022 at 12:17 pm
This was removed by the editor as SPAM
October 12, 2022 at 12:20 pm
This was removed by the editor as SPAM
October 12, 2022 at 12:30 pm
Thank you very much for your answer
Is your example valid too if I can’t remove the TRY CATCH and BEGIN TRAN from the child procedures because they can be called also as parent procedure... ?
Why did you remove try catch and begin tran from child stored procedures?
October 12, 2022 at 2:03 pm
It is up to you but personally, if at all possible, I would avoid nested transactions like the plague.
I would still strip out the transactions from SubProc1 etc and rename SubProc1 to SubProv1Sub. I would then create a new SubProc1 like YourOuterProc but only calling SubProv1Sub. Obviously YourOuterProc would then also call SubProc1Sub etc
ie I would try to ensure @@TRANCOUNT never exceeds 1.
As for Try/Catch, just let the outer procedure handle exceptions.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply