Misleading information in the readme about SQLite concurrency

[tantaman]

Wanted to call out that SQLite's "lack of concurrency" as it is called out in the readme:

no amount of asynchronicity will save you from SQLite3's serialized nature.

is a bit misleading. The serial nature is really a problem of the pairing of SQLite w/ JavaScript and doesn't exist in languages able to spawn threads. In languages that support threads, you can spawn many concurrent read connections and not be constrained to serial reads when in WAL mode.

This is also true for writes for those using SQLite's HCTree -- https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html

no amount of asynchronicity will save you...

You could work around this in NodeJS by spawning dedicated worker threads for long-running reads.

[m4heshd]

https://github.com/WiseLibs/better-sqlite3/blob/master/docs/threads.md

[JoshuaWise]

@tantaman, Everything you said is true, but it's debatable which explanation is more misleading.

99.9% of users aren't going to be using HCTree, given that it's just a prototype, is probably not production ready, and requires a custom build of SQLite off the main branch.

It's true that SQLite can handle concurrent reads, but the entire transaction has to be read-only, and no other operations can be done concurrently on the same database connection (or else they would be injected into the transaction). As you pointed out, you can circumvent this by using multiple read-only threads, with one database connection per thread, but that's not the model that my original comment was responding to. I was responding to comparisons with node-sqlite3, which doesn't use dedicated read-only threads, and therefore accomplishes basically nothing by using the Node.js event loop (actually, worse than nothing).

The way of using SQLite that would achieve the highest throughput would be to have a dedicated writer thread and many read-only threads, while using WAL mode. But of course, that only helps if you actually have a lot of read-only transactions; otherwise, spawning those read-only threads would be a big waste.

As @m4heshd pointed out, we do support worker threads for this exact reason. I suppose we could add more documentation on how to parallelize read-only transactions, but my guess is that if you need this, you already know how to do it.

[Prinzhorn]

Maybe you can rephrase it to make clear what asynchronicity refers to in this context. A more elegant version of the following:

-no amount of asynchronicity will save you from SQLite3's serialized nature
+no amount of asynchronicity (in the sense of giving control back to the Node.js event loop) will save you from SQLite3's serialized nature

Edit: Maybe the language of this whole paragraph could be made more friendly, with one or less commas per sentence.

[tantaman]

Thanks for the thoughtful replies. I do like @Prinzhorn's re-wording.