Nested Transaction

Question

Post reply

Nested Transaction

go.kulabharathirgb

SSC Enthusiast

Points: 122
More actions
August 26, 2009 at 6:47 am

#385250

Hai!
How can i achive the nested transaction. The following code give error, whats the error in that code. Please help me.
Please give me the example.
Sample code:
begin transaction t1;
insert into tst_table1(fld1) values('myname1');
insert into tst_table1(fld1) values('myname2');
select * from tst_table1;
commit transaction t1;
begin transaction t2;
insert into tst_table1(fld1) values('myname3');
insert into tst_table1(fld1) values('myname4');
select * from tst_table1;
commit transaction t2;
rollback transaction t2;
select * from tst_table1;
rollback transaction t1;
select * from tst_table1;

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Its traditional to post a question 😀

Clear Sky SQL
My Blog[/url]

matt6288 SSCertifiable Points: 5058 More actions · Answer 2

You are attempting to rollback a committed transaction but without a question in your post it makes it tough. There are some very good people on this forum but I don't know if they can answer a question without it being asked. 🙂

go.kulabharathirgb SSC Enthusiast Points: 122 More actions · Answer 3

Hi.

Sorry for the inconvenience.

My question is, how can i achive the nested transaction?. please give me a example..

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

You simply cant. Rollback will rollback all currently open transactions on that connection.

Data written to @ table do survive the rollback , so maybe you can use that ?

Clear Sky SQL
My Blog[/url]

SQLZ SSChampion Points: 12872 More actions · Answer 5

What exactly are you trying to achieve here?

You say you're trying to achieve a nested transaction but that doesn't make much sense. So why don't you explain a bit about what it is you want to do that makes you think you need a nested transaction?

Unless of course, all you want is an example of a nested transaction, which sounds a lot like a schoolwork question - sorry, I can't help being cynical ;-).

Karl
source control for SQL Server

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

What error are you getting?

BTW, a nested transaction is not one transaction after another. It's one transaction inside another.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning