Connection pools are used in various applications to streamline the access to a database or some such resource that needs to be used by various components of the application for brief periods of time. This article attempts to explain a simple way to implement a connection pool for use in your applications.
Whats a connection pool ?
A connection pool is an entity that manages connections to resources that are expensive to construct. A typical example of such a resource is a database connection. A database connection is expensive to establish, but once established, queries can be sent using the connection and results can be obtained. There is no limit to the number of queries that a connection can handle in its lifetime. Therefore, a Connection to a database is in some way a resource that is best utilized when shared among various modules accessing the same database.
A connection pool attempts to optimize the usage of a connection. Various modules can “check out” a pre-established connection from the pool, use it to perform the actions that are required and “check in” the connection once the work is done. This allows multiple modules to multiplex using the same connection(s), thus optimizing the usage of the connection and minimizing the expensive operation of recreating Connection objects.
What are the design goals ?
- The solution for the connection pool should be scalable to permit any number of simultaneous connections, without denial of Connection.
- The pool should manage the connections such that when there is load, the pool should be larger, and when there is no load, the pool should shrink to a predefined minimum size.
- Accessing the pool should be simple.
Keeping the goals in mind, lets design a simple connection pool that will let us connect to a mySQL database using a JDBC connection.
Lets dive into it !
The working of the pool is depicted by the following picture :
The steps outlined in the diagram explain a typical usage scenario. The steps are explained in detail below :
- Requests for connection : Multiple requests can arrive simultaneously asking for a connection to the database. The connection pool handles each of these requests in turn and hands them a Connection object from the pool.
- Connection handling : The pool maintains lists of used and unused connections. As a part of servicing a request for a connection, the pool picks a connection from the unused pool and moves it to the used pool before returning the connection.
- Checking out : The unused connection is returned as described in 2.
- Checking in : Once the module that requested a connection is done with using the connection, it is its responsibility to return it to the pool. This is done by checking in the connection to the pool. Once a module checks in the connection, the connection is removed from the used list and put back into the unused list of connections waiting to be checked out by new requests.
To implement the behavior of the pool as described above, we need the following :
- A pool object that will be the point of contact for various modules in the application. The modules will interact with this object using its public interface.
- A Vector to hold unused connection objects.
- A Vector to hold used connection objects.
Therefore, our Connection pool object will initially have the following structure :
import java.util.*;
import java.sql.*;
public class ConnectionPool
{
// A list of available connections for use.
private Vector m_AvailableConnections = new Vector();
// A list of connections being used currently.
private Vector m_UsedConnections = new Vector();
//Constructor
public ConnectionPool(String urlString, String user, String passwd)
{
}
}
Notice that the constructor accepts a URL string, a username and password. These parameters are required because henceforth, new Connections to the database will be made by the ConnectionPool itself. Therefore, it requires the parameters needed to create the connection. On construction, the pool is expected to create a basic set of connections that are ready to use, the number of such connections can be made configurable. For the sake of this example, we will use 5 as the number defined as a private member of the ConnectionPool object. Therefore, adding the code to create the connections leads us to the following :
import java.util.*;
import java.sql.*;
public class ConnectionPool
{
// Number of initial connections to make.
private int m_InitialConnectionCount = 5;
// A list of available connections for use.
private Vector m_AvailableConnections = new Vector();
// A list of connections being used currently.
private Vector m_UsedConnections = new Vector();
// The URL string used to connect to the database
private String m_URLString = null;
// The username used to connect to the database
private String m_UserName = null;
// The password used to connect to the database
private String m_Password = null;
//Constructor
public ConnectionPool(String urlString, String user, String passwd) throws SQLException
{
// Initialize the required parameters
m_URLString = urlString;
m_UserName = user;
m_Password = passwd;
for(int cnt=0; cnt<m_InitialConnectionCount; cnt++)
{
// Add a new connection to the available list.
m_AvailableConnections.addElement(getConnection());
}
}
private Connection getConnection() throws SQLException
{
return DriverManager.getConnection(m_URLString, m_UserName, m_Password);
}
}
On construction, the ConnectionPool object will now create 5 initial connections that are ready to be used and put them in the available connections list. Next, we need a way to check in and check out connections, so that the pool will always know which connections are used and which ones are free. Therefore, we need to add two public methods, checkin and checkout.
“checkout” will return a Connection object reference and move the returned object to the used connections list.
“checkin” will do exactly the reverse, will move the connection reference from the used list to the unused list.
One very obvious problem to consider is, what do we do when we run out of Connections to return ?
This condition can be handled in many ways, one being, returning an error and letting the client (the module that requested to check out a Connection) handle the condition.
This is not a very elegant way to do it, since error handling at the clients level for this feature can introduce many complexities in the client code. The better way is to always return a valid Connection. Where from ? Simple, just make one.
The following code shows the implementation of the checkout and checkin methods of the ConnectionPool.
public synchronized Connection checkout() throws SQLException
{
Connection newConnxn = null;
if(m_AvailableConnections.size() == 0)
{
// Im out of connections. Create one more.
newConnxn = getConnection();
// Add this connection to the "Used" list.
m_UsedConnections.addElement(newConnxn);
// We dont have to do anything else since this is
// a new connection.
}
else
{
// Connections exist !
// Get a connection object
newConnxn = (Connection)m_AvailableConnections.lastElement();
// Remove it from the available list.
m_AvailableConnections.removeElement(newConnxn);
// Add it to the used list.
m_UsedConnections.addElement(newConnxn);
}
// Either way, we should have a connection object now.
return newConnxn;
}
public synchronized void checkin(Connection c)
{
if(c != null)
{
// Remove from used list.
m_UsedConnections.removeElement(c);
// Add to the available list
m_AvailableConnections.addElement(c);
}
}
Notice that both methods are “synchronized”. This is required so as to make the access to a Connection object “thread safe”. If multiple threads call checkout at the same time, it is possible that the same Connection is returned to both, if the synchronized keyword is not used. To avoid this problem, the functions checkin and checkout are synchronized. In short, using “synchronized” makes sure that only one thread can access the ConnectionPool object at one time, making it safe to perform the checkin and checkout operations. For a more detailed explanation of the “synchronized” keyword, please visit the “Language Reference” section.
The checkout method looks at the available list to see if there are any readymade connections that can be returned. If there are, it returns the last Connection in the available list after moving it to the used list. If there are no readymade connections to be returned, the checkout method goes ahead and creates a Connection object and returns the newly created object after adding it to the used list of connections.
The checkin method merely moves the passed Connection object from the used list to the available list. Again, checkin has to be synchronized so that the access to the queue of Connections is serialized and Connection objects are available when they are needed after checkin is called for that object. If checkin is not synchronized, it is possible that the pool goes ahead and creates a Connection object even if one is available to be handed out, if the calls to checkin and checkout occur simultaneously.
Now lets test our little Connection Pool using a simple test program. For the sake of testing, we will add a function called “availableConnections()” to the ConnectionPool class. The work of this function is to return us the count of currently available connections. This function is implemented as follows :
public int availableCount() { return m_AvailableConnections.size(); }
Now we write a simple program that checks out n connections, and checks them all in afterward. At each check in and check out, we will list the available connections count. The output should give us an insight on whether our pool works well or not. The test program is as follows :
import java.sql.*;
public class Main
{
public static void main (String[] args)
{
try
{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
}
catch (Exception E)
{
System.err.println("Unable to load driver.");
E.printStackTrace();
}
try
{
ConnectionPool cp = new ConnectionPool("jdbc:mysql://localhost/test", "", "");
Connection []connArr = new Connection[7];
for(int i=0; i<connArr.length;i++)
{
connArr[i] = cp.checkout();
System.out.println("Checking out..." + connArr[i]);
System.out.println("Available Connections ... " + cp.availableCount());
}
for(int i=0; i<connArr.length;i++)
{
cp.checkin(connArr[i]);
System.out.println("Checked in..." + connArr[i]);
System.out.println("Available Connections ... " + cp.availableCount());
}
}
catch(SQLException sqle)
{
sqle.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
The output when this program is run (provided that your JDBC driver exists and you are able to connect to the database) is as follows. Checking out...org.gjt.mm.mysql.Connection@765291 Available Connections ... 4 Checking out...org.gjt.mm.mysql.Connection@26e431 Available Connections ... 3 Checking out...org.gjt.mm.mysql.Connection@4f8dab Available Connections ... 2 Checking out...org.gjt.mm.mysql.Connection@5debc3 Available Connections ... 1 Checking out...org.gjt.mm.mysql.Connection@218aa2 Available Connections ... 0 Checking out...org.gjt.mm.mysql.Connection@14ca6c Available Connections ... 0 Checking out...org.gjt.mm.mysql.Connection@7590db Available Connections ... 0 Checked in...org.gjt.mm.mysql.Connection@765291 Available Connections ... 1 Checked in...org.gjt.mm.mysql.Connection@26e431 Available Connections ... 2 Checked in...org.gjt.mm.mysql.Connection@4f8dab Available Connections ... 3 Checked in...org.gjt.mm.mysql.Connection@5debc3 Available Connections ... 4 Checked in...org.gjt.mm.mysql.Connection@218aa2 Available Connections ... 5 Checked in...org.gjt.mm.mysql.Connection@14ca6c Available Connections ... 6 Checked in...org.gjt.mm.mysql.Connection@7590db Available Connections ... 7
Isn’t something terribly wrong? Notice the number of available connections decreasing as they get checked out. As soon as connections start getting checked in, the available connections count starts increasing. This is expected behavior, just that, the number of available connections goes to 7 which is 2 Connections more than we started with.
The problem with the current implementation is, the total number of Connections will always be equal to the maximum number of Connections that were checked out during the lifetime of the Connection Pool. Therefore, since maximum 7 Connections were checked out concurrently at one time, the maximum number of Connections also becomes 7. These extra connections will never go away. This is not desirable in cases where there is large traffic for 1 % of the time but for 99% of the time, the traffic is low. Because of this, we need to do some “policing” and make sure that extra connections that are not required do not stay around for a long time. Therefore, we need to implement a cleanup procedure that periodically cleans up these extra connections.
To implement a cleanup procedure that cleans up unwanted connections, we will need a thread that sits dormant and runs the cleanup code periodically. This thread can be implemented as a part of the Connection Pool itself and will get created when the Pool is created. The thread sleeps for a period of time and cleans up unwanted Connections when it runs. The implementation of the thread is as follows :
public void run()
{
try
{
while(true)
{
synchronized(this)
{
while(m_AvailableConnections.size() > m_InitialConnectionCount)
{
// Clean up extra available connections.
Connection c = (Connection)m_AvailableConnections.lastElement();
m_AvailableConnections.removeElement(c);
// Close the connection to the database.
c.close();
}
// Clean up is done
}
System.out.println("CLEANUP : Available Connections : " + availableCount());
// Now sleep for 1 minute
Thread.sleep(60000 * 1);
}
}
catch(SQLException sqle)
{
sqle.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
}
}
The clean up thread shown above runs every minute and cuts down the size of the available connections list, so that only 5 available Connections are allowed to persist. Therefore, after every minute, the list of available Connections gets trimmed down to 5, which is the initial Connection count. Note that the actual number of Connections can be more than 5 since the thread only worries about the available connections irrespective of how many connections are in use. Also note that the Connection Pool is synchronized before the cleanup is done, again to avoid other threads meddling with the Connections as cleanup happens.
After this change, the output of our test program is as follows :
Checking out...org.gjt.mm.mysql.Connection@765291 Available Connections ... 4 Checking out...org.gjt.mm.mysql.Connection@26e431 Available Connections ... 3 Checking out...org.gjt.mm.mysql.Connection@4f8dab Available Connections ... 2 Checking out...org.gjt.mm.mysql.Connection@5debc3 Available Connections ... 1 Checking out...org.gjt.mm.mysql.Connection@218aa2 Available Connections ... 0 Checking out...org.gjt.mm.mysql.Connection@14ca6c Available Connections ... 0 CLEANUP : Available Connections : 0 Checking out...org.gjt.mm.mysql.Connection@7590db Available Connections ... 0 Checked in...org.gjt.mm.mysql.Connection@765291 Available Connections ... 1 Checked in...org.gjt.mm.mysql.Connection@26e431 Available Connections ... 2 Checked in...org.gjt.mm.mysql.Connection@4f8dab Available Connections ... 3 Checked in...org.gjt.mm.mysql.Connection@5debc3 Available Connections ... 4 Checked in...org.gjt.mm.mysql.Connection@218aa2 Available Connections ... 5 Checked in...org.gjt.mm.mysql.Connection@14ca6c Available Connections ... 6 Checked in...org.gjt.mm.mysql.Connection@7590db Available Connections ... 7 CLEANUP : Available Connections : 5
Notice the cleanup thread in play as it cleans up the surplus unused Connection objects so as to keep the Pool lean and mean.
Summary
Connection pools are very useful for connecting to databases, especially when there are multiple modules accessing the same database. Connection pools reduce the overhead of connecting to a database for every query, at the same time achieving parallelism in sending queries, as opposed to the method of static Connections.