IDBBatchAtomicVFS and Write-Ahead Logging (WAL)

[rhashimoto]

This is a sketch of an idea that is very similar to Write-Ahead Logging, which is an alternative to the default rollback journal that can provide better performance. No wa-sqlite example VFS currently supports WAL and this idea is technically not a way to implement WAL, but at a high level the approach is very similar and would provide similar benefits.

From the SQLite description of WAL:

The original content is preserved in the database file and the changes are appended into a separate WAL file. A COMMIT occurs when a special record indicating a commit is appended to the WAL. Thus a COMMIT can happen without ever writing to the original database, which allows readers to continue operating from the original unaltered database while changes are simultaneously being committed into the WAL. Multiple transactions can be appended to the end of a single WAL file.

This is actually not very different from the way that IDBBatchAtomicVFS already works. This VFS uses a versioned page store where there can be multiple versions of each page in the database file. Each write transaction on the database will write new versions of some of the pages, leaving the old versions (temporarily) in place. A COMMIT occurs when the version counter for the database is updated to match the transaction version, which lets all other connections "see" all the updated pages in an atomic and consistent manner.

So IDBBatchAtomicVFS doesn't use a WAL file, but it does work essentially the same way by writing its changes and making them globally visible in an atomic manner. A reader could continue to read its version of the database while a writer is writing a new version - in fact, the reader can continue while multiple new versions are committed simply by not updating its version counter. In some respects, this is actually a more elegant implementation of the WAL file concept than using a WAL file.

IDBBatchAtomicVFS already provides one performance advantage that WAL does: no rollback journal, which reduces I/O and requires fewer fsyncs. It doesn't yet provide WAL's other performance advantage: allowing read transactions concurrent with a (single) write transaction. Can it do that as well? I think probably yes, but this is where things get vague and hand-wavy.

Two things would need to be done to get concurrent reads and writes to work at all, and both seem relatively straightforward:

• Change the locking logic to permit read transactions to overlap with one write transaction.
• Prevent purging of old versions while any read transaction still needs them.

However, there are some tricky details that would need to be addressed. Unfortunately, SQLite doesn't always use batch atomic writes with a VFS that support them. When it doesn't then it falls back to a regular rollback journal and we lose the desired WAL-like features. That happens in these cases:

• When using batch atomic writes, SQLite actually keeps a rollback journal in its cache (in RAM). This allows a VFS that limits the size of a batch atomic write to fall back to a rollback journal...which is never for IDBBatchAtomicWriteVFS. If that unneeded rollback journal overflows the cache, SQLite will spill the journal to the file system and convert the transaction to be not batch atomic.
• When a transaction writes to multiple databases.

Here are some ideas to handle this:

• Augment the locking code to obtain an exclusive lock when a main journal file is created.
• Return an error when a main journal file is created. This would mean that applications could not write to multiple databases in a single transaction and would have to configure a sufficient cache size (i.e. with PRAGMA cache_size). An ugly but easy and pragmatic solution (note that WAL itself doesn't support atomic write to multiple databases).
• Use the WAL mechanism (which doesn't require that in-cache journal) instead of the batch atomic mechanism. This means that the application would enable WAL but behind the scenes the VFS would intercept writes to the WAL file and do its versioned block writes instead. Probably the most complicated solution, but maybe also the most performant.

I have no immediate plans to try this myself, so just a thought experiment for now (and an invitation for anyone else to give it a shot). I'm not sure how many in-browser applications would have the multiple connection contention to really benefit from it.