Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 865
adt Avatar asked Jul 29 '11 09:07

adt


1 Answers

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

like image 164
Piper Avatar answered Oct 03 '22 10:10

Piper