Transactions in SP
-
I am debugging a SP with the T-SQL debugger. I have a Stored Procedure A which calls SP B which calls SP C.
Both SP A and SP B have Begin Trans and Commit/Rollback. SP C does not have any transactions. SP C exits with RETURN value of 1 when an error occurs.
Before the first Rollback statement, the @@Transcount value is 3. After the first Rollback, @@Transcount value is 0. Shouldn't each Rollback reduce the @@Transcount by 1 ?
-
It sounds like a scope issue. I am not sure how this would work, but:
BEGIN TRANSACTION A
BEGIN TRANSACTION B
DO SP C... (three iterations and then an error : RETURN 1)
ROLLBACK/COMMIT TRANSACTION B
ROLLBACK/COMMIT TRANSACTION A
If the TRANSACTIONs in B are wrapped within A, when a ROLLBACK for TRANSACTION A is called, would that not ROLLBACK those TRANSACTIONs that took place in B? In essence, are those TRANSACTIONs in B not within the scope of TRANSACTION A and hence dependent upon TRANSACTION A being COMMIT'ed?
I wasn't born stupid - I had to study.
-
In a nested transaction, the rollback command in any nested SP will rollback the whole transaction and the @@TRANCOUNT will jump to 0. This is the expected behaviour of SQL server. If the rollback command rolls back to a saved point, the @@TRANCOUNT is not changed.
It's better always to check @@TRANCOUNT before commit or rollbacka tran.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply