Using a MySQL Database with C# in a multi-threaded application

A few days ago I was searching the internet for a solution to a problem, that was discussed often, but with no really working solution.

Basically I worked on a small Visual C# program to update a certain table within a certain MySQL database. As there are millions of rows to select and update I decided creating a multithread solution to work on several rows simultanously. I had no problem working on different rows „on the same time“ since the updates were indepentend of other rows. I created my multithread program with 5 threads, each of them fetching, „recalculating“ and updating a row by given IDs of pre-fetched rows. I.e. the program fetches about 10.000 IDs by random, divides them into slices of 2.000 and send them to the 5 threads. The threads taking these IDs, fetch other rows out of the databaes according to the specific ID given and write back some stuff to those rows after calculating. If all 10.000 IDs are worked out, another 10.000 were fetched – until all needed calculations are done.

After some smaller problems concerning different concurring database connections (ADO.net is managing connections for you which I found out then – simply take a „using“ directive as i.e. shown here), I still had problems because after some period of time (sometimes 3 minites, sometimes 5, sometimes even half an hour) there seemed to be no more update done to the database (checked with MySQL Administrator and „Server Status“ -> Queries). While running the program managed to take an avarage amount of 150 queries per second. But „stopping“ meant this rate went down to 2 queries, both seemed to be the „checking“ queries of MySQL Administrator or whatever since they had even been there without my program running.

Nevertheless there was no interuption from debugger nor any kind of error showing and even the program window was still showing. The only evedinces that there were no more working on the database by my program was the missing queries and a select count from a view which would increase during the workout did not increase anymore. But what to do? With having no error message, no program failure or anything else you can’t say why ore where things were stopping. I began to search for solutions regarding C#, MySQL and multithreading environments but nothing really worked.

Then I got an idea: As I had once a similar problem with a chatserver written in PHP. Ther problem there was that PHP checked it’s queue to fast and so the whole server was busy – too busy to take in anything on the queue and thats why the chatserver fails to work. Maybe it was something similar here? What if MySQL was to busy with 150 queries in avarage per second and shuts down connections to my program in order to stay „save“ for itself?

So I tested various ideas about artificically slow down my program. I used in „internal“ boolean value indicating one of the threads was busy on the database as well as I played around with Thread.sleep(); or Thread.SpinWait(); in order to force the threads to wait for some time. It didn’t really help although my program seemed to work „longer“ without the failure. Then I started to set breakpoints and debug the program – as far as this is possible within such a multithreaded program structure. And suddenly the program ran and ran and ran. So I analyzed where my breakpoint was when the program worked fine: It was within the function for updating the database.

What was the problem and how to solve it?

The problem was that MySQL itself manages its database connections as multiple threads and with having a „standard“ installation takes advantage of table locks. Since my programs threads even worked independently from each other and ADO.Net managed only the providing of various connections to the database server but not the execution of the update statements, I guess that there were simply 2 threads firing their updaes at nearly the same time, both requesting table locks and both getting the table lock. So none of both threads were able to write to the datbase but non of both could either drop the table locks. But how to keep an eye on who’s going to write to the databse and telling the other threads to wait as long as needed?

Simply take a lock around those areas in your code, that uses updates on your database. This maybe include only one or two lines of code but exclusively locks all other threads from doing their stuff at the same time. Using a lock solved my problem and my program works fine for hours and even days without stoppng.