JDBC calls wrapped in Scala Future

Question

I am writing an Akka HTTP Web API, that connects to an Oracle Database instance using OJDBC.

To my knowledge there is no asynchronous JDBC API to connect to the database, nor there is a callback implementation, hence, a process thread must be blocked in order to complete the call.

My question is: having that Akka HTTP naturally allows handling request with Scala Future, is it a good idea to simply wrap the database call into a Scala Future? Does the underlying thread idle while waiting for the database response?

Kuhl

Answer 1

Does the underlying thread idle while waiting for the database response? Yes, the thread is blocked until JDBC call finishes. It's not a good thing, but until adba is ready there is probably no better option.

It is a common pattern to use Future for blocking IO like JDBC calls. There are some things to consider though. There's a great article on that topic on github.

Some points to sum up things described in the article:

• wrap your blocking calls inside blocking block, like that:

  def fetchUser(id: Long): Future[User]  = Future {
     blocking { //mark this operation as blocking
        ...
        preparedStatement.execute()
        ...
     }
  } 
  

• you shouldn't use scala.concurrent.ExecutionContext.Implicits.global for futures that do any blocking, because you might starve thread pool. You should rather create a separate thread pool for your blocking operations:

  object BlockingIOExecutionContext {
      implicit val ec: ExecutionContextExecutor = ExecutionContext.fromExecutor(
         Executors.newCachedThreadPool()
      ) // create seperate thread pool for our blocking operations
  }
  

The best option for you would be just to use some kind of mature Scala frameworks, that do these things for you, like slick or doobie.

Huppert

Thank you very much for the answer! I have just one question regarding the starving of the thread pool. In Akka, the ExecutionContext instance is given by the Actor system, something like this: implicit val system: ActorSystem = ActorSystem("my-system") implicit val ec: ExecutionContextExecutor = system.dispatcher The same advice for the ExecutionContext.Implicits.global is true for the actor system execution context? – Kuhl

Check out this article. It describes how to use the separate dispatcher for blocking operations. – Menjivar

You don't need a separate dispatcher. Just configure your default dispatcher to have unlimited threads (or whatever limit you expect to be reasonable). – Antimagnetic

You could do that, but it's just better practice for having separate pools CPU and IO tasks. – Menjivar

Maybe so ... but where are the cpu tasks here? If there are different kinds of requests, with different performance characteristics, it may be beneficial to have them served by separate pools, yes. But absent that ... there is just no point for complexity. – Antimagnetic

For example, slick does use a separate thread pool, but it's hidden under the hood. Basically, every high abstraction concurrency framework (like zio, cats-io, JavaRx, Monix etc.) has some mechanism for switching thread pool whether you do blocking IO or CPU task. But of course, decision, if it's worth doing probably depends on your use case. – Menjivar

For example article about zio from John de Goes: "As a result, well-designed applications actually have at least two thread pools: 1. One thread pool, designed for asynchronous code, has a fixed number of threads, usually equal to the number of cores on the CPU. 2. Another thread pool, designed for blocking code, has a dynamic number of threads (more threads will be added to the pool when necessary), which is inefficient (but what can you do!)." – Menjivar

I thought that using blocking in a Future prevents starving the thread pool, as it causes the underlying executor to spawn more threads. See docs.scala-lang.org/overviews/core/futures.html: "The number of concurrently blocking computations can exceed the parallelism level only if each blocking call is wrapped inside a blocking call ... Otherwise, there is a risk that the thread pool ... is starved" – Schatz

I guess downside here is, that if you have thread pool fixed for 8 thread and there are 8 JDBC calls coming first, and then CPU-heavy tasks come, they would need to wait until calls finish to start work. Another way around it would work without problems, since using blocking would increase threads over the limit. – Menjivar