Achilleas>I know, but we still risk having our max_connections exceeded.
You should limit the work at thread pool level, not at connection pool level.
That is you should not accept new tasks for execution if they might block on a "getConnection" call.
Imagine a case:
1) Thread A starts a transaction, it calls the DB
2) Thread B starts processing another request, it locks some Java lock and calls .getConnection. Unfortunately, there's not enough connections, sot it just waits.
3) Thread A completes the DB call, it continues and tries to take the same Java lock.
Now we have a deadlock: A is waiting for the Java lock and it holds a DB connection (with uncommitted transaction), while B is holding a Java lock and it is trying to get a connection.
That is, it is not connection pool's task to limit the number of concurrent requests.
Achilleas>And this is not scaleable.
Can you elaborate?
Vladimir