Transaction Scope fails with BeginTransaction in Oracle : Connection is already part of a local or a distributed transaction

Question

Having this strange behavior while using OracleConnection with TransactionScope. If i try to use connection.BeginTransaction() in a transaction scope i get simple elegant InvalidOperationException : Connection is already part of a local or a distributed transaction.

here is some code:

var trxOptions = new TransactionOptions();
 trxOptions.IsolationLevel = IsolationLevel.ReadCommitted;
 using (var transaction = new TransactionScope(TransactionScopeOption.Required,trxOptions))
            {

                var c = ConfigurationManager.ConnectionStrings["oracle_test"].ConnectionString;
                using (var oracle = new OracleConnection(c))
                {
                    oracle.Open();
                    using (var tr = oracle.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
                    {
                        var cmd = oracle.CreateCommand();
                        cmd.CommandText = "INSERT INTO simple_user VALUES('a')";

                        cmd.ExecuteNonQuery();
                        tr.Commit();
                    }
                }


        // now go to sql server and insert data
       transaction.Complete();

}

If I dont use BeginTransaction everything works. Any ideas to make it work?

PS: I am not having such an issue on Sql Server.

Edit

Thanks for answers i suppose i should add some edit to make my question clear.

First off all, the code i provided above is demonstration of problem. Lets say i have two dll's MyProject.Oracle.dll and MyProject2.MsSql.dll and i want to use methods inside these dll's and they use db.BeginTransaction(). If these dlls had used TransactionScope my outer transaction wouldnt be a problem. Distrubuted transaction would be handled without any issues. But i cannot change code inside dlls.

And why db.BeginTransaction() works for SqlServer but not for Oracle?

Solution 1

I hit the same question in conjunction with NHibernate. Other answers indicate not to mix TransactionScope and BeginTransaction. Unfortunately no sources to support that claim where added. Here my research: As stated on MSDN (search for "mix") and in this discussion, one should not mix both concepts, not even for SQL-Server. Why it seems to work for SQL-Server, for both local and distributed transactions, is still not clear to me.

Some seem to think this is a stupid question but it makes sense when seen in context of NHibernate (see here, here and here).

Solution 2

The TransactionScope and DbConnection.BeginTransaction are 2 exclusive ways of transaction management. You use either 1 of them.

The moment you call OracleConnection.Open, the oracle connection is enlisted in the ambient system transaction. All you then need to do is call TransactionScope.Complete(),if you want to commit the transaction or do not call it, in which case the system transaction is rolled back. In case you do not want to enlist immediately on 'Open', you can set the 'enlist' connection string attribute to 'dynamic' and then enlist explicitly via a call to 'OracleConnection.EnlistTransaction'

Comments

[adt]

I know they are different, I actually edited a little bit but my question still persists. Why this works for Sql Server but not for Oracle?

[adt]

this works fine ( I've edited question a little bit). But why using BeginTransaction and Commit works for Sqlserver not for Oracle.

[Jaqen H'ghar]

Thanks Piper.. I too was facing similar problem. Links you shared are really helpful.