SQLite WAL with the C# Entity Framework

Question

For some reason my hosting only seems to work updating my SQLite database when it is set in WAL mode. So using "DB Browser for SQLite" I edited the Pragma Journal Mode to WAL and everything seems to work.

My database is now 1Mb but the accompanying WAL file is 4Mb. Is this expected? I've been trying to read up on the Pragma options (https://www.sqlite.org/pragma.html#pragma_journal_mode) but it seems over my head at the moment.

The WAL file is apparently used before a "Checkpoint"

Using Entity I do the following:

public DbSet<ProductModel> Products { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlite(@"Filename=" + dbPath);
} 

using (ProductDB db = new ProductDB())
{
    db.Products.Update(testModel);

    try
    {
         db.SaveChanges();
    }
}

Is there something extra I should be doing to commit any changes and reset a checkpoint? Will my WAL file keep growing, or is it always relative to the database filesize?

Thanks

Answer 1

Is this expected?

Yes. WAL basically works the opposite way to Journal mode storing changes yet to be applied to the database file.

That is with WAL the changes are not written to the database file, but are written to the -wal file. When check-pointing (a roll forward) occurs then the changes are then written to the database file and effectively removed from the -wal file (but not necessarily freeing the disk space).

The -wal is effectively part of the database i.e. data will be extracted from the -wal file before the underlying database is accessed (if the data was not in the -wal file).

If a roll-back occurs then the -wal file can (in-principle) be dropped/deleted and the roll-back is complete.

Whilst in JOURNAL mode the changes are written to the database file and logged in the -journal file. A roll-back undoes the the changes.

Is there something extra I should be doing to commit any changes and reset a checkpoint?

Closing the database should checkpoint, you can also force check-pointing by using PRAGMA schema.wal_checkpoint otherwise autocheckpointing applies (aprox after 1000 pages).

Will my WAL file keep growing, or is it always relative to the database filesize?

No it will not keep growing, but it will until checkpoint happens. It's not relative to the database size but relative to the changes (transactions) applied. The size is dependant upon the database's page size as the -wal fil stores changed pages. So a smaller page size may result in a smaller -wal size, but there may then be more pages to be stored and a greater amount of disk access.