Unit-testing – Testing thread safety

You can add another column that tracks whether or not a particular row is 'checked out' by your application. This might be a good place for a stored proc. Other threads would then select the first row where 'bit' is false and 'checked_out' is false.

Another option is to dedicate a single thread to pulling the id's and distributing them to worker threads.

It sounds like you might be implementing a queue, in which case you may also want to research the various queue/messaging systems.

Further options...

If you know you'll always have a fixed number of threads, you can add a mod condition to your query. eg, "where (bit = false) and (id % 4 = 0)". This runs the risk of missing items if one of the threads stops or falls behind.

Yet another thing to look at is perhaps consider only doing your work in a single thread. Your MySQL server will only handle so much, so you should profile to see if you are actually getting more done with multiple threads. So if the threads are just doing basic db read & writes, there may not be an advantage. On the other hand, if what you are doing is processor intensive, something like image processing, the threads will likely help.

You can't (realistically) prove thread safety exhaustively for a language like Java, but there are ways to detect some bugs.

The official behaviour of your Java program when run on several threads is defined by Chapter 17 of the JLS. The only way to prove that a program is thread safe is by applying all those rules in a theoretical way, which in most scenarios is too complex.

There are a few static analysis tools that can help, such as FindBugs, but they will only cover the most obvious errors.

There are some stress testing tools such as jcstress that will run your code many times until they get confident that they behave as expected - but some concurrency bugs will only appear on specific hardware / JVMs. For example the memory model of x86 CPUs is fairly strong and will never display concurrency bugs that would show up on weaker memory models.

Similarly, some JVMs will optimise your code more aggressively then others and will trigger more bugs by doing so (for example: variable hosting).

In the end, a reasonable path is a combination of:

• in-depth understanding of the memory model to avoid as many pitfalls as possible at design time
• a good testing framework (e.g. jcstress) that you run on various computers (mixing OSes and CPUs) to hopefully spot any remaining issues