Get exception when calling TransactionScope.Complete() method.  
Author Message
Xiao Feng





PostPosted: .NET Framework Data Access and Storage, Get exception when calling TransactionScope.Complete() method. Top

Hi,
Following is my code to use system.transaction:
ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings["Sample"];
DbProviderFactory factory = DbProviderFactories.GetFactory(setting.ProviderName);

using (TransactionScope scope = new TransactionScope())
{

DbConnection conn = factory.CreateConnection();
conn.ConnectionString = setting.ConnectionString;
conn.Open();

DbCommand cmdUpdate = conn.CreateCommand();
cmdUpdate.CommandType = CommandType.Text;
cmdUpdate.UpdatedRowSource = UpdateRowSource.None;

using (DbTransaction tran1 = conn.BeginTransaction())
{
cmdUpdate.CommandText = "UPDATE Table1 SET Name='Modified' Where ID=500";
cmdUpdate.Transaction = tran1;
cmdUpdate.ExecuteNonQuery();
tran1.Commit();
}

using (DbTransaction tran2 = conn.BeginTransaction())
{
cmdUpdate.CommandText = "UPDATE Table1 SET Name='Modified' Where ID=501";
cmdUpdate.Transaction = tran2;
cmdUpdate.ExecuteNonQuery();
tran2.Rollback();
}
scope.Complete();
}

Because the tran2 call the Rollback, when calling scope.Complete, I'll get a TransactionAbortedException. I think this scenario (rollback an ADO.NET transaction in existing system) is quite common. How to handle this Am I in right way



.NET Development9  
 
 
Sarah Parra - MSFT





PostPosted: .NET Framework Data Access and Storage, Get exception when calling TransactionScope.Complete() method. Top

In general I would not recommend mixing DbTransactions with System.Transactions transactions, because you can run into precisely these types of issues where something in the inner transaction affects the overall behavior of the outer transaction. Having said that, I'll try to provide some information on what's going on, and your options.

Which backend are you targetting here My response is based on how SQL Server handles transactions, but depending on what you are using, the behavior may be different.

I'm not sure if you understand why the exception is thrown, but just want to know how to handle it, or if you are questioning why it's happening in the first place. In the given example, with SQL Server, if you do a ROLLBACK in the middle of a nested transaction, it will rollback the entire transaction, which is why the error occurs when the Complete method tries to Commit it.

I realize this is probably a simplified example, but I don't know how common this scenario is as you have described it. Again with SQL Server, if you Commit in the middle of a nested transaction, it doesn't really do much, because the outer transaction is still active. In this case, when you do the rollback, it's rolling back everything, including the changes you make in the first BeginTransaction/Commit block. However, even if you remove the BeginTransaction/Commit block, you will still see the TransactionAbortedException. I'm just mentioning it because I don't fully understand the intent of the scenario, and why you are trying to use nested transactions at all (or, why they are both wrapped in an outer TransactionScope).

If you don't call Complete, you shouldn't get the exception. However, System.Transactions will still try to rollback the transaction. It will either attempt to commit if you use Complete, or attempt to Rollback. You can't really get around it trying one or the other. In this case, SQL Server will still throw an error even if you don't call Complete (you can see it in SQL Profiler), because the transaction is not longer active. It's just that the exception is not propagated to the client code.

If for some reason you can't avoid the Complete call, you will have to handle the TransactionAbortedException.

Thanks,
Sarah

Please Mark as Answer if this answers your question, or Unmark as Answer if it is marked and you feel it is not answered.

More info on Nested Transactions and SQL Server:

Nesting Transactions
http://msdn2.microsoft.com/en-us/library/ms189336.aspx

 

 



 
 
Xiao Feng





PostPosted: .NET Framework Data Access and Storage, Get exception when calling TransactionScope.Complete() method. Top

Hi,

Actually, I want to get some guidelines when using system.transactions.

For example, here are some existing components which are using ADO transaction, when we want to compose these components together, we should ensure them in same transaction. From this requirement, we should use TransactionScope to wrap outside the components call (is it correct ):

using (TransactionScope scope = new TransactionScope())
{
customer.Update();
order.Update();
scope.Complete();
}

How to add exception handling code in this case


 
 
Sarah Parra - MSFT





PostPosted: .NET Framework Data Access and Storage, Get exception when calling TransactionScope.Complete() method. Top

I think you should be able to add a try/catch around the whole using block, and just catch the TransactionAbortedException. You may want to log somewhere that this error has occurred, but if otherwise, if you don't care to do anything different as a result, you can just do nothing. However, keep in mind that you may get other errors using the pattern like you show above. For example, what if customer.Update() does a Rollback In that case, you will probably see a SqlException in order.Update before you even get to the scope.Complete. That's because the outer transaction has already ended.

The bottom line is that if it's possible for the transaction to be aborted inside of the TransactionScope, you need to expect that exception to be thrown.

Thanks,
Sarah