Connect Timeout expired. All pooled connections are in use.

shengyan cheng 0 Reputation points
2023-04-24T08:14:30.9966667+00:00

Hi everyone

Generally speaking, we can use the keyword [using] to release connection resources, and add try-catch to dobule ensure that the connection is released.

public async Task Execute()
{
            using var cn = new MySqlConnector.MySqlConnection(_options.Value.Worker.DB);
            try
            {
                cn.Open();
                cn.Query("sp_demo", commandType: CommandType.StoredProcedure);

            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (cn.State != ConnectionState.Open)
                    cn.Close();

            }
}

But when I call the async method by multi thread, [Connect Timeout expired. All pooled connections are in use.] will appear when the method has an exception


Task.Factory.StartNew(async () =>
            {
                await _db.Execute();
            }, TaskCreationOptions.LongRunning);

This is because the connection did not release as expected and reached the Max Pool value. (Maybe it's too late to release?) In addition to increasing the Max Pool value, how to properly release resources effectively under multi-threading? Thanks

NET6 Console App

Developer technologies | C#
{count} votes

1 answer

Sort by: Most helpful
  1. Anonymous
    2023-04-25T07:03:23.7966667+00:00

    Hi @shengyan cheng ,welcome to Microsoft Q&A. Read about the use of using: when declared in a declaration, a local variable is released at the end of the scope in which it was declared. So you don't need to manually close resources in finally.

    finally
    {
        if (cn.State != ConnectionState.Open)
            cn.Close();
    }
    

    The syntax introduced since C# 8.0 should correctly use async and await when using asynchronous statements. You can modify your code like this:

    public async Task ExecuteAsync()
    {
        await using var cn = new MySqlConnection(_options.Value.Worker.DB);
        try
        {
            await cn.OpenAsync();
            await cn.QueryAsync("sp_demo", commandType: CommandType.StoredProcedure);
        }
        catch (Exception ex)
        {
            // handle exception
        }
    }
    

    Using await on cn.OpenAsync() ensures completion before proceeding to the next line of code.

    For Task.Factory.StartNew and Task.run. After carefully reading this article What is the difference between Task.Run() and Task.Factory.StartNew(). (In your scenario, I prefer to use Task.run.)

    For the code in your comment: you could put the connection into the whole statement below, which can properly release resources under multithreading.

    for (int i = 0; i <= xxxx; i++)
    {
        // others condition
    
        Task.Factory.StartNew(async () =>
        {
            using var cn = new MySqlConnection(_options.Value.Worker.DB);
            await cn.OpenAsync();
    
            try
            {
                await _db.ExecuteAsync(cn);
            }
            catch (Exception ex)
            {
                // handle exception
            }
        }, TaskCreationOptions.LongRunning).Unwrap();
    }
    

    If using Task.Run:

    public async Task MainAsync(){
    for (int i = 0; i < xxxx; i++)
    {
        await Task.Run(async () =>
        {
            using var cn = new MySqlConnection(_options.Value.Worker.DB);
            await cn.OpenAsync();
            try
            {
                await _db.ExecuteAsync(cn);
            }
            catch (Exception ex)
            {
                // handle exception
            }
        });
    }
    }
    

    Of course this depends on your own actual scenario.

    Best Regards,

    Jiale


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.