16
votes

I would like to access a database in a non-blocking way, in other words, I will send the SQL request through a socket and read the query response through the same socket asynchronously. That means that I only read data from the socket as it becomes available (Java NIO SocketChannel) so I never block.

I might be wrong but as far as I know JDBC drivers for databases all use blocking sockets.

Even if I have to execute a query that returns 1 million lines, I can do that asynchronously, receiving lines as they become available in the socket buffer.

My goal is to execute an SQL query without ever blocking (i.e. without ever producing latency). Using a separate thread is not an option. I need to do that inside the network thread (NIO selector thread).

Has anyone succeeded in doing this or can recommend an approach that does not involve an extra thread?

2
Why is using a separate thread not an option? It would be quite simple to perform asynchronous queries from another thread. - erickson
@erickson Using another thread is not an option because I don't have a cpu core to spare on my machine for another thread. And that would also not work if you have to do 2 queries simultaneously. The thread would be blocking waiting for the result of the first query to finish. Then you would have to put the second query in a queue or span yet another thread. Doing what I am trying to do implements concurrency in the database side, not on my side. But again, that's a topic for another question. Assume that you are in a job interview and was told that another thread is not an option :) - Richard Bradley
You are suffering from some misconceptions about threading. You wouldn't need one core for that thread that is blocked on network I/O. It is not in a runnable state, and wouldn't be considered for scheduling on any cores that are available. Most systems successfully run hundreds of threads on a few cores. And doing many asynchronous queries concurrently is not a problem either. That's why database connection pools are a thing. - erickson
@erickson You are suffering from making assumptions about a scenario without knowing anything about the scenario. I can't have 1000 threads doing context switches in few cores because my system is very latency sensitive. I rest my case. - Richard Bradley
Which is why I asked, and responded to the information provided. - erickson

2 Answers

13
votes

It's possible, but not with JDBC. Unless you want to use the raw SocketChannel interface and parse the results yourself, the only async database drivers on the JVM I'm aware of are https://github.com/mauricio/postgresql-async (which despite the name also supports MySQL). They're written in Scala but it should be possible to call them from Java (since it's a JVM language), though I can't say how Java-friendly the API will be.

0
votes

If you write your own driver, it would certainly be possible to do this for most databases.

I am not aware of any drivers that do this "out of the box," however.