Write-ahead logging

A commit has to survive the machine losing power a millisecond later. The journal is what makes that true: changes are recorded somewhere durable before the database file is touched, so a database opened after a crash can be put back into a state that makes sense.

Two journals ship, and either is chosen with one word.

Write-ahead logging

The default. Every change is appended to app.witdb-wal next to the database, and a commit is durable once that append has reached the disk. The database file itself is written later.

The order is what buys the durability. The log is written first, and it is written sequentially, which a disk handles far better than the scattered page updates the same work would otherwise require. A commit therefore costs one sequential append and a sync, rather than a walk over every page the transaction touched.

Entries carry a CRC32. During replay each one is checked, and recovery stops at the first entry that fails, since a torn write at the end of the log is exactly what a crash leaves behind.

A replay that stopped early is reported, and the log is not truncated. What the replay did manage is flushed and the failure is raised, deliberately without a checkpoint: truncating would destroy the records behind the damage along with any chance of recovering them another way. This used to be silent, and one damaged record took every committed transaction behind it.

Rollback journal

The other direction. Before a page is modified, its original contents are saved to a file of its own, one per transaction, named after the transaction id. A commit deletes the file. A crash leaves it behind, and the next open uses it to undo the incomplete work.

A journal that cannot be applied does not stop the database opening, which is deliberate: one unreadable file must not lock you out of the rest of your data. What happens instead is that the file is kept rather than deleted, and the failure is reported.

Data Source=app.witdb;Journal=rollback

It writes less to disk over a transaction's life and keeps no growing log, which suits a single-user application that writes rarely. WAL is the better default for anything with concurrent readers.

Recovery

Recovery runs when the database opens, without being asked.

If the database is encrypted the journal is encrypted with it, and recovery needs the password like everything else.

Ask what recovery could not do. An open database reports the journals it failed to apply and why, and the files it named are still on disk. Nothing logs this, since the core has no logging dependency, so read it after opening if you want to know:

foreach (var failure in store.RecoveryFailures)
    logger.LogWarning("Could not apply {File}: {Reason}", failure.Path, failure.Reason);

An empty list is the ordinary case.

Once a statement has returned, its writes survive. A statement that has not returned is a different matter, and limitations covers why.

Checkpoints

A checkpoint folds the log into the database file and truncates it. It happens automatically once the log has grown by a megabyte since the last one, so a long-running application does not accumulate one indefinitely.

The rollback journal has nothing to checkpoint: a journal that was applied is already gone, and one that was not is being kept on purpose.

Call it yourself before copying the database, since without it the copy is missing whatever was still in the log. See file format for the whole procedure.

Sync writes

Sync Writes=true is the default and means a commit waits for the operating system to confirm the write reached the disk.

Turning it off makes commits faster and moves the moment of durability to somewhere you cannot predict: the data is in the OS cache, and whether it survives depends on when the OS gets around to flushing. A process crash is still survivable. A power cut is not.

That trade is reasonable for a test suite and for a cache. It is not reasonable for anything a user would be upset to lose. And it is a smaller lever than it looks: syncing costs very little on either storage engine, so turning it off is rarely where a slow write is coming from.

Writing your own

The journal is a provider like any other layer, so a journal that does something the built-in two do not is a class rather than a fork.

public interface ITransactionJournal : IProvider, IDisposable
{
    void BeginTransaction(long transactionId);
    void LogPut(long transactionId, ReadOnlySpan<byte> key,
                ReadOnlySpan<byte> value, ReadOnlySpan<byte> oldValue);
    void LogDelete(long transactionId, ReadOnlySpan<byte> key, ReadOnlySpan<byte> oldValue);
    void CommitTransaction(long transactionId);
    void RollbackTransaction(long transactionId);

    void Sync();
    int Recover(IKeyValueStore store);
    void Checkpoint();
}

oldValue is passed to both logging methods because an undo-style journal needs it. WAL ignores it, since replaying forward only needs the new value.

The obvious thing to build here is durability that outlives the machine. A journal that mirrors commits to another host, acknowledging only once enough replicas have the record, gives you survivable writes without changing a line of the application above it:

public sealed class ReplicatedJournal : ITransactionJournal
{
    private readonly ITransactionJournal m_local = new WalTransactionJournal("app.witdb-wal");
    private readonly IReplicationClient[] m_replicas;
    private readonly int m_requiredAcks;

    public string ProviderKey => "replicated-wal";

    public void CommitTransaction(long transactionId)
    {
        m_local.CommitTransaction(transactionId);

        var acks = Replicate("COMMIT", transactionId);

        if (acks < m_requiredAcks)
            throw new InvalidOperationException(
                $"Commit replicated to {acks} of {m_requiredAcks} required replicas.");
    }

    // ... the rest delegates to m_local and replicates alongside
}

Register it and name it:

ProviderRegistry.Instance.Register<ITransactionJournal>(
    "replicated-wal",
    p => new ReplicatedJournal(p.GetRequired<string[]>("replicas")));

See architecture for how registration works.

Where to go next

• Transactions, what a commit means
• Architecture, the provider registry
• File format, the log file beside the database
• Connection strings, Journal and Sync Writes