Threads get stuck in MySqlCommand.ExecuteNonQuery()

Question

I want to make the calls to a method parallel. In this method I calculate some value (very fast) and write the value of this calculation into a database table.

Therefore I use a Threadpool

ThreadPool.QueueUserWorkItem((state) => {
               method();
            });

After a while of calculation (not deterministic, the time when this happens varies, sometimes after a few minutes, sometimes after some hours) the Threadpool is full, but not with the thread of method() but with the thread of my mysql class that calls MySqlCommand.ExecuteNonQuery.

Does anyone have an idea how to avoid this? In the documentation of the mysql classes I found out that you cannot terminate an executing query but how to avoid such problems?

Answer 1

Do you need to write all values to the DB once they are calculated, or can you wait till you have some, and do a batch update where you insert a lot of them?

You could be running into a locking issue here, if all threads try to write to the same table at the same time. So try to store the numbers locally and then submit them in one big batch