Think you're Connection Pooling? Think Pooling=True is all you need? I think you might want to read this...
With this post I want to explain certain details about the way connection pooling works with SQL Server based applications. In the last months. it has been a recurrent topic I’ve had to discuss with some customers because of their misconceptions on the topic.
For the purpose of illustrating my explanations here, I’ve built a sample Windows Forms app whose only ability is that of opening and closing a unique instance of a SqlConnection object to send different statements to a given instance of SQL Server while leveraging ADO.NET’s connection pooling.
What I want to emphasize to SQL Server developers is that just because you are using connections that participate in a connection pool, you cannot forget about good programming practices. The price you have to pay for many poor programming errors is even higher when you are using connection pooling. For example, in a scenario where every connection is physically open whenever you call the Open method of the connection object, and also physically closed immediately after you call its Close method, leaving a transaction open is something that will be resolved earlier (when the connection’s Close method is invoked). If that happens with a connection that is pooled, the orphan transaction won’t be rolled back until someone else doesn’t pick up that idle connection from the pool and runs something over it.
Closing server cursors, deallocating them, dropping temporary objects or resolving your transactions is more important than ever when you use any connection pooling mechanism.
Believing that by the time your program calls Close on an open connection that is pooled, will immediately take care of cleaning up the mess you may have created on the server is simply wrong.
Also, believing that just because you have enabled connection pooling you are exempt from doing things properly, it is absolutely wrong.
And the last misconception I want to highlight is one based on something I detected in one customer a couple of weeks ago. Their Java based application created several hundred connections which were pooled. But the application never called close on them, so any new incoming request for a connection from the pool would always have to create a freshly new one. Q: Do you know how do we call that in my home town? A: A completely useless connection pool.
I thought this was an outstanding article on connection pooling (nothing like digging into the TFDS stream!), with great and easy to understand examples and code. Again, if you think you're Pooling, you're going to want to read this post...