Developing a stored procedure @Trancount
You are developing a stored procedure with the following requirements: ✑ Accepts an integer as input and inserts the value into a table. ✑ Ensures new transactions are committed as part of the outer transactions. ✑ Preserves existing transactions if the transaction in the procedure fails. ✑ If the transaction in the procedure fails, rollback the transaction. How should you complete the procedure? To answer, select the appropriate options in the answer area.
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-2017
Box 1: @TranCount> 0 - Procedure called when there is an active transaction. Create a savepoint to be able to roll back only the work done in the procedure if there is an error. Box 2: @TranCount = 0 - -- @TranCount = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started. Box 3: XACT_STATE() <> -1 - If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.
Discussion:
Bartek
Box 2: No metter what You choosed. May be @@TRANCOUNT = 1 or @Trancount = 0
RYP
No, because at that point @@TRANCOUNT will be 1 or above, since we explicitly started a transaction if one wasn't already started. The point of the code where Box2 exists is checking "was there a transaction before I started? if not, we are safe to commit" to avoid committing transactions started outside the stored procedure.
See: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver15
USE AdventureWorks2012; GO IF EXISTS (SELECT name FROM sys.objects WHERE name = N'SaveTranExample') DROP PROCEDURE SaveTranExample; GO CREATE PROCEDURE SaveTranExample @InputCandidateID INT AS DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 SAVE TRANSACTION ProcedureSave; ELSE BEGIN TRANSACTION; BEGIN TRY DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; IF @TranCounter = 0 COMMIT TRANSACTION; END TRY BEGIN CATCH IF @TranCounter = 0 ROLLBACK TRANSACTION; ELSE IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH GO
1. "AS.....DECLARE @TranCounter INT; ": Detect whether the procedure was called from an active transaction and save that for later use. In the procedure, @TranCounter = 0 means there was no active transaction and the procedure started one. @TranCounter > 0 means an active transaction was started before the procedure was called.
2. " IF @TranCounter > 0......SAVE TRANSACTION ProcedureSave; ": Procedure called when there is an active transaction. Create a savepoint to be able to roll back only the work done in the procedure if there is an error.
3. ELSE -- Procedure must start its own transaction -- BEGIN TRANSACTION;
4. BEGIN TRANSACTION -- Modify database. -- BEGIN TRY
5. WHERE JobCandidateID = @InputCandidateID; -- Get here if no errors; must commit any transaction started in the procedure, but not commit a transaction started before the transaction was called. -- IF @TranCounter = 0
6. IF @TranCounter = 0 -- @TranCounter = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started. -- COMMIT TRANSACTION;
7. BEGIN CATCH -- An error occurred; must determine which type of rollback will roll back only the work done in the procedure. -- IF @TranCounter = 0
8. IF @TranCounter = 0 -- Transaction started in procedure. Roll back complete transaction. -- ROLLBACK TRANSACTION;
9. ELSE -- Transaction started before procedure called, do not roll back modifications made before the procedure was called. -- IF XACT_STATE() <> -1
10. IF XACT_STATE() <> -1 -- If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure. -- ROLLBACK TRANSACTION ProcedureSave;
11. ROLLBACK TRANSACTION ProcedureSave; -- If the transaction is uncommitable, a rollback to the savepoint is not allowed because the savepoint rollback writes to the log. Just return to the caller, which should roll back the outer transaction. After the appropriate rollback, echo error information to the caller. -- DECLARE @ErrorMessage NVARCHAR(4000);
USE AdventureWorks2012; GO IF EXISTS (SELECT name FROM sys.objects WHERE name = N'SaveTranExample') DROP PROCEDURE SaveTranExample; GO CREATE PROCEDURE SaveTranExample @InputCandidateID INT AS -- Detect whether the procedure was called -- from an active transaction and save -- that for later use. -- In the procedure, @TranCounter = 0 -- means there was no active transaction -- and the procedure started one. -- @TranCounter > 0 means an active -- transaction was started before the -- procedure was called. DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 -- Procedure called when there is -- an active transaction. -- Create a savepoint to be able -- to roll back only the work done -- in the procedure if there is an -- error. SAVE TRANSACTION ProcedureSave; ELSE -- Procedure must start its own -- transaction. BEGIN TRANSACTION; -- Modify database. BEGIN TRY DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF @TranCounter = 0 -- @TranCounter = 0 means no transaction was -- started before the procedure was called. -- The procedure must commit the transaction -- it started. COMMIT TRANSACTION; END TRY BEGIN CATCH -- An error occurred; must determine -- which type of rollback will roll -- back only the work done in the -- procedure. IF @TranCounter = 0 -- Transaction started in procedure. -- Roll back complete transaction. ROLLBACK TRANSACTION; ELSE -- Transaction started before procedure -- called, do not roll back modifications -- made before the procedure was called. IF XACT_STATE() <> -1 -- If the transaction is still valid, just -- roll back to the savepoint set at the -- start of the stored procedure. ROLLBACK TRANSACTION ProcedureSave; -- If the transaction is uncommitable, a -- rollback to the savepoint is not allowed -- because the savepoint rollback writes to -- the log. Just return to the caller, which -- should roll back the outer transaction. -- After the appropriate rollback, echo error -- information to the caller. DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH GO
Inputs from experts
John,
there are two transaction counters: 1. the system variable "@@TRANCOUNT", which the DBMS uses to keep track of the number of active transactions (or: the number of BEGIN TRANSACTION's minus the number of COMMIT's) 2. your own local variable "@TranCount", which is initialised with the value of "@@TRANCOUNT" Every time the DBMS encounters a BEGIN TRANSACTION or COMMIT (or ROLLBACK) it increases or decreases the system variable "@@TRANCOUNT", but it does not change your own local variable "@TranCount"! And as long as you don't change it, it keeps the value of "@@TRANCOUNT" at the beginning of the procedure. So, the "BEGIN TRANSACTION" just before the “DELETE HumanResources.JobCandidate” doesn't change the value "@TranCount" (if it was 0, it remains 0). The value of system variable "@@TRANCOUNT" however will be increased by 1. Cheers, Gerard
John, as I can see, this is a stored procedure. A transaction coub be started outside, before the SP call, like BEGIN TRAN EXEC sp1 EXEC sp2 ... Also, a transaction could be initiated by the application that calls the procedure. It is always a good practice to check whether you are inside transaction before doing any ROLLBACK, because if you are not, you can cause an error, and if you are, you are rolling back everything that was done before the procedure call as well. Hope that this is clear enough. Best regards, Dejan
So basically, the transaction can be called either outside or inside of the procedure. If it is called outside, then @TranCounter would not be 0, so the delete transaction would not be committed. If it is called inside, then @TranCounter would be 0, so the delete transaction would be committed given there is no error (if an error exists, the transaction would be totally rolled back).