Is there SQL database that supports nonblocking access? It is kinda moot to use nonblocking ktor ser...

# ktor

[Matej Drobnič]

Is there SQL database that supports nonblocking access? It is kinda moot to use nonblocking ktor server only to end up blocking on JDBC queries.

[Vlad]

MySQL 8 has non blocking way of executing queries with X DevAPI. The API is kinda bad with a lot of bugs, but with some additional code it is usable.

[Cameron Hashemi]

you can use a dedicated thread pool for blocking JDBC queries, e.g.

withContext(<http://Dispatchers.IO|Dispatchers.IO>) {}

Details and a proof-of-concept in this article: https://ryanharrison.co.uk/2018/04/14/kotlin-ktor-exposed-starter.html

[Vlad]

It is great that Exposed now has suspendedTransaction, before that for transaction management they were using thread-local variables, which was deal-breaker for me then 🙂

[Matej Drobnič]

How can Exposed suspend transaction?

[Matej Drobnič]

It still uses JDBC behind the scenes right?

[Matej Drobnič]

So it has to block at some point

[Dennis Schröder]

But ... sorry there is a but ... I used Exposed with the DAO Model and when dealing with lazy executing queries, the needed Transaction will not be found.

[Vlad]

@Matej Drobnič I am not sure that I understand what you mean by that... The suspendedTransaction function just "propagates" the transaction by keeping it in the CoroutineContext,
so it won't be "lost" when different thread resumes the execution.

[Matej Drobnič]

you can use a dedicated thread pool for blocking JDBC queries, e.g.

Right, but how is this any better than just going blocking all the way if you are already going to use a lot of blocking threads?

[Matej Drobnič]

because most web requests just hit database, so 90% of the time, request would be blocking

[Vlad]

The dedicated thread pool just gives a way to limit the number of concurrent requests to the database.

[Vlad]

If you have pool with DB connections and thread pool with appropriate number of threads, only those threads will block, the other threads are free to handle web requests.

[obobo]

I've used jasync-db on postgres, works really well.