downstairs: use EXCLUSIVE sqlite locking_mode ? (research required) #774
Description
A natural follow-on from #771 is whether it makes sense to set the DB's locking_mode to EXCLUSIVE. in this mode the DB is permanently locked to a single connection, so there's no locking overhead after that. This might make sense to do right now since we only have one connection per extent at runtime. If we made this change, we might undo it in the future if we play with parallelizing the sqlite access in service of parallelizing reads within an extent.
This is a one line change to turn on, but would require updating a handful of tests that currently open concurrent connections to the extent DBs. They'd need to stop doing that. So it's actually more than one line worth of work to do.
I'm going to ignore the test suite for now and just run some perf measurements to see if it's actually worth doing or not. The nice thing is that if we want to undo this later we wouldn't need to undo the changes to the tests. I believe we'd just need to change the line of code that sets the locking_mode to put it back to NORMAL. With an asterisk, I do not fully understand the wording of this:
If the locking mode is EXCLUSIVE when first entering WAL journal mode, then the locking mode cannot be changed to NORMAL until after exiting WAL journal mode. If the locking mode is NORMAL when first entering WAL journal mode, then the locking mode can be changed between NORMAL and EXCLUSIVE and back again at any time and without needing to exit WAL journal mode
(from https://www.sqlite.org/pragma.html#toc )
Is this referring to entering/exiting WAL mode within a connection? is it referring to this across connections? I don't know. But I don't like the ambiguity. I'll see what the deal is.