Can I have 2 transactions in a stored procedure like below? If I rollback TRAN1, does it rollback all TRAN2? I am lost here, any feedback will help. Thanks in advance
BEGIN TRANSACTION TRAN1
BEGIN TRANSACTION TRAN2
COMMIT TRANSACTION TRAN2
ROLLBACK TRANSACTION TRAN2
COMMIT TRANSACTION TRAN1
No matter ,how many transactions you have ,Outer rollback rollbacks all the transactions..
When you nest transactions like in your case,each committ/rollback increases or decreases @@trancount..
Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one.
ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all of the nested transactions and decrements @@TRANCOUNT to 0
See this as well :A SQL Server DBA myth a day: (26/30) nested transactions are real
Below is demo to test this behaviour..
if object_id('t1','u') is not null drop table t1 create table t1 ( id int ) go begin tran outertran select @@trancount--1 insert into t1 select 1 begin tran innertran select @@trancount--2 insert into t1 select 2 /**below throws error,since you can reference only outer transaction -referrring inner tran is not legal ***/ --rollback tran innertran /*** error you get by uncommneting above Msg 6401, Level 16, State 1, Line 20 Cannot roll back innertran. No transaction or savepoint of that name was found. **/ commit tran innertran select @@trancount--1 rollback --rollbacks all select * from t1