Why do major DB vendors not provide truly asynchronous APIs?

Question

I work with Oracle and Mysql, and I struggle to understand why the APIs are not written such that I can issue a call, go away and do something else, and then come back and pick it up later eg NIO - I am forced to dedicate a thread to waiting for data. It seems that the SQL interfaces are the only place where sync IO is still forced, which means tying up a thread waiting for the DB.

Can anybody explain the reasons for this? Is there something fundamental that makes this difficult?

It would be great to be able to use 1-2 threads to manage my DB query issue and result fetch, rather than use worker threads to retrieve data.

I do note that there are two experimental attempts (eg: adbcj) at implementing an async API but none seem to be ready for Production use.

Answer 1

Database servers should be able to handle thousands of clients. To provide an asyncronous interface, the DB server will need to keep the resultset from the query in memory, so you can pick it up at later stage. It will quickly become out of resources.

Answer 2

A considerable problem with async is many many libraries use threadlocal for transactions.

For example in Java Much of the JDBC specification relies on a synchronous behavior to achieve single thread per-transaction. That is you write your transaction in procedural order.

To do it right transactions would have to be done through callback but they are not. I know of only node.js that does this but its unclear if its really async.

Of course even if you do async I'm not sure if it will really improve performance as the database itself if is probably doing it synchronous.

There are lots of ways to avoid thread over-population in (Java): Is asynchronous jdbc call possible?

Personally to get around this issue I use a Message Bus like RabbitMQ.