Retrieving SQL query execution results asynchronously.

Question

After quite a bit of research I decided to go ahead and write a question that may seem fairly redundant here on stack-overflow, but I'm only asking because I've failed to find my answer. I've been having some fun with the Java NIO2 framework which has increased the scalability of my applications tremendously. (We were previously using the old-school I/O TPC server/client setup for some mildly populated emulation servers), and let me tell you. Concurrency was queen B if you know what I mean.

We're moving from using threads as an answer to everything (In an attempt to better practices and become more familiar with advancing technology). Previously we were using flat-file storage (Text/Binary format storage) for user-data and have migrated everything over to a SQL database.

While doing this I've noticed that there's usually not a problem, however waiting on the database is never fun, and in an emulated game-environment waiting for a database call is never fun, especially when the entire game-server is waiting. This is where we would usually substitute threading. Why not just execute the SQL call on another thread using a Thread pool or similar.

The Java NIO2 CompletableFuture<K,V> has really been a huge help with any type of asynchronous event, however I've been trying to find a way to use this with SQL calls. I figured writing our own version of JDBC that used NIO2 was always an option but it seems a bit over-kill and will probably take more time and money than it's worth. Remember we're just a small emulation community, not a large-scale business, but we do want to make sure to provide quality to our users.

I was looking into jooQ which seems alright, but it also seems a little over-kill. This uses the CompletableFuture#supplyAsych method however the example available don't really explain it in-depth. However it's mixed in with this quote:

there will always be one blocking barrier to such solutions, and that is JDBC itself – which is very hard to turn into an asynchronous API. In fact, few databases really support asynchronous query executions.

However, the database doesn't necessarily need to support asynchronous query executions in-order for the results to be provided in an asynchronous context. If my server isn't waiting(and only waiting) for the API to get results for the query, that's the only thing that really matters here. Similar activity to this can be explained by using an example from Unity's scripting reference: CoRoutines.

Please do not recommend Scala.

Answer 1

Java NIO2 is at too low a level to help you.

Your "thought bubble" of developing an asynchronous version of JDBC is problematic for two reasons:

• It really doesn't fit with the way that transactions are implemented above the JDBC API layer (i.e. in application code). An application that handles multiple transactions simultaneously on a smaller number of threads is going to be a lot more complex. More complex (typically) means less reliable, and that's not what you want when doing (conventional) database operations.

• Designing an asynchronous JDBC API is one thing, but implementing it is another thing entirely. The real problem here is that the real work is happening in multiple JDBC drivers:

  • Some of the drivers are proprietary.
  • All of the drivers talk some kind of non-standard "protocol" to a back-end database (either in the JVM or over a network connection).
  • Those protocols are (AFAIK) always synchronous in nature, and often proprietary.
  • Updating or replacing the protocols with something that supports asynchronous modes is going to entail changing the database implementations themselves.

So what is the solution?

Well this Q&A has some links to projects attempting to implement asynchronous database APIs: "Is asynchronous jdbc call possible?".

I guess, if you were not satisfied with that (i.e. the progress or the APIs), you could start your own project. However, you are liable to run into the same set of design and implementation problems.

The other approach may be to give up on SQL and ACID entirely, and review some of the so-called "NoSQL" databases to see if they give you the kind of asynchronous APIs and behaviour that you want.