Transaction's in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transaction's in SQL

Author

Topic

vishnu.cm
Starting Member

7 Posts

Posted - 2006-08-29 : 07:06:11

Hi All,

please consider the sample below -

begin transaction Test

truncate table TranTbl

begin transaction Test
begin transaction InnerTest
insert into TranTbl values(1)
rollback tran InnerTest

begin transaction InnerTest1
insert into TranTbl values(2)
commit tran InnerTest1
commit transaction Test

select * from TranTbl

if(@@TRANCOUNT>1)
begin
select 'IN'
rollback tran
end

here the outer transaction test if rolled back will roll back the entire transaction even the inner transaction that is a success and commited ????

any guess wat happens if an inner transaction in the above case is rolled back ????

Please throw ur insights.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-29 : 07:12:26

Nothing is committed until the outermost transaction is committed.
A rollback will roll back to the outermost transaction.
There is no point in nesting transactions.
You can use a transaction save point to rollback part of a transaction but that's about all.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vishnu.cm
Starting Member

7 Posts

Posted - 2006-08-29 : 08:19:25

Thanks nr :-)

but the point is that , as u mentioned -> wats the need for nesting transactions .,this is some thing that doesnt feel quiet rite to me :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources