Rollback statement in T-SQL

Question

Rollback statement in T-SQL

VSSGeorge

SSCrazy Eights

Points: 8146
More actions
June 7, 2013 at 10:22 pm

#294922

The functionality of Rollback statement in T-SQL is such a way it rollbacks to the outermost nested transaction. This is true if commit is issued for inner transaction before the rollback happens in outer transaction.
What happens if inner transaction is roll backed and outer transaction has an insert statement and Commit statement? Will the outer transaction also get affected and will the table contain the inserted rows?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no such thing as a nested transaction, it's a syntactical lie. The earlier one realises that, the better.

A rollback will roll back ALL changes made in the current transaction, no matter how many begin transactions have been issued

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OTF SSCertifiable Points: 6054 More actions · Answer 2

As has already been pointed out, nested transactions don't exist in Sql Server.

Infact, all it appears to do is increment or decrement @@TRANCOUNT. I don't believe, it even writes anything to the Transaction Log.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

I will be very brave to disagree with Gail on this one. But...

There are such thing as a "nested transaction" in MS SQL Server. They just are not what most of people would think they are.

Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

Just this and nothing else. Source: http://msdn.microsoft.com/en-us/library/ms189336(v=sql.105).aspx

Whatever reason is (eg. syntactical lie), but microsoft calls them "nested transactions".

And YES, it has nothing to do with posibility of nested rollback/commit.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

This code shows the result of nested transactions:

create table #t ( x int not null primary key clustered )

begin transaction

select [@@Trancount 1] = @@trancount

insert into #t select 1

insert into #t select 2

begin transaction

select [@@Trancount 2] = @@trancount

insert into #t select 3

insert into #t select 4

commit

select [@@Trancount 3] = @@trancount

rollback transaction;

select [@@Trancount 4] = @@trancount

select [#t rows] = count(*) from #t

drop table #t

Results:

@@Trancount 1

-------------

1

(1 row(s) affected)

@@Trancount 2

-------------

2

(1 row(s) affected)

@@Trancount 3

-------------

1

(1 row(s) affected)

@@Trancount 4

-------------

0

(1 row(s) affected)

#t rows

-----------

0

(1 row(s) affected)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Eugene Elutin (6/11/2013)
I will be very brave to disagree with Gail on this one. But...
There are such thing as a "nested transaction" in MS SQL Server.

There are no such things as nested transactions. 🙂

There is a transaction that can be begin, committed and rolled back, and there is an @@nestlevel that gives the impression that there is such a thing as a nested transaction exists.

If you have multiple BEGIN TRANSACTION statements, only the first one starts a transaction. The others simply increment an internal counter and have no other effect, they do not write to the log, they do not start an atomic block of statements, they do not isolate changes from other sessions, they do not indicate the latest time for accessing row versions under snapshot isolation level.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

GilaMonster (6/11/2013)
Eugene Elutin (6/11/2013)
I will be very brave to disagree with Gail on this one. But...
There are such thing as a "nested transaction" in MS SQL Server.
There are no such things as nested transactions. 🙂
...

If we take MSDN as kind of "Old Testament", then you can see that at the end of the second paragraph (from linked I've posted), Microsoft refers to beasts called "nested transactions":

The following example shows the intended use of nested transactions...

It's almost like "time". Some argue its existence too :hehe:

So, now it's a matter of faith. Someone may believe in it, some others believe in something else and of course there are some complete agnostics (or even orthodox atheistic fundamentalists).

😀

Ok, on a serious note.

I do absolutely agree on the way you described the transaction behaviour. Actually, I do even agree that "nested transaction" is a badly chosen term, but even in Oracle they referred by this “badly chosen” name.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]