Concurrent database calls

[amitmurthy]

https://github.com/wankdanker/node-odbc the ODBC interface for Node, uses separate threads for database calls in parallel. Since Julia too uses libuv, it makes sense to incorporate, or at least try out, the same strategy for parallel DB calls..

Creating an issue here, in case someone feels like taking it up.

[amitmurthy]

Maybe worthwhile to see if @threadcall - https://github.com/JuliaLang/julia/blob/master/base/threadcall.jl - can be used here for concurrent ODBC calls.

[quinnj]

Hey @amitmurthy, do separate threads have the ability to do parallel IO? I'm wondering about the case where I want to do concurrent queries and stream the results to disk.

[amitmurthy]

As long as we are not using the libuv API in the separate thread, there should be no issue. In this case since I/O is happening from the ODBC library call it should be perfectly OK.

We will have to be careful of not using ODBC handles across threads concurrently though.

[StefanKarpinski]

This is basically the use-case for @threadcall: doing blocking C calls without blocking Julia.

[quinnj]

I'm just worried about cases where you're streaming results to disk. You think you're doing "concurrent" database calls, which technically you are, but once you start writing the results to disk, things get locked up.

[quinnj]

Ok, looked into this (finally!). I couldn't find a way that made thing work reliably. @threadcall doesn't support stdcall which is needed on windows; so we could just not support this on windows, but then running the test suite, I kept getting random hangs on OSX. I then tried just doing fetch(Threads.@spawn ccall(..., but even that produces some weird hangs when running the test suite.

[StefanKarpinski]

Does ODBC have a non-blocking API?

[quinnj]

It looks like the spec does allow for async operations, so here's a PR that gives it a whirl: #269

[quinnj]

It's a little unsatisfying because it depends on if the individual drivers actually implement the async abilities; as opposed to a @threadcall situation which would work no matter the driver.

[quinnj]

Though on the other hand, in our new 1.3+ world, it's pretty easy for users to do Threads.@spawn DBInterface.execute(conn, sql) themselves, so maybe this isn't as pressing an issue ¯_(ツ)_/¯

[StefanKarpinski]

That's not guaranteed to run on a separate thread, so it may still block the entire application. Perhaps this is fine until we have a more general way to run potentially blocking calls on a separate thread. This is certainly not the only package that wants such a capability. Many GUI toolkits want the same ability as well.