CODEWITHSUNDEEP
c#mysqlforeachparallel-processingmariadb
Andrew Kilburn
Andrew Kilburn

Reputation: 2251

C# Parallel.ForEach opening up too many connections from the pool

The full error received is this:

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

So I have a parallel foreach:

      Parallel.ForEach(fields, item =>
        {
            item.SysInvoiceID = _destinationRepo.GetMySQLSystemID(item);
        });

Which calls a method which looks like this:

   public int GetMySQLSystemID(CustomFieldBase GenericCustomField)
        {
            CustomField customField = GenericCustomField as CustomField;
            int sys_InvoiceID = 0;
            using (MySqlConnection mySQLConnection = new MySqlConnection("Server=web01;Database=wmp;User Name=root;Password=TotallyMyPWord"))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.CommandText = $@"SELECT Sys_InvoiceID FROM tblsys_naturalkey_lu WHERE CompanyCode = '{customField.CompanyCode}' AND InvoiceNo = '{customField.InvoiceNo}'
                                            AND LineItemNo = '{customField.LineItemNo}' AND FiscalYear = '{customField.FiscalYear}'";
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = mySQLConnection;

                    mySQLConnection.Open();

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            sys_InvoiceID = (int)reader["Sys_InvoiceID"];
                        }
                    }
                    mySQLConnection.Close();
                }
            }
            return sys_InvoiceID;
        }

When looking at the MySQL Session manager I can see that the Parallel.ForEach keeps adding connections until I had my limit.

I'm closing my MySQLConnection though, why does it keep creating connections until the max limit?

Upvotes: 1

Views: 4563

Answers (2)

Miloš Đošović
Miloš Đošović

Reputation: 188

You can use SqlConnection.ClearPool method, but still I would suggest to reuse you connection if this is something that must be done through parallel loop. As others mentioned you shouldn't be doing this.

For example, open connection before starting parallel loop. Then dispose after everything is done.

Upvotes: 0

Matthew Watson
Matthew Watson

Reputation: 109557

You haven't put any limit on the number of parallel operations created by Parallel.ForEach(), so it will be creating an unbounded number of SQL connections.

You can fix this by specifying the maximum degree of parallelism:

Parallel.ForEach(fields, new ParallelOptions { MaxDegreeOfParallelism = 8 }, item =>
{
    item.SysInvoiceID = _destinationRepo.GetMySQLSystemID(item);
});

However (as others have pointed out), you shouldn't be using Parallel.ForEach() for IO-bound work in any case.

Upvotes: 2

Related Questions