Many developers need to look at the documentation when building a SQL Server connection string for a real-world application. Among the various attributes, one has always captured my attention until I correctly figured out its whys and wherefores. The attribute is “Connection Lifetime.”
The “Connection Lifetime” attribute indicates in seconds the time a connection object is considered valid. Elapse the time, the connection object is disposed of. Fine, but why on earth should you be getting rid of a perfectly working connection object? As it turns out, the attribute is expressly designed to address a well-known situation, and should never be used otherwise.
Imagine you have a cluster of servers sharing the workload. At some point, you realize the load is too high and turn on an additional server. What do you think will happen? You expect that the newly added server picks up most of the newly allocated work to mitigate impact on others. There might situations in which this just doesn’t happen and the new server remains idle.
This nasty effect occurs when middle-tier components cache the connection objects for performance reasons and never open new ones. By disposing of working connections, you force middle-tier components to create new connections. Needless to say, new connections will be assigned to the least loaded server. In the end, set “Connection Lifetime” only if you’re in a cluster scenario.
Partially related to this issue is the problem of clearing the pool of connection objects that ADO.NET creates when connection pooling is enabled. Until ADO.NET 2.0, there was no way to programmatically clear the pool of open connections. This is not an everyday operation, but it becomes essential when the database server goes down and is then restarted. As a result, all connection objects in the pool are invalid because they hold a reference to server connections that no longer exist. What happens when a new connection is requested?
The pooler returns an apparently valid connection object to the caller and the caller runs the command. Unfortunately, the connection object is not recognized by the database server, resulting in an exception. The connection object is removed from the pool and replaced. To make it short, shutting down the server without shutting down the application brings the connection pool to an inconsistent state.
This situation is common for applications that deal with server reboots, like a failover cluster. Only one solution is possible—flushing the connection pool. It is not as easy to implement as it may seem at first. Fortunately, in ADO.NET 2.0, the solution comes with the framework. ADO.NET 2.0 is smart enough to recognize when a command exception is due to a possible corruption of the pool. In this case, it walks down the pool and marks each connection as obsolete. When does a command exception indicate pool corruption? It has to be a fatal exception raised from the network layer on a previously opened connection. All other exceptions are ignored and bubble up as usual.
Connection classes define two new static methods for clearing the connection pool. They are ClearPool and ClearAllPools and are defined for both SqlConnection and OracleConnection. These methods can be used to programmatically clear the pool if you know that the server has been stopped and restarted. At the same time, these methods are used internally by ADO.NET 2.0 to clear the pool when a particular command exception is detected.
Dino Esposito is Wintellect's ADO.NET and XML expert, and a trainer and consultant
based in Rome, Italy. Dino is a contributing editor to Windows Developer
Network and MSDN Magazine, and the author of several books for Microsoft
Press including Building Web Solutions with ASP.NET and ADO.NET
and Applied XML Programming for .NET. Contact Dino at [email protected].
