onsdag 4 april 2012

Long running System.Transactions (TransactionScope)

I've came accross a problem when working with long running System.Transactions (TransactionScope). An InvalidOperationException is thrown with the following message: "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."

No transaction is reused, the usage of the connection and transaction is perfectly fine:

var transactionOptions = new TransactionOptions()
{
    IsolationLevel = IsolationLevel.Serializable,
    Timeout = TimeSpan.FromMinutes(20),
};
using (var connection = new SqlConnection(ConnectionString))
using (var transaction = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
    // Some database manipulations that eventually throws the exception

    transaction.Complete();
}
The problem occurs if the transaction runs longer than the maxTimeout property. This property can only be modified in the machine.config and its default value is 10 minutes. It doesnt matter that the Timeout property of the TransactionOptions are set to a higher value, TransactionOptions.Timeout can not exceed maxTimeout property.

Your machine.config is located at: %windir%\Microsoft.NET\Framework\[version]\config\machine.config

On my current machine, they exist at (depending on target platform):

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config

To modify the machine.config add the following (in the configuration tag):

    
        
    

Be aware that the maxTimeout property is sometimes used for deadlock detection, so don't set a too high value.

4 kommentarer:

  1. I tried this and got an error:

    The worker process for application pool 'MyAppPool' encountered an error 'The configuration section 'system.transactions' cannot be read because it is missing a section declaration
    ' trying to read configuration data from file '\\?\C:\Windows\Microsoft.NET\Framework64\v4.0.30319\CONFIG\machine.config', line number '13'. The data field contains the error code.

    Any idea what I'm doing wrong?

    SvaraRadera
  2. Solved: Note that you *must* put this at the *end* of the config section, otherwise you will get an error.

    SvaraRadera
  3. thank you, this article solves my problem.

    SvaraRadera
  4. Why at the *end* of the config section ?

    SvaraRadera