SQLite Forum

VACUUM resets journal_mode wal2 to delete
Login

VACUUM resets journal_mode wal2 to delete

(1) By Wolfgang Oertl (w.oertl) on 2021-04-08 21:36:15 [source]

I noticed an unexpected behaviour when vacuum'ing a db in wal2 mode. Assume that the file cmds.sql contains:

PRAGMA journal_mode;
PRAGMA journal_mode=wal2;
VACUUM;
PRAGMA journal_mode;

When feeding the following statements to the sqlite3 shell for an on-disk database (can be empty), then exiting the shell and repeating, this is the output:

$ sqlite3 test.db < cmds.sql
delete
wal2
wal2
$ sqlite3 test.db < cmds.sql
delete
wal2
wal2

So after VACUUM the journal_mode remains wal2 at first, but after exiting the shell it is in delete mode. This doesn't happen with wal mode.

If two SQLite shell processes access the same database file simultaneously, the reset to delete seems only to happen after both (all) processes have exited and the journal files have been committed and removed.

Shouldn't the journal_mode be unchanged? Using SQLite 3.35.3, begin-concurrent-pnu-wal2 branch.