C# TransactionScope 不回滚事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/339269/
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
TransactionScope not rolling back transaction
提问by Michael Kniskern
Here is the current architecture of my transaction scope source code. The third insert throws an .NET exception (Not a SQL Exception) and it is not rolling back the two previous insert statements. What I am doing wrong?
这是我的事务范围源代码的当前架构。第三个插入抛出一个 .NET 异常（不是 SQL 异常）并且它不会回滚前两个插入语句。我做错了什么？
EDIT:I removed the try/catch from insert2 and insert3. I also removed the exception handling utility from the insert1 try/catch and put "throw ex". It still does not rollback the transaction.
编辑：我从 insert2 和 insert3 中删除了 try/catch。我还从 insert1 try/catch 中删除了异常处理实用程序并放置了“throw ex”。它仍然不回滚事务。
EDIT 2:I added the try/catch back on the Insert3 method and just put a "throw" in the catch statement. It still does not rollback the transaction.
编辑 2：我在 Insert3 方法上添加了 try/catch，并在 catch 语句中放了一个“throw”。它仍然不回滚事务。
UPDATE:Based on the feedback I received, the "SqlHelper" class is using the SqlConnection object to establish a connection to the database, then creates a SqlCommand object, set the CommandType property to "StoredProcedure" and calls the ExecuteNonQuery method of the SqlCommand.
更新：根据我收到的反馈，“SqlHelper”类正在使用SqlConnection对象建立与数据库的连接，然后创建一个SqlCommand对象，将CommandType属性设置为“StoredProcedure”并调用SqlCommand的ExecuteNonQuery方法。
I also did not add Transaction Binding=Explicit Unbind to the current connection string. I will add that during my next test.
我也没有将 Transaction Binding=Explicit Unbind 添加到当前连接字符串中。我会在下一次测试中添加这一点。
public void InsertStuff()
{
try
{
using(TransactionScope ts = new TransactionScope())
{
//perform insert 1
using(SqlHelper sh = new SqlHelper())
{
SqlParameter[] sp = { /* create parameters for first insert */ };
sh.Insert("MyInsert1", sp);
}
//perform insert 2
this.Insert2();
//perform insert 3 - breaks here!!!!!
this.Insert3();
ts.Complete();
}
}
catch(Exception ex)
{
throw ex;
}
}
public void Insert2()
{
//perform insert 2
using(SqlHelper sh = new SqlHelper())
{
SqlParameter[] sp = { /* create parameters for second insert */ };
sh.Insert("MyInsert2", sp);
}
}
public void Insert3()
{
//perform insert 3
using(SqlHelper sh = new SqlHelper())
{
SqlParameter[] sp = { /*create parameters for third insert */ };
sh.Insert("MyInsert3", sp);
}
}
回答by tvanfosson
It looks like you are catching the exception in Insert3() so your code continues after the call. If you want it to rollback you'll need to let the exception bubble up to the try/catch block in the main routine so that the ts.Complete() statement never gets called.
看起来您在 Insert3() 中捕获异常，因此您的代码在调用后继续。如果您希望它回滚，您需要让异常冒泡到主例程中的 try/catch 块，以便 ts.Complete() 语句永远不会被调用。
回答by Frustrating Developments
An implicit rollback will only occur if the using is exited without calling ts.complete. Because you are handling the exception in Insert3() the exception never causes an the using statement to exit.
只有在没有调用 ts.complete 的情况下退出 using 才会发生隐式回滚。因为您在 Insert3() 中处理异常，所以异常永远不会导致 using 语句退出。
Either rethrow the exception or notify the caller that a rollback is needed (make change the signature of Insert3() to bool Insert3()?)
要么重新抛出异常，要么通知调用者需要回滚（将 Insert3() 的签名更改为 bool Insert3()？）
回答by Marc Gravell
(based on the edited version that doesn't swallow exceptions)
（基于不包含异常的编辑版本）
How long do the operations take? If any of them are very long running, it is possible that the Transaction Bindingbugfeature has bitten you - i.e. the connection has become detached. Try adding Transaction Binding=Explicit Unbindto the connection string.
手术需要多长时间？如果它们中的任何一个运行时间很长，则事务绑定错误功能可能会困扰您 - 即连接已分离。尝试添加Transaction Binding=Explicit Unbind到连接字符串。
回答by John Allers
I have also run into a similar issue. My problem occurred because the SqlConnection I used in my SqlCommands was already open before the TransactionScope was created, so it never got enlisted in the TransactionScope as a transaction.
我也遇到了类似的问题。出现我的问题是因为我在 SqlCommands 中使用的 SqlConnection 在创建 TransactionScope 之前已经打开，因此它从未作为事务登记在 TransactionScope 中。
Is it possible that the SqlHelper class is reusing an instance of SqlConnection that is open before you enter your TransactionScope block?
SqlHelper 类是否有可能重用在您进入 TransactionScope 块之前打开的 SqlConnection 实例？
回答by Omer Cansizoglu
I dont see your helper class, but transaction scope rollsback if you don't call complete statement even if you get error from .NET code. I copied one example for you. You may be doing something wrong in debugging. This example has error in .net code and similar catch block as yours.
我没有看到你的助手类，但是如果你不调用完整的语句，即使你从 .NET 代码中得到错误，事务范围也会回滚。我复制了一个例子给你。您可能在调试中做错了什么。此示例在 .net 代码和与您的类似的 catch 块中存在错误。
private static readonly string _connectionString = ConnectionString.GetDbConnection();
private const string inserttStr = @"INSERT INTO dbo.testTable (col1) VALUES(@test);";
/// <summary>
/// Execute command on DBMS.
/// </summary>
/// <param name="command">Command to execute.</param>
private void ExecuteNonQuery(IDbCommand command)
{
if (command == null)
throw new ArgumentNullException("Parameter 'command' can't be null!");
using (IDbConnection connection = new SqlConnection(_connectionString))
{
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
}
public void FirstMethod()
{
IDbCommand command = new SqlCommand(inserttStr);
command.Parameters.Add(new SqlParameter("@test", "Hello1"));
ExecuteNonQuery(command);
}
public void SecondMethod()
{
IDbCommand command = new SqlCommand(inserttStr);
command.Parameters.Add(new SqlParameter("@test", "Hello2"));
ExecuteNonQuery(command);
}
public void ThirdMethodCauseNetException()
{
IDbCommand command = new SqlCommand(inserttStr);
command.Parameters.Add(new SqlParameter("@test", "Hello3"));
ExecuteNonQuery(command);
int a = 0;
int b = 1/a;
}
public void MainWrap()
{
TransactionOptions tso = new TransactionOptions();
tso.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
//TransactionScopeOption.Required, tso
try
{
using (TransactionScope sc = new TransactionScope())
{
FirstMethod();
SecondMethod();
ThirdMethodCauseNetException();
sc.Complete();
}
}
catch (Exception ex)
{
logger.ErrorException("eee ",ex);
}
}
If you want to debug your transactions, you can use this script to see locks and waiting status etc.
如果你想调试你的交易，你可以使用这个脚本来查看锁和等待状态等。
SELECT
request_session_id AS spid,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL ,
resource_type AS restype,
resource_database_id AS dbid,
DB_NAME(resource_database_id) as DBNAME,
resource_description AS res,
resource_associated_entity_id AS resid,
CASE
when resource_type = 'OBJECT' then OBJECT_NAME( resource_associated_entity_id)
ELSE 'N/A'
END as ObjectName,
request_mode AS mode,
request_status AS status
FROM sys.dm_tran_locks l
left join sys.dm_exec_sessions s on l.request_session_id = s.session_id
where resource_database_id = 24
order by spid, restype, dbname;
You will see one SPID for two method calls before calling exception method.
在调用异常方法之前，您将看到两个方法调用的一个 SPID。
Default isolation level is serializable.You can read more about locks and transactions here
默认隔离级别是可序列化的。您可以在此处阅读有关锁和事务的更多信息
回答by Hamid Heydarian
I ran into a similar issue when I had a call to a WCF service operation in TransactionScope.
I noticed transaction flow was not allowed due to the 'TransactionFlow' attribute in the service interface. Therefore, the WCF service operation was not using the transaction used by the outer transaction scope. Changing it to allow transaction flow as shown below fixed my problem.
当我在TransactionScope. 我注意到由于服务接口中的“TransactionFlow”属性而不允许事务流。因此，WCF 服务操作没有使用外部事务范围使用的事务。更改它以允许如下所示的交易流程解决了我的问题。
[TransactionFlow(TransactionFlowOption.NotAllowed)]
[TransactionFlow(TransactionFlowOption.NotAllowed)]
to
到
[TransactionFlow(TransactionFlowOption.Allowed)]
[TransactionFlow(TransactionFlowOption.Allowed)]
