I've build a class to synchronize data between two different datasources. This synchronization is divided into multiple parts (and methods). Every method has his own TransactionScope and the methods are run sequentially.
Everytime I Run this code I get the following errormessage:
"The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements."
The following code is an example of such a method with a TransactionScope:
private void SomeMethod()
{
try
{
using (var _transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
_connection.Open();
DoSomething()...
}
_transactionScope.Complete();
}
}
catch (TransactionAbortedException e)
{
nlog.Error(string.Format("The transaction has been aborted: {0}", e.Message));
throw e;
}
catch (Exception e)
{
throw e;
}
}
It seems that the call "_transactionScope.Complete()" isn't enough to kill the transactionscope.. Does anyone have a clue what i'm doing wrong?
Thanks in advance!
UPDATE Thanks for your replies. After a few tests I discovered that this problem only exists when there are multiple queries in one method. for example:
try
{
using (TransactionScope _transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
_connection.Open();
//new method:
using (TransactionScope _transactionScope = new TransactionScope(TransactionScopeOption.Suppress))
{
//a selectquery
}
//an update or insert query
_transactionScope.Complete();
}
}
Try changing the constructor.
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable,
Timeout = TimeSpan.FromSeconds(120)
}))
I did a method for creating a Max Timeout value on a transaction scope
public static TransactionScope CreateDefaultTransactionScope(TransactionScopeOption option = TransactionScopeOption.Required)
{
var transactionOptions = new TransactionOptions();
transactionOptions.Timeout = TimeSpan.MaxValue;
transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
return new TransactionScope(option, transactionOptions);
}
and then you would use it:
using (TransactionScope transaction = TransactionHelper.CreateDefaultTransactionScope())
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With