事务范围因 Oracle 中的 BeginTransaction 失败：连接已经是本地或分布式事务的一部分

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

提问by adt

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.

将 OracleConnection 与 TransactionScope 一起使用时出现这种奇怪的行为。如果我尝试在事务范围内使用 connection.BeginTransaction()，我会得到简单优雅的 InvalidOperationException ：Connection 已经是本地或分布式事务的一部分。

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?

如果我不使用 BeginTransaction 一切正常。有什么想法可以让它发挥作用吗？

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

PS：我在 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.

首先，我上面提供的代码是问题的演示。假设我有两个 dll 的 MyProject.Oracle.dll 和 MyProject2.MsSql.dll，我想使用这些 dll 中的方法，它们使用 db.BeginTransaction()。如果这些 dll 使用了 TransactionScope，我的外部事务就不会成为问题。分布式事务处理不会有任何问题。但我无法更改 dll 中的代码。

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

为什么 db.BeginTransaction() 适用于 SqlServer 而不适用于 Oracle？

回答by Piper

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.

我在结合 NHibernate 时遇到了同样的问题。其他答案表明不要混合使用 TransactionScope 和 BeginTransaction。不幸的是，没有任何来源支持该声明。这是我的研究：正如MSDN（搜索“mix”）和本讨论中所述，不应混合使用这两个概念，即使对于 SQL-Server 也不应。为什么它似乎适用于 SQL-Server，对于本地和分布式事务，我仍然不清楚。

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

有些人似乎认为这是一个愚蠢的问题，但在 NHibernate 的上下文中看到它是有道理的（请参阅此处、此处和此处）。

回答by alwayslearning

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

TransactionScope 和 DbConnection.BeginTransaction 是两种独有的事务管理方式。您使用其中之一。

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'

在您调用OracleConnection.Open的那一刻，oracle 连接被登记在环境系统事务中。然后你需要做的就是调用 TransactionScope.Complete()，如果你想提交事务或不调用它，在这种情况下系统事务将被回滚。如果您不想在“打开”时立即登记，您可以将“登记”连接字符串属性设置为“动态”，然后通过调用“ OracleConnection.EnlistTransaction”显式登记

回答by Davide Piras

You should not use the inner Transaction object, TransactionScopecreation already does it and the Completemethod does the commit, the inner BeginTransactionand Commitmethod calls are not needed.

您不应该使用内部 Transaction 对象，TransactionScope创建已经这样做了并且Complete方法进行了提交，不需要内部BeginTransaction和Commit方法调用。

How does it work if you follow this way?

如果你按照这种方式，它会如何工作？

回答by V4Vendetta

You should do some more reading on TransactionScope

你应该多读一些 TransactionScope

First of all the enumeration of TransactionScopeOption

首先枚举 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.

环境事务是您的代码在其中执行的事务。您可以通过调用 Transaction 类的静态 Current 属性来获取对环境事务的引用。