Nested Transaction

14 Nov 2005 3:34 PM

UncleJoe

Hi all,

I would appreciate some pointers on how to implement nested
transactions in ADO.NET. Take the following code as an example:

SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlTransaction tran =
conn.BeginTransaction(IsolationLevel.ReadUncommitted , "OuterTran");
process1();
process2();
process3();
tran.Rollback("OuterTran");

Process1, Process2 and Process3 are originally three separate processes
not meant to work together so they each open its own connection and
transaction. As you can see I am trying to wrap them together in a
nested transaction so that the outer shell can have a final say. Is
that do-able? My testing result indicates that the outer transaction
was ignored completely. Any insight will be greatly appreciated.

14 Nov 2005 7:05 PM

W.G. Ryan - MVP

If you're using the 1.x framework (or even the 2.0) then you're going to
need to use a Distributed transaction if I understand you correctly. The
2.0 makes it a lot easier with the TransactionScope object though. This
should help
http://msdn.microsoft.com/msdnmag/issues/05/02/DataPoints/default.aspx

Show quote

"UncleJoe" <unclejoenob***@yahoo.ca> wrote in message
news:1131982493.241762.255200@o13g2000cwo.googlegroups.com...
> Hi all,
>
> I would appreciate some pointers on how to implement nested
> transactions in ADO.NET. Take the following code as an example:
>
> SqlConnection conn = new SqlConnection(connStr);
> conn.Open();
> SqlTransaction tran =
> conn.BeginTransaction(IsolationLevel.ReadUncommitted , "OuterTran");
> process1();
> process2();
> process3();
> tran.Rollback("OuterTran");
>
> Process1, Process2 and Process3 are originally three separate processes
> not meant to work together so they each open its own connection and
> transaction. As you can see I am trying to wrap them together in a
> nested transaction so that the outer shell can have a final say. Is
> that do-able? My testing result indicates that the outer transaction
> was ignored completely. Any insight will be greatly appreciated.
>