Is asynchronous jdbc call possible?

Question

I wonder if there is a way to make asynchronous calls to a database?

For instance, imagine that I've a big request that take a very long time to process, I want to send the request and receive a notification when the request will return a value (by passing a Listener/callback or something). I don't want to block waiting for the database to answer.

I don't consider that using a pool of threads is a solution because it doesn't scale, in the case of heavy concurrent requests this will spawn a very large number of threads.

We are facing this kind of problem with network servers and we have found solutions by using select/poll/epoll system call to avoid having one thread per connection. I'm just wondering how to have a similar feature with database request?

Note: I'm aware that using a FixedThreadPool may be a good work-around, but I'm surprised that nobody has developed a system really asynchronous (without the usage of extra thread).

** Update **
Because of the lack of real practical solutions, I decided to create a library (part of finagle) myself: finagle-mysql. It basically decodes/decodes mysql request/response, and use Finagle/Netty under the hood. It scales extremely well even with huge number of connections.

Answer 1

There is no direct support in JDBC but you have multiple options like MDB, Executors from Java 5.

"I don't consider that using a pool of threads is a solution because it doesn't scale, in the case of heavy concurrent requests this will spawn a very large number of threads."

I am curious why would a bounded pool of threads is not going to scale? It is a pool not thread-per-request to spawn a thread per each request. I have been using this for quite sometime on a heavy load webapp and we have not seen any issues so far.

Answer 2

An old question, but some more information. It is not possible to have JDBC issue asynchronous requests to the database itself, unless a vendor provides an extension to JDBC and a wrapper to handle JDBC with. That said, it is possible to wrap JDBC itself with a processing queue, and to implement logic that can process off the queue on one or more separate connections. One advantage of this for some types of calls is that the logic, if under heavy enough load, could convert the calls into JDBC batches for processing, which can speed up the logic significantly. This is most useful for calls where data is being inserted, and the actual result need only be logged if there is an error. A great example of this is if inserts are being performed to log user activity. The application won't care if the call completes immediately or a few seconds from now.

As a side note, one product on the market provides a policy driven approach to allowing asynchronous calls like those I described to be made asynchronously (http://www.heimdalldata.com/). Disclaimer: I am co-founder of this company. It allows regular expressions to be applied to data transformation requests such as insert/update/deletes for any JDBC data source, and will automatically batch them together for processing. When used with MySQL and the rewriteBatchedStatements option (MySQL and JDBC with rewriteBatchedStatements=true) this can significantly lower overall load on the database.

Answer 3

A solution is being developed to make reactive connectivity possible with standard relational databases.

People wanting to scale while retaining usage of relational databases are cut off from reactive programming due to existing standards based on blocking I/O. R2DBC specifies a new API that allows reactive code that work efficiently with relational databases.

R2DBC is a specification designed from the ground up for reactive programming with SQL databases defining a non-blocking SPI for database driver implementors and client library authors. R2DBC drivers implement fully the database wire protocol on top of a non-blocking I/O layer.

R2DBC's WebSite

R2DBC's GitHub

Feature Matrix

Answer 4

You have three options in my opinion:

1. Use a concurrent queue to distribute messages across a small and fixed number of threads. So if you have 1000 connections you will have 4 threads, not 1000 threads.
2. Do the database access on another node (i.e. another process or machine) and have your database client make asynchronous network calls to that node.
3. Implement a true distributed system through asynchronous messages. For that you will need an messaging queue such as CoralMQ or Tibco.

Diclaimer: I am one of the developers of CoralMQ.

Answer 5

The commons-dbutils library has support for an AsyncQueryRunner which you provide an ExecutorService to and it returns a Future. Worth checking out as it's simple to use and ensure you won't leak resources.

Answer 6

If you are interested in asynchronous database APIs for Java you should know that there is a new initiative to come up with a set of standard APIs based on CompletableFuture and lambdas. There is also an implementation of these APIs over JDBC which can be used to practice these APIs: https://github.com/oracle/oracle-db-examples/tree/master/java/AoJ The JavaDoc is mentioned in the README of the github project.