Correcting An ASP.NET & Oracle Connection Issue

Posted by Paul on Nov 17, 2010 in Development | 0 comments

I came across a rather unusual issue at work last week — one involving Oracle database connections. The application I’ve been working on makes us of multiple Oracle connections; some of them simple connections which open, perform a read, and then close; and some of them are persistent connections that are utilized for larger transactions, such as multiple read and/or updates. Even with both of these connection types, through ASP.NET’s connection pooling capability, there should have never been more than two active connections to the database at any given time.

Or so I thought. One day last week I began to notice a significant decrease in the performance of the application, and an increase the amount of timeout errors I was receiving from Oracle. After some investigation I found that the cause of the resource drain was my application making, and maintaining, multiple connections to the database. At one point, my application had created 152 simultaneous connections. And to make matters confusing, when I stepped through the code in Visual Studio’s debugger, I could see my application executing the code that should have terminated the connection when the object was done with it — and yet it still wasn’t killing the connection.

One day and several head scratches later, I found the cause. I had been nesting multiple simple connections inside of a persistent connection. This wasn’t intentional. I was simply closing my persistent connection in the Finally portion of my Try Catch statement, but because I was opening another connection before the persistent connection was closed ASP.NET wasn’t closing any of my connections. With multiple connection per page load, and multiple pages per transactions, this added up rather quickly.

I’m still not 100% sure why my simple connections weren’t being closed even while nested inside of a persistent connection, but closing the persistent before opening any other corrected the issue.

My code behind ended up looking like:

'Persistent Connection
Dim pc As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
pc.Open()
'Oracle Transaction
Dim ot As Oracle.DataAccess.Client.OracleTransaction = pc.BeginTransaction

Try

' --- All Kinds of Logic Here --- |
[...]


If ProcessTransaction Then
ot.Commit

' --- Close The Connection --- |
If Not pc.State = ConnectionState.Closed Then
    pc.Close()
    pc.Dispose()
End If

' --- Logic That Opens Simple Connections --- |
[...]

Else

ot.Rollback()

' --- Close The Connection --- |
If Not pc.State = ConnectionState.Closed Then
    pc.Close()
    pc.Dispose()
End If

End If


' --- Method which Opens Simple Connections --- |

Catch ex As Exception
    Throw ex

    Finally

    ' --- This is where I originally kept my only Connection Close Statement.  
        It doesn't hurt having it here, so I left it in. --- |
    
If Not pc.State = ConnectionState.Closed Then
    pc.Close()
    pc.Dispose()
End If

End Try