A Case for Asynchronous Non Blocking JDBC — Part Two
Background
This is a three part series
- Part One — Click on the link
- Part Two —This page
- Part Three —Early 2021 Update
In Part one we went through non blocking calls and event based systems
Sections on this page
- We will try and understand Java NIO at a high level
- We will explain the importance of cursors/connections in a database
- We will make a case for non blocking JDBC
Java IO NIO and NIO2
Key aspects
- Java IO had a thread per Connection per connection model, not very scalable
- 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
- 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
Conclusion
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”
Further Reading
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
JDBC connections and pools
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.
Resources on Database Server side per connection
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
Database Administrator’s nightmare
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-1 — Java IO vs Java NIO
Appendix-2 — Java NIO2 with Completion Handlers
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