Nested transactions in Sql Server

Question

Imagine the following scenario:

I am using SQL Server 2005. I have a transaction that is calling, among other SQL statements, a stored procedure that also has a transaction inside. The outer transaction sometimes fails and it is rolled back after the stored procedure is called and committed successfully.

My question is, does the stored procedure's transaction rollback too?

score 56 · Accepted Answer · answered Feb 09 '09 at 12:04

56

With a nested transaction, a commit does not write any changes to disk, except for the top level transaction. A rollback, however works regardless of the level of the transaction, so yes, it will roll the inner transaction back.

answered Feb 09 '09 at 12:04

Matthew Farwell

60,889
18
128
171

20

Rollback will rollback -all- transactions, not just the previous one. EG: http://www.emoreau.com/Entries/Articles/2011/02/EricMoreau1.gif – Pure.Krome Jun 27 '12 at 08:14
10

@Pure.Krome is correct. Nested transactions will *not* do what you want. See the [SQL Server Myth a Day series](http://www.sqlskills.com/blogs/paul/post/A-SQL-Server-DBA-myth-a-day-%282630%29-nested-transactions-are-real.aspx) – Mike Bailey Nov 13 '12 at 13:07
1

I was confused by this and google directed me here. This statement throws and error: begin transaction begin transaction select 1 rollback rollback. But if i do commit rollback, then it works fine. I guess because the after the first rollback I am no longer in a transaction. the first rollback essentially rolls back both begins. – TizzyFoe Sep 23 '19 at 20:42
3

Both articles linked in the comments here appear to be dead. The first one isn't archived but the second is: https://web.archive.org/web/20121223021357/http://www.sqlskills.com/blogs/paul/post/a-sql-server-dba-myth-a-day-(2630)-nested-transactions-are-real.aspx – avandesa Feb 22 '22 at 20:02

score 18 · Answer 2 · answered Feb 09 '09 at 12:56

Absolutely yes, the top level transaction will own all the data changes until it is committed or rolled back.

However I would encourage you to think carefully about the transaction model. The more such scenarios exist in your system the greater your exposure to locking issues. Also the computational expense of the procedure increases.

It's remarkable how often, when rationalising SQL, I find transactions have been implemented where they just aren't required. I encourage you (and anyone working with transactions) to think carefully about why you are using them in each context and what would happen were the transaction not implemented. Just my 2c worth!

John Sansom · Answer 3 · 2009-02-09T12:09:17.560

4

Yes the stored procedure will be rolled back.

Here is the overall flow of your code:

BEGIN TRY

    BEGIN TRANSACTION

    EXEC SotredProcedureName

    --Do some other activity

    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    --IF an error occurs then rollback the current transaction, which includes the stored procedure code.
    ROLLBACK TRANSACTION

END CATCH

Cheers, John

edited Feb 09 '09 at 12:09

answered Feb 09 '09 at 12:04

John Sansom

41,005
9
72
84

score 1 · Answer 4 · edited Oct 09 '11 at 12:42

I've tried with begin tran and commit inside the stored procedure say usp_test.
Exec these sp with some other query as below

update x set name='xxx'
select * from x---contains 'xxx'
begin tran
update x set name='yyy'
select * from x---contains 'yyy'
exec usp_test
select * from x---contains 'zzz' inside the sp
rollback tran

While executing the above query name in x table must be 'xxx' its not 'zzz' since the first begin tran rollbacked even the sp tran commit.
So, first begin tran own the data changes.

Nested transactions in Sql Server

4 Answers4

Linked

Related