C# “操作对交易状态无效”错误和交易范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/193154/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"The operation is not valid for the state of the transaction" error and transaction scope
提问by Michael Kniskern
I am getting the following error when I try to call a stored procedure that contains a SELECT Statement:
当我尝试调用包含 SELECT 语句的存储过程时出现以下错误：
The operation is not valid for the state of the transaction
该操作对交易状态无效
Here is the structure of my calls:
这是我的电话结构：
public void MyAddUpdateMethod()
{
using (TransactionScope Scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
using(SQLServer Sql = new SQLServer(this.m_connstring))
{
//do my first add update statement
//do my call to the select statement sp
bool DoesRecordExist = this.SelectStatementCall(id)
}
}
}
public bool SelectStatementCall(System.Guid id)
{
using(SQLServer Sql = new SQLServer(this.m_connstring)) //breaks on this line
{
//create parameters
//
}
}
Is the problem with me creating another connection to the same database within the transaction?
我在事务中创建到同一个数据库的另一个连接有问题吗？
采纳答案by Michael Kniskern
After doing some research, it seems I cannot have two connections opened to the same database with the TransactionScope block. I needed to modify my code to look like this:
在做了一些研究之后，似乎我无法使用 TransactionScope 块打开到同一个数据库的两个连接。我需要修改我的代码看起来像这样：
public void MyAddUpdateMethod()
{
using (TransactionScope Scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
using(SQLServer Sql = new SQLServer(this.m_connstring))
{
//do my first add update statement
}
//removed the method call from the first sql server using statement
bool DoesRecordExist = this.SelectStatementCall(id)
}
}
public bool SelectStatementCall(System.Guid id)
{
using(SQLServer Sql = new SQLServer(this.m_connstring))
{
//create parameters
}
}
回答by Wyatt
I've encountered this error when my Transaction is nested within another. Is it possible that the stored procedure declares its own transaction or that the calling function declares one?
当我的事务嵌套在另一个事务中时，我遇到了这个错误。是否有可能存储过程声明自己的事务或调用函数声明一个事务？
回答by Sharique
I also come across same problem, I changed transaction timeout to 15 minutes and it works. I hope this helps.
我也遇到了同样的问题，我将事务超时更改为 15 分钟并且它有效。我希望这有帮助。
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = new TimeSpan(0, 15, 0);
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required,options))
{
sp1();
sp2();
...
}
回答by R. Schreurs
When I encountered this exception, there was an InnerException "Transaction Timeout". Since this was during a debug session, when I halted my code for some time inside the TransactionScope, I chose to ignore this issue.
当我遇到这个异常时，有一个 InnerException "Transaction Timeout"。由于这是在调试会话期间，当我在 TransactionScope 中暂停代码一段时间时，我选择忽略此问题。
When this specific exception with a timeout appears in deployed code, I think that the following section in you .config file will help you out:
当部署的代码中出现这个带有超时的特定异常时，我认为 .config 文件中的以下部分将帮助您：
<system.transactions>
<machineSettings maxTimeout="00:05:00" />
</system.transactions>
回答by Vishav Premlall
For me, this error came up when I was trying to rollback a transaction block after encountering an exception, inside another transaction block.
对我来说，当我在另一个事务块中遇到异常后尝试回滚事务块时，出现了这个错误。
All I had to do to fix it was to remove my inner transaction block.
我所要做的就是删除我的内部事务块。
Things can get quite messy when using nested transactions, best to avoid this and just restructure your code.
使用嵌套事务时，事情会变得非常混乱，最好避免这种情况，只需重新构建代码即可。
