C# MySQL connection pool limits, and cleaning up connections

Question

I have a simple DB manager class (a grander name than it's abilities deserve):

class DbManager
{
    private MySqlConnectionStringBuilder _connectionString;

    public DbManager()
    {
        _connectionString = new MySqlConnectionStringBuilder();
        _connectionString.UserID = Properties.Database.Default.Username;
        _connectionString.Password = Properties.Database.Default.Password;
        _connectionString.Server = Properties.Database.Default.Server;
        _connectionString.Database = Properties.Database.Default.Schema;
        _connectionString.MaximumPoolSize = 5;
    }


    public MySqlConnection GetConnection()
    {
        MySqlConnection con = new MySqlConnection(_connectionString.GetConnectionString(true));
        con.Open();
        return con;
    }

}

I then have another class elsewhere that represents records in one of the tables, and I populate it like this:

class Contact
{
    private void Populate(object contactID)
    {
        using (OleDbConnection con = DbManager.GetConnection())
        {
            string q = "SELECT FirstName, LastName FROM Contacts WHERE ContactID = ?";

            using (OleDbCommand cmd = new OleDbCommand(q, con))
            {
                cmd.Parameters.AddWithValue("?", contactID);

                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        this.FirstName = reader.GetString(0);
                        this.LastName = reader.GetString(1);
                        this.Address = new Address();
                        this.Address.Populate(ContactID)
                    }
                }
            }
        }
    }
}


class Address
{
    private void Populate(object contactID)
    {
        using (OleDbConnection con = DbManager.GetConnection())
        {
            string q = "SELECT Address1 FROM Addresses WHERE ContactID = ?";

            using (OleDbCommand cmd = new OleDbCommand(q, con))
            {
                cmd.Parameters.AddWithValue("?", contactID);

                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        this.Address1 = reader.GetString(0);
                    }
                }
            }
        }
    }
}

Now I thought that all the using statements would ensure that connections are returned to the pool as they're done with, ready for the next use, but I have a loop that creates hundreds of these Contacts and populates them, and it seems the connections are not being freed up.

The connection, the command and the reader are all in their own using statements.

Solution

If the app is multithreaded then you are potentially going to have say 10 threads running at the same time. Each of those needs its own connection but if you are limiting that pool size to 5 then you the 6th thread is going to be unable to get a connection from the pool.

You may be limiting your threads in some way but I'd suggest increasing the size of your app pool significantly to ensure that you have more connections available than you might have threads. As an indicator the default size (which is normally good enough for most people) is 100.

Additionally if you have any recursion inside your using block, (eg calling the populate again) as you have indicated you have in comments as opposed to the code above then you are going to run into further problems.

If you call populate inside the using block then you will have the connection from the parent open and in use (so not reusable) and then the child call will open another connection. If this happens just a few times you will run out of your allocation of connections.

To prevent this you want to move the secondary Populate call out of the using block. The easiest way is rather than looping through your recordset calling populate for each ID is to add the IDs to a list and then after you've closed your connection then do the populate for all the new IDs.

Alternatively you could just lazily evaluate things like the Address. Stored the addressID in a private field and then make Address a Property that checks if its backing field (not the addressID) is populated and if not then looks it up with the AddressID. This has the advantage that if you never look at the address you don't even do the database call. Depending on use of the data this may save you a lot of database hits but if you definitely use all the details then it just shifts them around, potentially spreading them out a bit more which might help with performance or maybe just making no difference at all. :)

In general with database access I try to just grab all the data out and close the connection as soon as I can, preferably before doign any complicated calculations on the data. Another good reason for this is that depending on your database query, etc. you could potentially be holding locks on the tables that you are accessing with your queries that could cause locking issues on the database side of things.

Comments

[Cylindric]

I am limiting my threads to throttle the remote DB connections a bit, but I think I may have failed to take into account the fact that each main "populate" requires about three connections, one for the parent, one to find the children, and one to populate each child. So connections = 3*threads

[Cylindric]

Our edits collided. Seems commenting is not thread-safe... You appear to be correct, I think it was exploding connection numbers.

[Chris]

If you are limiting your threads anyway then I wouldn't worry about limiting your threads too much. If you are only ever going to have five threads then having fifty connections in the pool won't matter. It won't create all fifty up front, it will just create them as they are needed and keep them around for a while. And if there are unforseen situations like this then you have the buffer. If you are wanting to be really strict with your database connections then I am sure there are ways to do a kind of locking on getting connections such that a thread can be made to wait until it can get a con

[Cylindric]

Yeah, I only set connection-max to 5 so it breaks quicker. In production it needs to sync ~80k records though, so I don't want it to creep up to max when I'm not looking.

[Chris]

Does option 2 not result in a complaint that the connection is already in use? I have a memory that the connection can only be used by one thing at a time...

[Cylindric]

Interesting conflicting information. MSDN Documentation for DbConnection makes no mention of this, nor do the examples close.

[competent_tech]

@Chris: An open connection can be used over and over as long as you clean up the previous activity (i.e. close an open reader).

[competent_tech]

@Cylindric: Actually, dbConnection does mention this very behavior at here: msdn.microsoft.com/en-us/library/…

[Chris]

@competent_tech: Yup. That's what I thought. In the case above the parent method will still have teh reader open when calling Address.Populate. A refactor can sort that out of course but then that connection could just go back in the pool anyway if you were finished with it. It was just something I felt should be explicitly mentioned if you were suggesting this approach.

[competent_tech]

@Chris: Good point; all of our data access code is wrapped in a layer derived from enterprise library, so any open readers are automatically closed by that code, which I forget about sometimes. I have updated the answer to reflect this (attributed to you). Thanks

[Chris]

Yeah, I think this is the way to go which is why in my answer I suggested methods that return datatables and do little calculaton so the the readers are open for a minimal amount of time.

[hagello]

@comptent_tech: Close and Dispose are functionally equivalent. This does not validate your suggestion #1, but invalidates it. Right?