0 Replies Latest reply: Aug 7, 2007 2:33 PM by 591956 RSS

    TransactionScope not rolling back for ODP

    591956
      - Framework: v2.0.50727
      - Database: Oracle 9i
      - Client: 9.2.0
      - Oracle.DataAccess.dll: 9.2.0.700
      - oraMTS.dll: 9.2.0.7

      Hi,

      I recently moved a project from using the .NET Framework's provider to Oracle's ODP. The project uses MTS to wrap a number of inserts and updates into a single transaction. Under the framework's provider this worked fine - transactions rolled back whenever an error occurred. Now using ODP, transactions no longer roll back when the TransactionScope object is disposed despite '"complete()" never being called.

      I simplified the issue down to a simple app and found the problem still exists. The following code always results in the record persisting in the database:
              Try
                  Using scope As TransactionScope = New TransactionScope()
                      Dim conn As OracleConnection = New OracleConnection("Data Source=test.world;User Id=abc;Password=def")
                      Dim cmd As New OracleCommand()
                      cmd.Connection = conn
      
                      cmd.CommandText = "INSERT INTO test (one, two) VALUES (1, 2)"
                      cmd.Parameters.Clear()
                      conn.Open()
                      cmd.ExecuteNonQuery()
                      conn.Close()
      
                      Throw New Exception("test exception")
      
                      scope.Complete()
                  End Using
              Catch ex As Exception
                  MsgBox(ex.ToString)
              End Try
      I have tried both "enlist=true" in the connection string and "TransactionScopeOption.Required" for the transaction object but the problem persists.

      All I have found here are two similar posts with no responses... I'm crossing my fingers that three times is lucky and someone will have some info.

      Thanks,
      Mike