Transaction timeouts and connection pools issue

In an asp.net 2.0 application I maintain, we were experiencing issues with transaction aborted errors (timeout). Code that fails seems to cause a timeout, then the page logging functionality that uses transactionscope (default constructor) fails (but not always). The timeout is set to 2 minutes.

Some example code that is similar to what we have in our app is below:

Try

      Dim scope As TransactionScope = New TransactionScope(TransactionScopeOption.Required, New TimeSpan(0, 0, CInt(TransactionTimeout)))

    **A method call that fails is here**

     Using scope

**other code is here**


scope.complete

end using


catch

From what I've seen and read, my guess is that since the using block is never reached, the transaction times out. Then the logging code (done with any page request) tries to enlist in the existing transaction, which has timed out and results in a transaction aborted error (as soon as the constructor is called). Is this assumption correct? Why would only some requests fail going forward and not all (assuming they all use transactionscope)?

My big question is how does connection pooling play into this? If user A hits the bad code, would user B then be potentially affected by this? This is the behavior we've seen. If not, what else might cause this? I've been to MSDN, but I can't find anything that really clicks with me as to what is happening and why.

Here is the relevant portion of the connection string:

Enlist=true;Pooling=true;Connection Lifetime=20;Max Pool Size=25;Min Pool Size=5

FYI. Not sure if this is relevant, but the app uses an Oracle 11g database with the EntLib database factory pattern.

Thanks for the help.

Solution

First, if you can, I would change your code to be:

Using scope As New TransactionScope(TransactionScopeOption.Required, New TimeSpan(0, 0, CInt(TransactionTimeout)))

If this is not possible, then you should wrap your code in a try/finally statement and dispose the scope in the finally if it is set.

    Dim scope As TransactionScope
    Try
        scope = New TransactionScope(TransactionScopeOption.Required, New TimeSpan(0, 0, CInt(TransactionTimeout)))

        ' **A method call that fails is here**

        ' **other code is here**
        scope.complete()
    Finally
        If scope IsNot Nothing Then
            Try
                scope.Dispose()
            Catch
            End Try
        End If
    End Try

However, connection pooling could be playing a part in your timeout issues.

Generally speaking, connections are pooled keyed by the connection string so that a subsequent request for an identical connection string will result in a free connection from the pool with the same connection string (if any) being allocated to the request.

When a connection is returned the pool, it will remain in the pool for the number of seconds specified in your Connection Lifetime parameter and then be freed if it isn't reused.

Based on this, assuming that you have some user-specific information in the connection string or other data that varies from connection to connection, if you have more than 25 users perform an operation in a 20 second time span, you will run out of available connections.

In addition, if your application holds connections open for longer than absolutely necessary or does not explicitly close connections it has opened, then the connections could be held open for much longer than expected. This could be the case when the TransactionScope is not disposed.