Gotcha: Connection Property on ADO.Net Transaction

In a recent project, the application utilized a custom Data Access Layer that provided an API for managing connectivity to the database. The API had an abstract definition that business logic would call into in order to define the explicit Unit of Work boundaries that were being performed. This forced the business logic to create an abstract transaction object to begin, create or rollback the unit of work. The concrete implementation had connectors for both MSSQL and Oracle that manipulated the respective ADO.Net libraries.

The Transaction class for ADO.Net has a property for a connection reference. When an abstract transaction was started, the concrete implemalentation would create a new OracleConnection or SQLConnection and then grab a transaction from that. Once, the transaction had ended the connection property would be checked if still available, and if it was – it was closed.

At a client site, we started noticing connection leak messages were appearing in the application. When checking the open sessions for the user, it was found that the connections would quickly reach the default maximum of 100. I managed to narrow it down to the fact that the concrete implementation of the transactions was the cause. Upon further investigation, I found that when an ADO.Net transaction is committed, it will lose its reference to the connection, and since the connection was not being garbage collected fast enough within this tight loop, the pool limit would be hit. Once this was resolved by holding an explicit reference to the connection, the open connections went down drammatically and all was right in the world.

Advertisements

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: