I'm trying to implement database connection pooling. For some reason, I randomly get exceptions when I use multiple threads.
When I only have one, everything is fine. Also at least when I am testing, the issues appear only in the beginning. As I'm not sure what causes them, I can't really be sure they won't happen later on as well.
Using c3p0 version 0.9.5.2
Example output from my test: https://pastebin.com/NrcR6mhz
This test has 3 threads running simple query for 1000 times each. I cut the output shorter, as there are no exceptions later on.
Error messages for tl;dr version:
I won't bother uploading the whole project, as DB is configured separately. However, you can see the code of all of my classes here:
Main - https://pastebin.com/1Rgjr8H5
Pool conf https://pastebin.com/wtbw1X5v
Controller https://pastebin.com/BvPh0guY
As the c3p0 seems decent library imho, I would guess I'm doing something wrong here. Any tips please?
So, you need to think a bit about how multithreaded coding works, why one often should prefer objects accessible only in local scopes to instance or static member variables, and when one should synchronize for safe access of objects that must be shared between Threads.
Your most basic issue is the fact that MariaController.connection is a static member variable, when it should be entirely local to isPlayerSuspended(...).
You also ought to mark the static MariaConnectionPool.getInstance() method synchronized [ which is equivalent to surrounding the body of the cod with synchronized( MariaConnectionPool.class ){ ... } ].
There may be other issues as well. Writing multithreaded code requires that you think carefully about what can be shared and when, and often the answer is to aoid sharing mutable state by working with it in objects accessible only via local references within methods (access to which is inherently single-threaded).