Managing database connectivity with ADO.NET

问题:

We have an application that is built upon ADO.NET. We follow some simple best practices that allow us to make use of the connection pool. For example, a block of code that is using a database might look something like this:

using( DbConnection dbConnection = GetDatabaseConnection() ) {   
    doWork();
}

FWIW, there's nothing special about GetDatabaseConnection. It establishes a connection with the database which is running MSSQL Server 2000. In fact, it reads as follows:

DbConnection GetDatabaseConnection() {
    return GetConnection(MyConnectionString);
}

DbConnection GetConnection(String connectionString)
{
  try {
      SqlConnection dbConnection = new SqlConnection(connectionString);
      dbConnection.Open();
      return dbConnection;
  } catch( InvalidOperationException ex ) {
      handleError(ex);
      throw;
  } catch( DbException ex ) {
      handleError(ex);
  }
}

As such, our connections are disposed of at the end of the block scope. However, we ran into a little glitch as we began to test the application. We found that our application is very bursty meaning there are times when it becomes very chatty and then goes silent for a while. The result is that we can have multiple threads simultaneously all coming up to obtain a connection.

So imagine you have 10 threads. A batch of work (please don't try to rephrase the batch of work) arrives and is segmented across a few threads. Each thread then attempts to obtain a connection and boom, I'm hit with an InvalidOperationException. I've adjusted the ConnectTimeout and all that does is stretch out the time until I get a burst of exceptions. Once I get past the "wind-up" phase, the application is fine. Then it quiesces again, the connections "go away" and the process begins again.

I've also tried tweaking the LoadBalanceTimeout but the exceptions continue to roll in. Have any of you seen this issue before? Any thoughts ... I'll throw out a couple of my own.

• Continually keep some connection "hot"
• Attempt to open the connection again up to some # of attempts
• Implement my own connection pooling (bleah, not interested in reinventing the wheel)

Edit:

Most forums I've read have discouraged increasing the connection pool size. By default the connection pool has an upper bound of 50 connections (that's more than enough -- if I have to increase it, something's fundamentally wrong elsewhere). What I notice is that when the ConnectTimeout is low, the InvalidOperationException occurs. It's as if the spin-up of the connection is too long and the pending connections all timeout.

MARS is certainly an option... The text of the InvalidOperationException.Message is:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

回答1:

From the MSDN (http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx):

When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, a connection must be unused, have a matching transaction context or be unassociated with any transaction context, and have a valid link to the server.

The connection pooler satisfies requests for connections by reallocating connections as they are released back into the pool. If the maximum pool size has been reached and no usable connection is available, the request is queued. The pooler then tries to reclaim any connections until the time-out is reached (the default is 15 seconds). If the pooler cannot satisfy the request before the connection times out, an exception is thrown.

Translation: Check your transaction contexts... if you have a pool size of 10 connections, and 10 connections have been created under different transactions, you're screwed.

Note that a severed connection can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only when they are closed or reclaimed.

If a connection exists to a server that has disappeared, this connection can be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur. When this occurs, the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown.

Translation: You can't really rely on a connection to be connected? The article doesn't really explain how to handle this...

You could try manually clearing the pool occasionally using ClearAllPools and ClearPool, but this still sounds like a band-aid to me, makes me cringe.

The article also discusses Security Contexts, saying:
After a SQL Server application role has been activated by calling the sp_setapprole system stored procedure, the security context of that connection cannot be reset. However, if pooling is enabled, the connection is returned to the pool, and an error occurs when the pooled connection is reused.

I'm starting to wonder why I use connection pooling...

And finally:
Pool Fragmentation Due to Integrated Security
Connections are pooled according to the connection string plus the user identity. Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server.

So if you're using integrated security on a web app, you can fill up your connection pool if you have enough users.

Without knowing more specifics on your application, it's hard to zoom in on what might be tripping you up, but hopefully this gives you some ideas where to look.

HTH

回答2:

You can try setting "Max Pool Size" higher. Also you may want to try explicitly calling "Close" on the connection.

回答3:

Question 1: Does your GetDatabaseConnection() method spin off a new thread to generate the database connection and return it to the main thread... or are multiple threads trying to access this one isntance of the method... or is this method a shared/static method that just grabs a connection from the pool?

Question 2: What make and model is your DB server? SQL Server? Oracle? PostgreSQL?

Question 3: Do you require all the work completed on different connections, or if it could all be done on one, would that suffice? If SQL Server, then perhaps MARS allowing multiple recordsets on a single connection might help, but may not be feasible for your app.

Question 4: What is the detail in the returned exception?

Just trying to get a clear picture of how your application architecture is working...

(P.S. Does anyone know how I flag an "answer" as "not an answer, but a request for further clarification"... i.e. for this "answer")

回答4:

According to MSDN, InvalidOperationException is thrown by SqlConnection.Open if you haven't specified a data source or server, or if the connection is already open.

Are you sure you don't have any calls to SqlConnection.Open in your "doWork" method?

Also your code in the GetConnection method swallows DbException.