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

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?

---

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).

---

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'

---

The reason it works for SQL/Server and not Oracle is that SQL/Server supports nested transactions and Oracle does not.

---

You should do some more reading on TransactionScope

First of all the enumeration of TransactionScopeOption

Required:

A transaction is required by the scope. It uses an ambient transaction if one already exists. Otherwise, it creates a new transaction before entering the scope. This is the default value.

So the transaction if not available is created and automatically associated.

The ambient transaction is the transaction within which your code executes. You can obtain a reference to the ambient transaction by calling the static Current property of the Transaction class.

---

You should not use the inner Transaction object, TransactionScope creation already does it and the Complete method does the commit, the inner BeginTransaction and Commit method calls are not needed.

How does it work if you follow this way?