Akka: how to make non-blocking JDBC queries

Question

I'm completely new to Akka ( Java lib ), and I'm trying to figure out if Akka can be used to make non-blocking JDBC requests, and if so, what it looks like. I believe that most JDBC drivers open a socket connection and block the thread that created it until a specific JDBC response is received, and therefore maybe not so much Akka can help here, but I wonder if there is a way ( perhaps through Futures or Agents?) That Akka can help improve performance and allow the actor system to continue processing data while an existing JDBC call is made and is waiting for a response.

I found this article which is a bit vague / cryptic, but it looks like futures may be key here. However, this article does not really show any meaningful (real) code examples, and therefore Im still at a loss. So let's say that we have a stored procedure, sp_make_expensive_calculation , which usually takes 10-30 seconds to return a response, and this is usually called via JDBC as follows:

 String makeExpensiveCalculationSql = "{call sp_make_expensive_calculation(?)}"; callableStatement = dbConnection.prepareCall(makeExpensiveCalculationSql); callableStatement.setInt(1, 10); // Could take up to 30 seconds to complete. callableStatement.executeUpdate(); int answer = callableStatement.getString(2); 

Can Akka do anything so that the acting system can continue to process data (and even make other calls to sp_make_expensive_calculation ) while we wait for the first call to return?

Answer 1

The general scheme is to use separate execution contexts : one for accessing the synchronous database via JDBC, one for reactive processing. Also see Akka futures documentation .

When you create an actor system, it creates its own execution context - this is the one you use for your usual reactive processing with actors. You need to create a second execution context for JDBC calls. You will then pass this execution context to the future factory, as shown here in the Akka documentation .

To be notified of the completion of the future, you can (optionally) use the pipeline design (also shown in the previous link, but in the previous section of the documentation). The effect of the pipe design is to accept the return value of the future, the type of which relates to the parameter of the future general type (for example, the result of your request) and send it to the specified mailbox of the participant.

The code executed by the future should not change or even read any mutable data belonging to the initiator (or any actor, for that matter). You will need to mark the result of the future so that when it arrives in the actor’s mailbox, the actor will be able to associate it with the original JDBC request. Finally, your actor will ultimately receive the result, and you can continue to process it (in accordance with the Akka delivery guarantee with a maximum delivery time).

Note that you do not need to use two execution contexts - one will work, but it will be dangerous that your database queries will consume all available threads in the execution context.

Answer 2

You are correct that the JDBC connection will block until the request completes, but if you wrap it in an asynchronous processor, you can allow it to complete and continue the main thread.

One way to grant the requestor access to the result of a JDBC query is to provide a callback that is called when the asynch database process is complete.