WAL commits are never flushed to disk: a power loss drops acknowledged commits and can leave documents pointing at freed pages #260

CommitAsync (and every auto-committing write) is acknowledged once the WAL has been handed to the OS, not once it is on disk: the WAL is never fsynced on commit. After a power loss, every commit since the database was opened (or since the last backup) is lost unless a checkpoint copied its pages to the page file. Because the pages those commits stopped using have already been freed, durably, in the page file, the documents that come back can point at overflow pages that are on the free list or have been handed out again: the result is not an older committed state but a corrupted one.

README "Transactions & ACID" (README.md:394): "Durable: WAL ensures data safety even in power loss."

Found by the WAL crash test (tests/BLite.Tests/Oracles/WalCrashTests.cs, #263), which carries the affected rows as a known candidate until this is fixed.

Repro

The test records every write the engine makes and rebuilds the files at each crash point. In crash mode PowerLoss, only WAL bytes that were fsynced survive (Flush(flushToDisk: true)); page writes are durable when PageFile.WritePage returns (each write flushes its view, msync(MS_SYNC) on Unix). Rows: PowerLoss / Durability (108 of 249 crash states), PowerLoss / Atomicity (6), PowerLoss / Pages.Reachable (6), PowerLoss / Continue (3). Shortest sequences, crash points as the test prints them:

Durability, crash after the WAL flush of step 2:
    1. Insert(id=64, count=9, code=c15, payload=2500 chars)
    2. Update(id=64, count=6, code=c06, payload=0 chars)
  the collection is the state after the first open: document 64, inserted and acknowledged
  in step 1, is missing

Atomicity and Pages.Reachable, crash after FreePage(21) of step 3:
    1. Insert(id=74, count=3, code=c08, payload=18000 chars)
    2. Backup
    3. Upsert(id=74, count=7, code=null, payload=20 chars)
  document 74 is back in its step-1 version, but its payload differs at index 3899: expected
  'llll...', got '....' (zeros); page 24, on its overflow chain, has type Free

dotnet test tests/BLite.Tests --filter "FullyQualifiedName~Oracles.WalCrashTests"

With the issue number removed from the family in s_candidates, the row fails and prints the seed, the crash point and the shortest sequence. BLITE_WALCRASH_LOG=<directory> writes every failing crash state to <directory>/<mode>.tsv.

Expected: a commit that CommitAsync has acknowledged survives a power loss, and whatever survives is a committed state.

Actual: only what the WAL held at open (or at the last backup) survives; later commits are lost, and the frees they made are kept, so documents can reference freed or reused overflow pages (the Continue row: a later insert overwrites them).

Mechanism (verified on 0450b97)

• A commit is acknowledged after await _wal.FlushAsync() (src/BLite.Core/Storage/StorageEngine.GroupCommit.cs:202; the direct path StorageEngine.Transactions.cs:445 says "Durability: ensure WAL is on disk").
• WriteAheadLog.FlushAsync calls _walStream.FlushAsync(ct) (src/BLite.Core/Transactions/WriteAheadLog.cs:491). That writes the 64 KB stream buffer to the OS and does not fsync: on .NET 6 to 10, OSFileStreamStrategy.FlushAsync returns Task.CompletedTask; only Flush(flushToDisk: true) calls FlushToDisk. The comment right below (:492-499) already says so: "FlushAsync doesn't guarantee flushToDisk ... To be safe for WAL, we might care about fsync. For now, just FlushAsync();".
• The WAL FileStream is opened without FileOptions.WriteThrough (WriteAheadLog.cs:119-126): "REMOVED FileOptions.WriteThrough for SQLite-style lazy checkpointing / Durability is ensured by explicit Flush() calls". The only Flush(flushToDisk: true) on the WAL is in BackupAsync (:1002). TruncateAsync (:1035-1037) uses FlushAsync too.
• Page writes, by contrast, are durable at once: PageFile.WritePageCore flushes its view after every write (PageFile.cs:712, :727).
• So the order on disk is wrong wherever the engine acts on a commit: the group commit frees the pages the batch stopped using right after the unsynced flush (StorageEngine.GroupCommit.cs:248, FreePagesOfCommittedTransaction), and FreePage writes the Free header and the free-list head straight to the page file when the page has no WAL image (StorageEngine.Memory.cs:105, as after the backup's truncation in the repro). The same holds for truncation: the pages it releases are freed durably right after the unsynced TruncateAsync (StorageEngine.Recovery.cs:272, :285). A power loss keeps the free and drops the commit.

The test's seed (WalCrashTests.ack-before-wal-flush.patch, which drops the flush entirely) shows the same corruption under a plain process crash: what this issue does on a power loss, the seed does on any crash.

History

• 1e9e8cb (2026-02-07) opened the WAL with FileOptions.WriteThrough ("Ensures writes go to disk immediately"), 4 KB buffer.
• f5802f9 (2026-02-08, v1.0.0) removed WriteThrough and raised the buffer to 64 KB for SQLite-style lazy checkpointing; IMPLEMENTATION_NOTES.md in that commit lists it as "RIMOSSO FileOptions.WriteThrough - Enorme guadagno di performance!" (the OS buffers WAL writes instead of syncing each one). The commit kept one fsync per commit: WriteAheadLog.Flush() was _walStream.Flush(flushToDisk: true), the notes count it as "WAL.Flush() [SYNC I/O Develop #1 - UNICO!]" and state "Durabilità garantita: commit record nel WAL è flushed prima del return". So dropping write-through traded per-write syncs for one sync per commit, not durability.
• a4e052b (2026-02-11, v1.0.0, Async Core) added WriteAheadLog.FlushAsync with _walStream.FlushAsync(ct) and the comment still at WriteAheadLog.cs:492-499 ("To be safe for WAL, we might care about fsync. For now, just FlushAsync();"). From here CommitAsync did not fsync; the synchronous commit paths still called Flush() → Flush(flushToDisk: true).
• d72b73a (2026-03-12, v3.6.2) introduced group commit: "issues a single FlushAsync per group, amortising fsync cost", with the flush estimated at "~5–30 µs", which is the cost of handing the buffer to the OS, not of an fsync.
• 63465aa (2026-03-24, v4.0.0, async-first) removed the synchronous Flush()/Truncate() with their Flush(flushToDisk: true). Since then no commit path fsyncs the WAL; BackupAsync (f3bdf4f, Complete multi-file backups with manifest and backup API metadata #95) is the only Flush(flushToDisk: true) left.

So the fsync was meant to stay (group commit was designed to amortise it), and it went missing through the async FlushAsync path. Whether to restore it on every commit is a durability/latency decision, see below.

Affected versions: CommitAsync since v1.0.0, every commit path since v4.0.0. The frees that turn a lost commit into a corrupted document are those of the current code, right after the commit (#233 / #236, 4d3e6b9) or at the next truncation (#168 / #169, e19035c), both merged after v5.2.3; before them pages were freed earlier still. Not reproduced on hardware; the test models a power loss as "only fsynced WAL bytes and msynced pages survive".

Fix considerations

This is a durability/latency trade-off for the maintainers to decide; the code today is in between (the README and the group-commit design promise sync-on-commit, the flush does not do it).

• Sync on commit (what the README describes, SQLite's synchronous=FULL): fsync the WAL once per group commit before acknowledging the batch: _walStream.Flush(flushToDisk: true) in WriteAheadLog.FlushAsync, or keep FlushAsync and follow it with a flush to disk of the handle (fsync / FlushFileBuffers). One fsync per batch, which on most storage dominates the latency of a small commit; group commit amortises it over every transaction in the batch, as d72b73a intended. The same fsync belongs in TruncateAsync before the pages a truncation releases are freed. On macOS, fsync does not flush the drive cache; SQLite uses F_FULLFSYNC there.
• A configurable synchronous mode if the lazy path should stay available: sync-on-commit as the default, and a sync-on-checkpoint mode (SQLite's synchronous=NORMAL in WAL mode) documented as possibly losing the last commits on a power loss. Even in that mode the WAL has to be fsynced before freeing pages a commit released, otherwise a power loss corrupts those documents instead of just making them older.
• The README line should then say which mode gives which guarantee.

Related: #95 / f3bdf4f (the only Flush(flushToDisk: true), in BackupAsync), #119 / #120 (checkpoint on Dispose, a clean close, not a power loss), #168 / #169 and #233 / #236 (the frees that make a lost commit a corrupted document), #261 and #262 (the other two findings of the same test). No closed issue or PR discusses commit fsync or power loss.