LockingMode Exclusive for a shared readonly database
(1) By anonymous on 2021-11-17 11:20:39 [link] [source]
I would like to know, if there is an issue, if the locking_mode is set to exclusive on a shared readonly database?
I have a large sqlite3 file, which is used by multiple docker container. The sqlite3 database is used only for reads (SELECT).
Can I set all the connections to use locking_mode exclusive so that all connections have better performance, as I dont use any write operation? Or could the connections block each other while reading because they assume that there could be a writelock be acquired by another process?
(2) By anonymous on 2021-11-17 12:00:06 in reply to 1 [link] [source]
I don't have the exact answer for your question, I am assuming it is a No though, but as an alternative you may open the database in the nolock mode. Since it is only reads.
(3) By Simon Slavin (slavin) on 2021-11-17 15:16:26 in reply to 1 [source]
Just make each of the connections open the database in read-only mode. SQLite will figure out behind the scenes what this means for locking.
Because Docker is a bit unpredictable to do with levels of caching, I would test out real lifelike situations using WAL mode and classic journalling mode, and see which is fastest. I do not mean that you should write special testing code, or simulate your data using special random data. I mean run your actual software on actual data and see whether there's any noticeable difference in time taken. There probably won't be until your data files get big.
(4) By Keith Medcalf (kmedcalf) on 2021-11-17 19:03:33 in reply to 1 [link] [source]
You would be better served by opening the file as "immutable", assuming that it is, in fact, immutable (this effectively means readonly and nolock) during the time it is open (that is, nothing will change the file, as if it were on WORM media that has been finalized (ie, a closed CD, for example)).
This is a good thing since Docker does not implement the locking semantics necessary for reliable concurrency -- and an "immutable" file does not require any locking.