single writer, multiple dirty readers
(1) By Ron Burk (ronburk) on 2021-07-02 08:54:41 [link] [source]
I'm making a (single-process, WAL mode) versioned database; roughly speaking each row of each table gets an extra "revision#" sub-key column, and data is basically always added, never modified or deleted. Given that I'm already paying for that overhead, it seems a shame (conceptually anyway) not to enable dirty reads since I only read rows that are never updated or deleted. If I understand correctly, a separate benefit might be keeping reads from competing with checkpointing.
After reading the docs and many posts, I think that to get dirty reads I need to use pragma read-uncommitted and
sqlite3_enable_shared_cache(), producing a process-wide shared cache. Presumably to overlap writes with dirty reads I then need at least two threads each with their own connection: a writer thread and a (dirty) reader thread.
Have I understood the architecture more or less correctly?
Pretty sure I understand that SQLite serializes writes, but I'm less clear how granular the multithreading is in SQLite internals vis a vis reads. Seems like if this article is right, adding multiple dirty reader threads should scale reasonably with the usual caveats.
OTOH, is it plausible that dirty reads are not worth the effort and will produce little in the way of increased maximum throughput?
(2) By Dan Kennedy (dan) on 2021-07-02 11:10:46 in reply to 1 [source]
Shared cache mode generally offers less concurrency than wal mode. For starters, all queries will be serialized by a single mutex belonging to the shared cache.
I might be missing something, but I don't think looking to enhance performance by allowing dirty reads really makes much sense in SQLite's single-writer/multi-reader architecture. Each reader sees a version of the database image representing the open snapshot, not a set of rows that need to be filtered to match the open snapshot based on transaction ids and whatnot.
(3) By Ron Burk (ronburk) on 2021-07-02 16:18:35 in reply to 2 [link] [source]
Thanks -- serialized reads would surely eliminate any benefit I was hoping for. I also now find this thread on much the same topic.
Maybe the correct way to think about this is that SQLite doesn't generally offer dirty reads, but shared memory mode (which is really for low-memory conditions I guess?) throws that feature in for reasons.