Search code examples
c#mysqlconnectionpooling

Need help managing MySql connections

I'm having trouble finding a clear explanation of connection pooling. I'm building an app using the .NET connector I downloaded from mysql.com. The app only needs one db connection but will be running simultaneously on about 6 machines on my network. Normally, I'd create the connection at startup and just leave it. But I'm seeing lots of posts from people who say that's bad practice. Also I'm concerned about timeouts. My app will run 24/7 and there may be extended periods without database activity.

I'm leaning toward the following:

using (MySqlConnection conn = new MySqlConnection(connStr))
{
    conn.Open();
    // use connection
}

But I'm not sure I understand what's going on in the background. Is this actually closing the connection and allowing gc to kill the object, or is there a built in pooling behavior that preserves the object and redelivers it the next time I try to create one?

I certainly don't want the app reauthenticating across the network every time I hit the database.

Can anyone offer me some advise?

Solution

  • Its customary of the .net providers to use connection pooling in that scenario.

    The connection should be just returning to the pool at the end of your using.

    I haven't looked under the hook with a mysql profiler, but I have code in place that depends on it - haven't had trouble with it.

    Update: I just looked at the calls done during dispose and it definitely does connection pooling i.e. it ends up calling:

    internal void CloseFully()
{
    if (this.settings.Pooling && this.driver.IsOpen)
    {
        if ((this.driver.ServerStatus & ServerStatusFlags.InTransaction) != 0)
        {
            new MySqlTransaction(this, IsolationLevel.Unspecified).Rollback();
        }
        MySqlPoolManager.ReleaseConnection(this.driver);
    }
    else
    {
        this.driver.Close();
    }
    this.driver = null;
}

    Update 2 / answering the comment: the MySqlConnection instance is a different one, as the using statement just deals with disposing (freeing of resources). Because of that, you don't need to check if its closed. The MySqlConnection internally uses other classes / instances, which is were it grabs the approppiate instance. That's transparent to your code, so you use it just like if it were a new+different connection / just like in the code you posted.

    Like you said, in order to be able to reuse the lower level connection (called Driver in the mysql connector code), each pool is determined by the connection string.