How do parallel queries work when SQLite doesn't support them?

[ColonelThirtyTwo]

I'm a beginner at Node.js, but I'm familiar with SQLite.

SQLite has a synchronous interface; you call sqlite3_step(stmt), it blocks and fetches one result, then returns. There's no way to asynchronously fetch results, short of running all SQL queries on another thread, and one connection can only run one query at a time.

But node-sqlite3 claims that it can run queries in parallel, which shouldn't be possible with SQLite's API. This leads me to believe that one of the following is happening:

• Queries aren't actually processed in parallel, but are simply queued up and eventually executed. Of course, if you're going to execute queries one-at-a-time, you may as well preserve the order. If that's the case, serialize has literally no purpose.

  From looking at the source code and issue db.parallelize() needs to run queries in parallel #394, this seems the most likely.

• Database isn't one connection, but a pool of several connections, each with their own worker thread. But this leads to other issues; temporary tables and certain PRAGMA settings are per-connection, and each query would have to wait for the main thread to call the callback after each row or statement.

Maybe there is a different technique for running SQLite queries in parallel that I can't think of, but to me, this is a major deign smell, and I'm skeptical about using this library until this issue is explained and documented.

[JohnLonginotto]

One month after submitting #394, im pretty sure this is going to be a real pain to 'fix' this because the only way around it is to create child processes which connect to the database separately. But then you have availability/concurrency questions and the whole problem kind of balloons out until you basically end up with a 'regular' non-file database like Postgres or MySQL - where some kind of server process manages the pool of client connections and makes sure transactions happen properly and in the right order.
So it's kind of a bummer because SQLite is fast and it is simple and this library is easy to use - but at the end of the day, it really doesn't serve much purpose because you can only run 1 query at a time, so as you say - what's the point in db.serialize()?

Im currently trying to figure out parallel queries in postgres (again, to see if its possible - they offer a client pool, but after looking at the documentation it seems to be a pool of 1!) so if that works i'll let you know :)

[ColonelThirtyTwo]

@JohnLonginotto You can still run queries in parallel; you just need one database per connection to do so; for example, creating a Database on every request (which is probably a better idea anyway; otherwise, one request might execute queries in another requests's transaction). Connection pooling is mostly so that connections can be re-used across requests, reducing the amount of connections needing to be initiated (though with SQLite, this is less of an issue, since SQLite deals with files and not network connections).

Though in the case of a DB connection per request and a single threaded server, I'm curious as to what will happen if a new request tries to access the DB while another request holds the writer lock...

[ColonelThirtyTwo]

I think I realized the purpose of db.serialize.

Let's say two requests come in at roughly the same time, r1 and r2, and you have a single database connection db.

r1 goes first, and executes db.exec("BEGIN TRANSACTION;", some_cb_function). node-sqlite3 then yields, and node decides to execute r2. r2 then runs db.exec("INSERT whatever");, but db has a transaction open that belongs to r1, so the transaction is no longer isolated. db.serialize ensures that all the queries ran in the callback are ran one after the other, so it's less that node-sqlite3 runs queries "in parallel" and more that it runs them "interspersed".

But this hinges on the fact that the whole node server has only one connection. A better solution would be to give each request its own connection (possibly pooling them for reuse) and let SQLite handle the locking (as it can do a better job; read transactions won't block each other, and in WAL mode, writers won't block readers and vice versa). If sqlite3_prepare or sqlite3_step return SQLITE_BUSY, then the database is locked and node-sqlite3 should yield to node's event manager to continue on with the requests that are locking the database.

[ColonelThirtyTwo]

Actually, that can't be true either, except in callbacks which db.serialize doesn't affect, because r1 must run to completion...

[wmertens]

The way I understand it, the only way db.parallelize can work is by using a connection pool. I'm sad @springmeyer didn't respond to this issue…

Frankly, https://github.com/JoshuaWise/better-sqlite3 is a better approach but that one simply blocks on all queries, which is too much of a good thing. That's a wontfix for them, too… WiseLibs/better-sqlite3#32