Object pooling in C# throwing StackoverFlow Exception

Question

I am trying to implement object pooling in C#. My requirement is to have a pool which can hold 100 active SqlConnection objects. If the pool is already having 100 connections and if user requests a new connection, then pool has to wait till one existing connection is released.

Below is the code i used. I am getting StackOverFlow exception after pool is reached 100 in use objects.

Please suggest what could be the reason for StackOverFlow exception in below code.

class ObjectPoolingTest
{
    static void Main(string[] args)
    {

        int insertedRecords = 1;
        Parallel.For(1, 150000, i =>
        {
            test1(i);
            Console.WriteLine("{0} - Query executed", insertedRecords);
            insertedRecords++;
        }
        );
        Console.ReadKey();

    }

    static void test1(int hitNo)
    {
        var objConnection = Pool.GetConnection();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = objConnection.ConnectionObject;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "[dbo].[prSaveRecNumber]";
        cmd.CommandTimeout = 30;
        cmd.Parameters.Add("@recNo", SqlDbType.Int).Value = hitNo;
        int result = cmd.ExecuteNonQuery();
        Pool.ReleaseConnection(objConnection);
        Console.WriteLine(Pool.Message);
    }
}

public class Pool
{
    private static List<Connection> _available = new List<Connection>();
    private static List<Connection> _inUse = new List<Connection>();
    private static int MaxPoolSize = 100;

    private static object lock1 = new Object();
    private static object lock2 = new Object();

    public static string Message
    {
        get
        {
            return string.Format("Available: {0} - InUse: {1}", _available.Count, _inUse.Count);
        }
    }

    public static Connection GetConnection()
    {
        lock (lock1)
        {
            if (_available.Count != 0)
            {
                Connection connection = _available[0];
                _inUse.Add(connection);
                _available.RemoveAt(0);
                return connection;
            }
            else if ((_available.Count + _inUse.Count) != MaxPoolSize)
            {
                Connection connection = new Connection();
                connection.ConnectionObject = new SqlConnection("Server= abcd; Database=sai; User Id=sa; Password=abcd;");

                connection.ConnectionObject.Open();
                _inUse.Add(connection);
                return connection;
            }
            return GetConnection();
        }
    }
    public static void ReleaseConnection(Connection connection)
    {
        lock (lock1)
        {
            _available.Add(connection);
            _inUse.Remove(connection);
        }
    }
}

I suggest you learn how to use the debugger. It would quickly show you what's happening in the code and you could fix it quickly. Also since SqlConnection already has pooling internally, why do you want to implement it yourself? — Sami Kuhmonen, Jan 21 '17 at 16:50
Its not about debugger @SamiKuhmonen. You can not get the exact results when you are using recursion. I want to implement my own pooling just for some obvious reasons. Thanks for your response. Issue is solved with John Koerner solution — Krish, Aug 17 '17 at 11:32
You can get the exact results since you catch the exception and look at what causes it. That's what a debugger is for. As I said it would have immediately shown the infinite recursion problem. — Sami Kuhmonen, Aug 17 '17 at 11:44

score 1 · Accepted Answer · answered Jan 21 '17 at 16:33

You are recursively calling GetConnection when you reach the max of 100. Since you are doing 150000 in parallel, once you hit 100 in use (which happens quickly in this scenario) you will start the recursive calls and eventually hit the StackOverflowException, because the recursive calls happen much faster than the connections being freed and there are way more calls happening than connections available.

If the desired behavior is to wait a certain amount of time and try again, you will need to refactor the GetConnection call to be in an infinite loop, continuously calling the same code until it gets a connection.

You may also want to consider adding a timeout value, that if you cannot get a connection in a certain period of time, you throw an exception.

Finally, you probably only want to enter the locked code if you think there is a chance that there is a connection available. This results in double checks on the locks, but you will enter the locked code less frequently.

Thanks for the solution. Removing the recursive method call solved the issue — Krish, Jan 24 '17 at 13:27

Object pooling in C# throwing StackoverFlow Exception

1 Answers1