A Resource Pool recycles resources to minimize resource initialization overhead. A resource pool manages resources efficiently while allowing application code to freely allocate them.
Connection initialization is one of the slowest data access operations. And because connecting to a database is a prerequisite for any data access operation, it tends to introduce significant bottlenecks to even the most simple data access operations. Server-side software services many concurrent users and using the same connection to all users is simply not possible, unless you streamline all access to the database which will effectively slow server-side processing to absolutely unacceptable levels.
You can alleviate the above situation by creating a connection for each server-side request. This strategy often results in many open connections than an application actually needs because most of these connections sit idle while end-users peruse the data just returned to them.
A much effective approach is resource pooling. A resource pool manages a set of active resources that are recycled for repeated use. For example, code requiring an open connection to the database obtains such a connection from the connection (resource) pool, uses it to access the database, and then checks the connection back to the connection pool for others to use. If code requests a resource (connection) from the pool and none is available, then it is the responsibility of the pool to create a new resource - assuming the pool does not impose a configurable upper limit on the number of active resources (connections). Also note that a resource pools keep resources active (connections open) so they can be recycled.
The following figure illustrates the static structure of the Resource Pool pattern:
The Resource interface represents any database resource such as a connection (in ADO.NET this would be IDbConnection interface). A ConcreteResource implements Resource interface for a particular database platform (in ADO.NET this would be SqlConnection for SQL Server database). Note again that client code is written in terms of Resource (IDbConnection) but interacts directly with a ConcreteResource (SqlConnection) instance.
ResourcePool is a class that manages a pool of one or more recycled resources. Clients get a resource from ResourcePool by calling its GetResource method. ResourcePool wraps each ConcreteResource (SqlConnection) that it manages in a PooledResource. PooledResource is actually a resource decorator which decorates any Resource implementation (ConcreteResource and hence SqlConnection) with a special Close behavior while delegating all other remaining operations. The key point to the whole pattern is this: closing a PooledResource has the effect of returning the ConcreteResource to the ResourcePool without actually closing it (in the case of a database connection, the connection remains open).
If all Resouces (connection) are identical then ResourcePool can hand back any available Resource in conjunction with the request. However, a ResourcePool can in fact manage different Resources (different connection for multiple data sources). In cases like this, its GetResource operation defines a parameter (ResourceKey) identifying which kind of Resource (data source connection) to return.
The following figure shows what happens when a client requests a resource from a ResoucePool:
ResourcePool may discover that it has no available Resources (IDbConnection) so it creates a new ConcreteResource (SqlConnection) and decorates it a with a PooledResouce (PooledSqlConnection). ResourcePool returns a PooledResource to the client.
PooledResource delegates all its calls to its referenced ConcreteResource with the exception of the Close operation. When clients call Close on a PooledResource, PooledResource intercepts this call and places its referenced ConcreteResource into the ResourcePool for recycling. The connection on ConcreteResouce is not closed: This situation is shown below:
The next time a client calls GetResouce, the ResoucePool will remove an existing ConcreteResouce (see first sequence diagram) and return a new PooledResource. This is because ResourcePool already contains a ConcreteResource and does not need to create one from scratch as was the case with the first sequence diagram. This situation is shown below:
Note that a side effect of keeping ConcreteResources open is that clients can potentially use them after returning them to the ResourcePool. When this happens, multiple clients would share the same ConcreteResource leading to problems that will be very difficult to diagnose. To prevent this possibility, ResourcePool creates a new PooledResource for every request - once a PooledResource has returned its ConcreteResource to the pool, it rejects any subsequent operations.
The following example shows how to implement a database connection pool:
// Note: this is a non-working
implementation and is used to illustrate related concepts
public class ConnectionPool
/* Data members */
private Hashtable m_htConnectionStringToConnectionList = null;
private SqlConnection m_sqlConn = null;
/* Public interface */
public SqlConnection GetConnection( string strConn )
// Get list of available connections for this connection string. If this is a
// new connection string, create an empty connection list (it will be populated later)
ArrayList alConnections = null;
if (m_htConnectionStringToConnectionList.Contains( strConn ))
alConnections = m_htConnectionStringToConnectionList[strConn];
alConnections = new ArrayList();
m_htConnectionStringToConnectionList.Add( strConn, alConnections );
// We now have a connection list. If there is at least one SqlConnection objects,
// return it. Otherwise create a new connection
SqlConnection sqlConn = null;
if (alConnections.Count > 0)
sqlConn = alConnections[alConnections.Count - 1];
sqlConn = new SqlClientPermission( strConn );
// Now decorate this connection with a PooledConnection
m_sqlConn = new PooledSqlConnection( this, sqlConn );
public void PutConnection( SqlConnection conn )
// Get the connection list for the associated connection string
ArrayList alConnections = m_htConnectionStringToConnectionList[conn.ConnectionString];
// Add the connection to the connection list
alConnections.Add( conn );
public class PooledSqlConnection : SqlConnection
/* Data members */
SqlConnection m_sqlConn = null;
ConnectionPool m_Pool = null;
bool m_bClosed = false;
/* Constructor */
public PooledSqlConnection( ConnectionPool pool, SqlConnection conn )
m_sqlConn = conn;
m_Pool = pool;
public override void Close()
// Mark this connection as closed
if (!m_bClosed) m_bClosed = true;
// Close any associated transactions
// How? Need more info that just a connection object
// Return connection to the connection pool
m_Pool.PutConnection( m_sqlConn );
// Get a connection from the pool
ConnectionPool pool = new ConnectionPool();
SqlConnection conn = pool.GetConnection( strConnectionString )
// Use the connection
// Return connection to pool
pool.PutConnection( conn )
Use this pattern when:
Consider these strategies when designing a resource pool
Clients can use resource keys to specify certain required criteria when requesting a resource from the pool. The type and structure of the resource key does not matter as long as it follows these semantics:
In the case of database connections, this resource key is typically the connection string. This implies that a resource key typically encapsulates all initialization parameters.
You can make the resource pool completely configurable by exposing a few related properties.. A resource pool implementation should encapsulate these properties completely and even provide an administrative utility to allow administrators to tailor these settings for each environment. Resource pool properties include:
Beware of resources whose close operations affect other dependent resources. For example, IDbConnection.Close rolls back any pending transactions. In other words, users expect any pending transactions to be rolled back when calling Close on an ADO.NET connection object. However, we stated earlier that closing a PooledResource has the effect of returning the ConcreteResource to the ResourcePool without actually closing it. Therefore, a PooledResource Close implementation should ensure that all dependent resources are released as clients expect, In the case of IDbConnection (Resource) and SqlConnection (ConcreteResource), the PooledResource Close implementation should roll back any pending transactions.
You can include an automated cleaning mechanism that reduces the number of resources a pool manages when its utilization decreases. For example, one cleaning issue would be dealing with clients that forget to call release the connection to the pool (i.e., clients that forgot to call Close). You can use the Resource Timer pattern to solve this problem to automatically return resources to the pool after a period of inactivity. Another cleaning issue would be dealing with high loads during the workday but with low loads during the night. Again, the Resource Timer pattern can be used to solve this problem to automatically decrease the number of open resources during nighttime, but to increase them substantially during the workday.