Question about nested transaction in ADO.NET

  • Thread starter Thread starter tc
  • Start date Start date
T

tc

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
 
tc said:
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?
Click to expand...

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
 
tc said:
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
Click to expand...

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
 
Back
Top