SQLite Forum


2 forum posts by user ronburk

16:18 Reply: single writer, multiple dirty readers (artifact: 8be4091cf4 user: ronburk)

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.

08:54 Post: single writer, multiple dirty readers (artifact: 9e7159f02d user: ronburk)

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?