HikariCP and Asynchronicity

[Brett Wooldridge]

Hi,

Over at HikariCP we're looking out a little over the horizon at adding support for asynchronous connection acquisition from the pool.  We're currently in the midst of a release (2.3.0), but possibly targeting next (or next next) release if we do indeed add support for this.

Our question is this.  From an API perspective, what is the easiest consumable way to expose asynchronicity?  Understanding that HikariCP is written in Java and not Scala, we see two possibilities.  First, an API call to DataSource.getConnection() could return a Future.  This of course requires "polling" or "waiting" for the client to obtain the Connection.  Second, an API to DataSource.getConnection() could provide a callback (implementation of a callback interface).  This would seem more truly asynchronous, being more of a "push" than a "pull".  We'd rather pick one rather than implement multiple, because that's how we roll.  Keep it simple.

Is there a third option we are overlooking?  Which one is preferable?  While HikariCP supports Java 6/7 and through a separate artifact Java 8, we are perfectly willing to limit this feature to Java 8 if taking advantage of some of the new constructs (java.util.Optional, java.util.function.Function, etc.) makes it easy/better/natural/forward-looking.

Feedback greatly appreciated.

[Nicolas Rémond]

If you are limiting this feature to Java 8, java.util.concurrent.CompletableFuture would be a good candidate in my opinion. 

Otherwise, for Java 6/7, I would go with the callback mechanism.

Best

Nicolas

[Stefan Zeiger]

On 2014-11-28 5:00, Brett Wooldridge wrote:

Over at HikariCP we're looking out a little over the horizon at adding support for asynchronous connection acquisition from the pool.  We're currently in the midst of a release (2.3.0), but possibly targeting next (or next next) release if we do indeed add support for this.

Before I get to the actual question, let's look at how this would fit into the design of Reactive Slick: There are cases where it would be useful but so far I haven't bothered implementing asynchronous Connection acquisition directly in Slick because those cases are rather specific. (Of course, it would be nice to get such a feature for free, directly from the connection pool). The basic model of Reactive Slick looks like this:

• We have a ThreadPoolExecutor with a queue (usually an ArrayQueue of limited size) at the entrance. Unlike a simpler approach where you just wrap asynchronous database calls in Future(blocking(...)), we do all contention of database resources through this executor, so that we don't spawn unnecessary threads which will just sit there blocked, waiting for a Connection from the pool. There is (ideally) no contention over actual database connections.
• This also means that we configure the connection pool in a non-traditional way. The maximum pool size can be large, equal to the number of concurrent database sessions you want to allow, provided that these sessions are idle (e.g. you start a transaction, read some data, then wait for an asynchronous web service to process the data while the session is idle, and finally update the rows you read and commit the transaction). The size that is usually recommended for the connection pool (i.e. the number of active jobs the database can run in parallel) is instead used to limit the thread pool.
• When you execute an Action (which can be composed of many individual Actions, e.g. AndThen(AndThen(CreateSchema, Insert), Query)), for each synchronous database action (in this case: CreateSchema, Insert, Query), a job is scheduled for execution on the thread pool. This job obtains a database connection, does its work, releases the connection, and completes a Future with the result. In case of the AndThen combinator, the completed Future triggers the next action to run.
• We fuse synchronous database actions at construction time, so they don't have to be cps-transformed and don't need intermediate Futures. This also means that they can share a single Connection without returning it to the pool in between and reacquiring it (or a different Connection) immediately.
• Up to this point, asynchronous getConnection calls would buy us nothing. The blocking getConnection calls run on the database thread pool, and while they do not keep the database busy (which is what all code on this thread pool should do), any other code that could do so would also have to get a Connection first. But, as mentioned above, you can have asynchronous code running within a transaction context (or just a pinned session without a transaction), and with Reactive Streams for getting streaming results directly from a ResultSet, you can have a streaming action getting suspended (due to built-up back-pressure) while the ResultSet is open. In these cases we need to hold on to an open Connection outside of running a synchronous database action, so there can be more Connections than threads  in the pool.
• This is where asynchronous connection acquisition would be useful. When you have suspended Connections without an active thread in combination with a dynamically expanding connection pool, there can be a situation where you are waiting for a new Connection from the pool, thus wasting a slot on the database thread pool and preventing another action from continuing its job with an existing Connection.

Our question is this.  From an API perspective, what is the easiest consumable way to expose asynchronicity?  Understanding that HikariCP is written in Java and not Scala, we see two possibilities.  First, an API call to DataSource.getConnection() could return a Future.  This of course requires "polling" or "waiting" for the client to obtain the Connection.  Second, an API to DataSource.getConnection() could provide a callback (implementation of a callback interface).  This would seem more truly asynchronous, being more of a "push" than a "pull".  We'd rather pick one rather than implement multiple, because that's how we roll.  Keep it simple.

I think a Java Future would be useless for this purpose. Getting a JDBC Connection is an inherently blocking operation. If you have to block (or resort to polling) on the Future, you might as well block on the getConnection call directly. I would go for a callback interface with methods onComplete(Connection) and onError(Throwable). The getConnection call should also take a Duration after which it will time out and call onError.

Is there a third option we are overlooking?  Which one is preferable?  While HikariCP supports Java 6/7 and through a separate artifact Java 8, we are perfectly willing to limit this feature to Java 8 if taking advantage of some of the new constructs (java.util.Optional, java.util.function.Function, etc.) makes it easy/better/natural/forward-looking.

If you limit the feature to Java 8, you can use CompletableFuture which allows asynchronous chaining of operations similar to scala.concurrent.Future.

--
Stefan Zeiger
Slick Tech Lead
Typesafe - Build Reactive Apps!
Twitter: @StefanZeiger

[Brett Wooldridge]

Wow, Stefan!  A detailed answer far beyond my expectations.  I too had thought about, "Well, yes asynchronous connection acquisition is nice, but what about queries and results?"  I understand these are also issues.  I consider offering asynchronous connection acquistion a first step, and there are obviously good things that can be done that not only apply to Slick and Scala but also pure Java developers who are writing in a "reactive" style.

Thank you so much for the input, I'm leaning toward a Java 8 only solution for this feature (set?), so I'll start my investigations in that direction.

Please pardon any nieve questions I may have in the future, I am ashamed to admit that I am not a Slick user at this point.

Best Regards,

Brett