ayilmaz ayilmaz - 2 months ago 11
SQL Question

Can I open 2 transactions with different names

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

WHILE(...)
BEGIN

BEGIN TRANSACTION TRAN2

BEGIN TRY
.....
.....
.....
COMMIT TRANSACTION TRAN2
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION TRAN2
END CATCH
END

COMMIT TRANSACTION TRAN1

Answer

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..

From MSDN..

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

References :
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/