Oracle 中的事务范围因 BeginTransaction 失败:连接已是本地或分布式事务的一部分
将 OracleConnection 与 TransactionScope 一起使用时出现这种奇怪的行为。 如果我尝试在事务范围中使用connection.BeginTransaction(),我会得到简单优雅的InvalidOperationException:连接已经是本地或分布式事务的一部分。
这是一些代码:
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();
}
如果我不使用 BeginTransaction 一切正常。有什么想法让它发挥作用吗?
PS:我在 Sql Server 上没有遇到这样的问题。
编辑
感谢您的回答,我想我应该添加一些编辑以使我的问题清楚。
首先,我上面提供的代码是问题的演示。假设我有两个 dll 的 MyProject.Oracle.dll 和 MyProject2.MsSql.dll,我想使用这些 dll 中的方法,并且它们使用 db.BeginTransaction()。如果这些 dll 使用了 TransactionScope,我的外部事务就不会成为问题。分布式交易将毫无问题地处理。但我无法更改 dll 内的代码。
为什么 db.BeginTransaction() 适用于 SqlServer 而不适用于 Oracle?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
我在使用 NHibernate 时遇到了同样的问题。
其他答案表明不要混合 TransactionScope 和 BeginTransaction。不幸的是,没有任何来源来支持这一说法。这是我的研究:
如MSDN(搜索“mix”)和在此讨论中,不应将这两个概念混为一谈,即使对于 SQL-Server 也不应如此。我仍然不清楚为什么它似乎适用于 SQL-Server、本地事务和分布式事务。
有些人似乎认为这是一个愚蠢的问题,但在 NHibernate 的上下文中看到它是有意义的(参见 这里,这里和此处)。
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).
TransactionScope 和 DbConnection.BeginTransaction 是事务管理的两种专有方式。您使用其中之一。
当您调用 OracleConnection.Open 时,Oracle 连接就会被登记在环境系统事务中。如果您想提交事务或不调用它,您需要做的就是调用 TransactionScope.Complete(),在这种情况下系统事务将回滚。
如果您不想立即在“Open”上登记,可以将“enlist”连接字符串属性设置为“dynamic”,然后通过调用“OracleConnection.EnlistTransaction'
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'
它适用于 SQL/Server 而不适用于 Oracle 的原因是 SQL/Server 支持嵌套事务,而 Oracle 不支持。
The reason it works for SQL/Server and not Oracle is that SQL/Server supports nested transactions and Oracle does not.
您应该进一步阅读
TransactionScope
首先枚举
TransactionScopeOption
必需:
因此,如果交易不可用,则会创建交易并自动关联。
环境事务是您的代码在其中执行的事务。您可以通过调用 Transaction 类的静态 Current 属性来获取对环境事务的引用。
You should do some more reading on
TransactionScope
First of all the enumeration of
TransactionScopeOption
Required:
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 对象,
TransactionScope
创建已经执行此操作,并且Complete
方法执行提交,内部BeginTransaction
和不需要调用 Commit
方法。如果按照这种方式进行的话效果如何?
You should not use the inner Transaction object,
TransactionScope
creation already does it and theComplete
method does the commit, the innerBeginTransaction
andCommit
method calls are not needed.How does it work if you follow this way?