A Case for Asynchronous Non Blocking JDBC — Part Two

Background

This is a three part series

  1. Part One — Click on the link
  2. Part Two —This page
  3. Part Three —Early 2021 Update

In Part one we went through non blocking calls and event based systems

  1. We will try and understand Java NIO at a high level
  2. We will explain the importance of cursors/connections in a database
  3. We will make a case for non blocking JDBC

Java IO NIO and NIO2

  1. Java IO had a thread per Connection per connection model, not very scalable
  2. Java NIO came out in Java 4. It introduced a concept of selectors and buffers, which changed the model to one thread being able to handle many connections. More scalable than Java IO, with fewer threads
  3. Java NIO2 came out in Java 7. Now we have aspects like “Completion Handlers” which is a nice way to do asynchronous non blocking operations

Java has been thinking about asynchronous non blocking operations for a long time(Since Java4)

However for the JDBC, we had a myth perpetuated for a long time.

“Because JDBC is ACID, we cannot have asynchronous non-blocking JDBC”

See Appendix-1 for Java IO vs Java NIO

See Appendix-2 for Java NIO2 with Completion Handlers

See Appendix-3 for ACID

Database Cursors/Connections

When Java came up with the JDBC standard, we quickly realized making JDBC connections and closing them, for every operation was expensive and time consuming

Thus came the concept of JDBC connection pools. Most Java Servers now come with JDBC connection pools as a standard install.

See Appendix-4 to read up more on this.

Each connection represents certain processes, memory and other resources on the database side.

What i mean is a JDBC connection costs some resources(money) on the server side.

In the Oracle database these are called “Cursors”. Read up on these in Appendix-5

These are not infinite resources, and we could run out of cursors. Read up on these in Appendix-6

Hypothetical scenario

We have developers who write a JDBC application. All works fine in development.

The application goes for performance test. And then the DBA’s see that database resources used by the application gets expensive as the load increases.

Developers blame the DBA’s for not enough resources. DBA’s blame the developers for not coding the application right, and not understanding that the database does not have unlimited resources.

See Appendix-7 for a nice real world performance test simulation.

Similar Blocking IO issue ?

So do have a similar problem with blocking IO ? Did we create NIO and NIO2 to assist with not blocking threads and hence reduce, the amount of resources used ? Yes we did and some of it was as long ago as Java 4.

Non Blocking JDBC

Community effort

David Morten(See Appendix-8) is one person who works on a project hosted at Github where he is actively working at non-blocking JDBC connection pools.

I am quoting from the projects Github page(See Appendix-8) as is below:

“Blocking a thread is a resource issue as each blocked thread holds onto ~0.5MB of stack and may incur context switch and memory-access delays (adds latency to thread processing) when being switched to. For example 100 blocked threads hold onto ~50MB of memory (outside of java heap).”

Oracle initiatives

Oracle the company which owns the Oracle Database and Java brands has been also working on a Asynchronous JDBC solution. It is an a package calle “java.sql2" .

See Appendix-9 for details

I have had a look at the latest JDK 11, could not yet find the “java .sql2” package. I guess we do not have a fixed release date yet.

Appendices

Appendix-3 — ACID databases

Appendix-4 — JDBC Connection pools

Appendix-5 — Cursors

Appendix-6 — Max cursors exceeded

Appendix-7 — Performance degrades as connections increase

Appendix-8 — Non Blocking JDBC connection pools

Appendix-9 — Non Blocking JDBC Oracle

Principal Architect at Mastercard