On 17 Feb 2016, at 2:48pm, Dave Baggett <dmb at inky.com> wrote: > Regarding transactions, I'm bundling write operations into transactions, but > not optimally. If, say, I do a huge write as a single transaction, will that > cause SQLite to keep everything in memory until I COMMIT? Perhaps that's the > right strategy for me to pursue.
A lot of operations on the database file are done at the beginning and end of every transaction. If your journal is in memory, then you can dramatically disk usage by using large transactions. So I think you are right and you should check out that strategy. I've forgotten whether you already said which journal mode you are using or not, but there is a big difference in journal usage between WAL mode and the original mode. Benchmarking between these two modes would be another good test for you. Unfortunately I have no idea which option would be better for you but there are people here with more experience of these differences than I have and I hope some might help. > If there is a document (or even section of the SQLite source) that I could > read to fully understand where the transition from memory (page cache, etc.) > to disk occurs, that would probably get me above n00b level of understanding, > which would help. I don't think it's gathered together at one point. For traditional mode you might find section 4 of <https://www.sqlite.org/lockingv3.html#rollback> useful. Sorry but it mixes information at the level you want with information far more details than you need. The WAL information is gathered and <https://www.sqlite.org/wal.html> explains a lot about how WAL mode uses the journal file. Simon.
