Question about nested transaction in ADO.NET

I am using SQL Server 2000 as data storage
I have a transaction which has the following structure

connection.begintran
Process A
Process B
Process C
connection.commit
rollback when failed

In each process(A, B, C), there will also be BeginTran, Commit & Rollback
inside it.
I have such structure because process A, B, C are methods provided by
different object.

I have found that when I failed in process B, A will not be rollback...

So how can I implement a nested transaction?

Thanks

David
Thu Dec 04 15:09:27 CST 2003

"tc" <ywchan@hkem.com> wrote in message
news:uBzI5hquDHA.2368@TK2MSFTNGP09.phx.gbl...
> I am using SQL Server 2000 as data storage
> I have a transaction which has the following structure
>
> connection.begintran
> Process A
> Process B
> Process C
> connection.commit
> rollback when failed
>
> In each process(A, B, C), there will also be BeginTran, Commit & Rollback
> inside it.
> I have such structure because process A, B, C are methods provided by
> different object.
>
> I have found that when I failed in process B, A will not be rollback...
>
> So how can I implement a nested transaction?

SqlServer 2000 does not support nested transactions. The TSQL keywords
"begin transaction" and "commit transaction" have nested semantics, but
"rollback transaction" does not. This means that the following sequence
will generate an error:

BEGIN TRANSACTION
BEGIN TRANSACTION
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION <-- error the transaction has already been rolled back.

So to do what you want, you need to use savepoints instead of nested
transactions.


The basic structure of a "nested" transaction using savepoints is, in TSQL:

BEGIN TRANSACTION
SAVE TRANSACTION 'MY_TRAN'
[SOME DML]
IF @@ERROR <> 0 GOTO EH

[SOME DML]
IF @@ERROR <> 0 GOTO EH

[SOME DML]
IF @@ERROR <> 0 GOTO EH

COMMIT TRANSACTION
EH:
BEGIN
ROLLBACK TRANSACTION 'MY_TRAN' -- just rollback to the savepoint
COMMIT TRANSACTION
RETURN
END


Or in VB.NET

Sub DoSomething(ByVal c As SqlConnection, ByVal t As SqlTransaction)
t.Save("DoSomething")
Try
Dim cmd As New SqlCommand
cmd.Transaction = t
...
cmd.ExecuteNonQuery()
...
cmd.ExecuteNonQuery()
...
cmd.ExecuteNonQuery()
Catch ex As Exception
t.Rollback("DoSomething")
Throw
End Try
End Sub


In either case, the only the top level code ever issues a ROLLBACK
TRANSACTION without a savepoint specified. And remember that a transaction
name is not the same as a savepoint. Transaction names are meaningless,
savepoints actually do something.

David
David

Chris
Thu Dec 04 15:21:15 CST 2003


"tc" <ywchan@hkem.com> wrote in message
news:uBzI5hquDHA.2368@TK2MSFTNGP09.phx.gbl...
> I am using SQL Server 2000 as data storage
> I have a transaction which has the following structure
>
> connection.begintran
> Process A
> Process B
> Process C
> connection.commit
> rollback when failed
>
> In each process(A, B, C), there will also be BeginTran, Commit & Rollback
> inside it.
> I have such structure because process A, B, C are methods provided by
> different object.
>
> I have found that when I failed in process B, A will not be rollback...
>
> So how can I implement a nested transaction?
>
> Thanks
>
>

In my limited knowledge you can not nest transactions in ADO.Net

However I can find nothing to back this up except below.

http://support.microsoft.com/default.aspx?scid=kb;en-us;316872
-- "By design, OLE DB Provider for SQL Server does not allow nested
transactions. "


Chris