Understanding Connection pooling

[Mario Cantani]

Hi,

I've read a bit about connection pooling, and I still have some questions
that I hope you guys can answer..., so here goes.

1) Is connection pooling provided by a provider? And if so, does the
provider have to be a managed provider?

2) Does the connection have to be explicitly closed for it to be returned to
the connection pool?

3) What happens when the connection is not explicitly closed and another
call is made to the DB?

4) What happens to connections that never get explicitly closed?

5) When the connection is returned to the connection pool, is the 'real
connection' to the DB severed? And if not, how long does the connection
remain open?

6) Are there any differences in connection pooling for Win or Web apps?


Thanks in advance for your response!

 

[Jared]

I'm no ADO expert, so, please research this topic further.
Jared

1: No, I know in MSSQL 2000 pooling is constructed in the connection string,
which does not necessarily have to use a managed provider (SqlConnection,
SqlCommand, SqlDataReader, etc.). This feature is provided by your server,
so, you should be able to connect to it in any way you like as long as you
follow the guidelines.
2: No - But you should make sure the connection is closed.
3: You app/site will wait out the timeout threshold, then, return an error
4: I will be returned to the pool when the timeout is exceeded. In the case
of using a dataadapter, it implicitly opens/closes the connection for you.
5: It's marked as invalid, the server will release the connection at
periodic intervals
6: I don't think so, again, this feature is provided at the server level,
not at the application level.

ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.htm
- or -
http://msdn.microsoft.com/library/d...nectionpoolingforsqlservernetdataprovider.asp

 

[William \(Bill\) Vaughn]

1) Yes. Each provider implements connection pooling on its own. Most follow
similar approaches but each is controlled differently and to different
degrees by the ConnectionString.
2) No. Any operation that closes the connection such as passing a DataReader
with CommandBehavior.CloseConnection to a complex bound control. You can't
assume that a Connection falling out of scope will be returned to the pool
in a timely manner.
3) If a closed connection is not available in the pool, a new connection is
opened and placed in the pool.
4) If a connection is not closed in scope it is orphaned in the pool and
cannot be reused. Eventually, the pool will fill and your requests to open
additional connections will time out.
5) Once a connection is closed, it begins to age in the pool. It remains
there for 4-8 minutes and the database connection remains open. If it is not
reused in that time, the connection is closed and the connection is dropped
from the pool (unless less than "minimum pool members" are available.
6) Yes and no. The pooling mechanism is the same for both, but remember each
process gets its own pool. ASP applications share pools based on the
Application Domain, Windows apps each get their own pool. Each pool is built
on the client. Windows apps on the client system, ASP apps on the IIS
server.

See my article on connection pooling or more details in my book.

http://www.betav.com/msdn_magazine.htm

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I'm no ADO expert, so, please research this topic further.
Jared

1: No, I know in MSSQL 2000 pooling is constructed in the connection
string, which does not necessarily have to use a managed provider
(SqlConnection, SqlCommand, SqlDataReader, etc.). This feature is provided
by your server, so, you should be able to connect to it in any way you
like as long as you follow the guidelines.

2: No - But you should make sure the connection is closed.
3: You app/site will wait out the timeout threshold, then, return an error
4: I will be returned to the pool when the timeout is exceeded. In the
case of using a dataadapter, it implicitly opens/closes the connection for
you.

 

[Michael D. Long]

Mario,

5. Some time back Joe Long had informed me that the connection is discarded
at a random interval in the range of 3 to 8 minutes, which closely coincides
with Bill's intelligence. I think it is safe to say that 8 minutes is the
intended upper end. However, you may find as I have that under extremely
heavy loads some connections never properly clear, regardless of the
technique used to close them.

6. The pool is based on the complete connection attributes, which includes
the exact connection string with credentials and also the distributed
transaction identifier. The latter point is frequently omitted in
discussions on this topic, but has a significant impact on the number and
frequency of connection requests.

 

[Angel Saenz-Badillos[MS]]

Bill has as always provided the right info. For the SqlClient and Oracle
managed providers the connections don't get cleared while in use, they are
cleared after being idle in the pool for 4 to 8 minutes (7 minutes 40
seconds actually in the current implementation). Under heavy loads the
connections are not idling in the pool and so they do not get cleared.

What about for Oledb and Odbc? you will have to look at the docs for the
pooling implementation in the native provider that you are using.



--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

 

[Michael D. Long]

I beg to differ on the point of heavy loads - my reference isn't to "idling
connections", but connections that are no longer servicing any requests and
*never* get reused or disposed. I'll admit that never could be an
overstatement, but I've left them in such a state for > 48 hours and not a
single connection was ever released, so for all practical purposes this
equates to the same thing.

The behavior only exibits with distributed transactions, at around 30 tps
for Oracle and 112 tps for SQL Server. I'd provide yet more repros, but it
is pointless to do so. Oracle allocated several man-months to this issue
and made significant changes to the XA layer based on the code I supplied,
but I've never observed any serious interest from Microsoft. I guess it has
to do with the level of pain - there are so few applications being developed
that actually push these limits that resources will not be allocated to
address the issue.

 

[Angel Saenz-Badillos[MS]]

Michael,
That is a great point I apologize. Connection pooling works completely
differently when using Distributed Transactions. Connections involved in a
dtc are not returned to the general pool, instead they are placed in a
subpool where they wait for a notification from the DTC transaction. The
expected behavior is that after we get notified that the transaction has
completed (one way or the other) we will place this connection back in the
general pool.

I take it from your post that this is not the behavior that you are seeing?
I am very interested in following up with any issues related to distributed
transactions and I would be happy to follow up with you on this issue. Feel
free to contact me directly at the email shown here minus the .online. part.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/