Possible to checkpoint a WAL file during a transaction?

Question

We are performing quite large transactions on a SQLite database that is causing the WAL file to grow extremely large. (Sometimes up to 1GB for large transactions.) Is there a way to checkpoint the WAL file while in the middle of a transaction? When I try calling sqlite3_wal_checkpoint() or executing the WAL checkpoint PRAGMA statement, both return SQLITE_BUSY.

Answer 1

Not really. This is whole point of transactions: WAL (or journal file) keeps data that would become official once successfully committed. Until that happens, if anything goes wrong - program crash, computer reboot, etc, WAL or journal file allow to safely rollback (undo) uncommitted action. Moving only part of this uncommitted transaction would defeat the purpose.

Note that SQLite documentations defines check-pointing as moving the WAL file transactions back into the database. In other words, checkpointing moves one or more transactions from WAL, but not part of huge uncommitted transaction.

There are few possible solutions to your problem:

• Avoid huge transactions - commit in smaller chunks if you can. Of course, this is not always possible, depending on your application.
• Use old journaling mode with PRAGMA journal_mode=DELETE. It is slightly slower than new WAL mode (with PRAGMA journal_mode=WAL), but in my experience it tends to create much smaller journal files, and they get deleted when transaction successfully commits. For example, Android 4.x is still using old journaling mode - it tends to work faster on flash and does not create huge temporary or journal files.