NonPooled connections + System.Transaction.TransactionScope ?

Topics: Data Access Application Block
Mar 15, 2007 at 5:49 AM
Edited Mar 16, 2007 at 1:58 AM
Is anyone else using the System.Transaction.TransactionScope and noticed that they are getting non-pooled connections only??????

If not, doesn't anyone know how to debug the EntLib to see how/where a non-pooled connection is being created to try and see why that is happening?

Info: Using 3.0 Feb CTP in release mode, manually strong key signed.
Links: this is a link where i learnt about using TransactionScope with EL3.0: http://davidhayden.com/blog/dave/archive/2007/01/27/TransactionScopeDataAccessApplicationBlock.aspx

edit: updated post to include formatting.
Mar 15, 2007 at 3:15 PM
The DAAB has a couple of additional classes to support System.Transactions that has made debugging a little more complicated associated with connections. The two classes that come to mind are the ConnectionWrapper and TransactionScopeConnections Classes.

In particular, the TransactionScopeConnections Class keeps track of connections involved in transactions in a Dictionary Class for reuse and then disposes of them when the transaction is over. The ConnectionWrapper class encapsulates the DbConnection Class and holds an additional boolean field that decides if the DbConnection Class is to be disposed of or not.

The creation of new connections seems appropriate as new connections are created with the appropriate DbProviderFactory.CreateConnection() method. However, perhaps the connections are not properly being disposed of at the end of the transaction due to some sort of bug in ConnectionWrapper or TransactionScopeConnections and hence never being put back into the pool.

If you believe it is an issue, I would add it to the IssueTracker so someone on the EntLib Team is aware of the issue and can verify it. They may want to know more about how you are determining the behavior.

Regards,

Dave

________________________

David Hayden
Microsoft MVP C#
Mar 15, 2007 at 5:34 PM
purekrome: When a TransactionScope is active, the EntLib 3.0 DAAB will not open and close the connection on each call like it normally does. Instead it reuses the same connection object for all calls in the transaction. This is done to prevent escalation of the transaction to DTC and hence improves performance.

Is this the behavior you are seeing, or is it something else? How can you tell that the connection is non-pooled?

thanks
Tom
Mar 16, 2007 at 1:56 AM
tomhollander: I constantly use perfmon to test my database connection stuff to make sure I have done the right thing. Lnked is a screen shot of the current perfmon results: http://img412.imageshack.us/img412/9550/nonpooledconnectionsym3.png

If you notice in the image, there are two 'domains' ... i'm not sure which one is which, but I only use WebDev.exe (not IIS) and vsts Unit Tests (which i'm not sure if it uses WebDev.exe again).

I was under the impression that the TransactionScope would reuse the connection because before I refactored my code to use the TransactionScope model, i MANUALLY passed around the current transaction to my child objects (aggregations) which the root object would create. That code worked great, but i prefer the TransactionScope model because it seems to be a lot cleaner and simpler.


DavideHayden/TomHollander: is there particular method in the EntLib 3.0 code which i could manually step through to see when the connection is actually created and therefore see what perfmon says the connection type is :: pooled/non-pooled? Maybe it's not being closed/disposed of correctly like DH suggested?

If you require me to do something specific to prove that it's not the EntLib but some weird code bug in my own code, I'm all ears and eager to try anything you guys (eg. do this type of debugging, etc.)

Thanks heaps guys for the posts, also.

Regards,
Justin.
Mar 20, 2007 at 5:48 AM
Just touching base again to see if anyone can provide further feedback.

cheers.