Creating a Custom Database Connection Pool

In this post, I’m going to show you how to create a database connection pool.

Based on my expertise, on high demand systems, database connections should be reused, instead of recreating them before each database operation. Although database providers already have their own connection pooling mechanism, they’re not very efficient. Let’s see how to create a custom database connection pool using C# and which requirements we need.

• Pool must be compatible with any database. Thus, our pool will work with ADO interfaces.
• Pool must be thread-safe. We don’t want two clients using the same connection at the same time.
• Pool must detect timed out connections

The DB Pool Class

public static class DbPool
{
    private IDbConnection[] Connections;
    private int POOL_SIZE = 100;
    private int MAX_IDLE_TIME = 10;
 
    private int[] Locks;
    private DateTime[] Dates;
}

Connections array will hold an array of 100 connections. We assume this number as the maximum concurrent connection. This should be enough for any large scale high demand system. The idea is to release the connection as soon as the database operation has been completed.

Locks array will hold integers 1 or 0. 1 means connection is in use, 0 means it’s free.

And finally, Dates array will hold the latest date and time the connection was used.

GetConnection Method

public IDbConnection GetConnection(out int identifier)
{
    for (int i = 0; i < POOL_SIZE; i++)
    {
        if (Interlocked.CompareExchange(ref Locks[i], 1, 0) == 0)
        {
            if (Dates[i] != DateTime.MinValue && (DateTime.Now - Dates[i]).TotalMinutes > MAX_IDLE_TIME)
            {
                Connections[i].Dispose();
                Connections[i] = null;
            }

            if (Connections[i] == null)
            {
                IDbConnection conn = CreateConnection();
                Connections[i] = conn;
                conn.Open();
            }

            Dates[i] = DateTime.Now;
            identifier = i;
            return Connections[i];
        }
    }

    throw new Exception("No free connections");
}

Our GetConnection method basically checks for a free connection. What the method actually does is to loop over all items in the array, and look for a ’0′ value, meaning that slot is not in use.

The important thing here is the Interlocked.CompareExchange method. This thread safe method does an atomic operation, checking the value and changing it, all in the same operation, and guarantees that no other thread will check the same slot at the same time. If the value is 0, set it to 1. The next thread will see this slot as busy and will continue to the next slot.

Inside the Interlocked block, we can check the last time the connection was used, or if it was never initialized, and create a new one. Now that we have a free connection, we return it, along with the identifier (the index in the connections array) for freeing the connection later.

CreateConnection Method

This method creates the connection object itself. I’m going to put the basic implementation for MySQL, but you can put all providers you want using flags or whatever method you prefer.

private IDbConnection CreateConnection()
{
    return new MySqlConnection(this.ConnectionString);
}

FreeConnection Method

public void FreeConnection(int identifier)
{
    if (identifier < 0 || identifier >= POOL_SIZE)
        return;

    Interlocked.Exchange(ref Locks[identifier], 0);
}

The FreeConnection method simply puts back the value ’0′ in the array, freeing the connection so other thread can use it. We don’t need to call Dispose on the connection any more, since the connections are managed by our pool.

Basic Usage

Now that we have our custom database connection pool ready, we can start using it. We always have to call FreeConnection method after completing the database operation, if not, the connection will remain ‘busy’ forever.

public void Test()
{
    int iConn = 0;
    try
    {
        IDbConnection conn = GetConnection(out iConn);
        using (IDbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "QUERY";
            cmd.ExecuteNonQuery();
        }
    }
    finally
    {
        FreeConnection(iConn);
    }
}

And that’s all, our custom database connection pool is ready!

We will see more ways to improve it in a future post.

Thanks for your attention.

CodeProject