Optimizing select with transaction under SQLite 3

Question

I read that wrapping a lot of SELECT into BEGIN TRANSACTION/COMMIT was an interesting optimization.

But are these commands really necessary if I use "PRAGMA journal_mode = OFF" before? (Which, if I remember, disables the log and obviously the transaction system too.)

Answer 1

Note that I don't agree with BigMacAttack.

For SQLITE, wrapping SELECTs in a Transaction does do something: It reduces the number of SHARED locks that are obtained and then dropped.

Reference: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg79839.html

So I think the transaction would also be beneficial even if you had journal_mode turned off, because there is still the locking overhead to consider.

Maybe read_uncommitted would be something you could consider - I would guess that it would disable the SHARED locking.