Rex Liu

One writer

BetterSpotlight's first two days in C++: two processes on one SQLite file, and how long each of them held the write lock.

BetterSpotlight, the file search I'm writing for my Mac, keeps its whole index in one SQLite file, and two background processes share it. The indexer walks the disk, opens every PDF, image and document it finds, and writes down what it learns. The query service reads the index to answer searches. I split them because extraction is the part most likely to crash, and a malformed PDF should be able to take down the indexer without taking search with it.

SQLite in WAL mode suits that shape. Any number of readers can run alongside a writer, and readers don't block each other or the writer. What it won't do is let two connections write at once. A writer that finds the lock taken waits out its busy timeout and then gives up with SQLITE_BUSY.

Lock contention was the first thing I fixed in the C++ version, and it had two causes. Every open wrote to the database, and the indexer held the lock while it read documents.

I had every process run the same setup when it opened the file: the pragmas, every table and index IF NOT EXISTS, and a list of default settings, each one an INSERT OR IGNORE. On a database that already exists, all of that looks like it does nothing. Some of it needs the write lock to find that out. Setting user_version or application_id takes the lock even when the value is the same, and an INSERT OR IGNORE takes it before discovering that the row is already there. So the query service took the write lock every time it started. I split the setup. Settings that belong to a connection, like the busy timeout, run on every open. Everything that belongs to the database runs only if a read-only look at sqlite_master says the schema isn't there yet. I also raised the busy timeout from 5 seconds to 30.

I'd written the indexer's loop to open a transaction, then, for each file in a batch of a hundred, write the file's metadata, extract its text and write that, and commit at the end of the batch. SQLite takes the write lock at the first write, so from the first file's metadata onwards, the lock was held while the indexer parsed PDFs and ran OCR on the other ninety-nine. That night I halved the batch to fifty, so whenever the query service needed to write, to record what I'd done with a search result, it waited behind up to fifty files instead of a hundred.

A busy timeout didn't fully cover it either. SQLite's docs are blunt about this: "The presence of a busy handler does not guarantee that it will be invoked when there is lock contention." If waiting could deadlock, SQLite returns SQLITE_BUSY straight away. So the upsert got its own retry loop as well, five attempts with sleeps of 50 to 200 milliseconds between them.

The next evening I rebuilt the indexer in three stages:

  1. A coordinator takes file-system events off a queue.
  2. A small pool of prep workers does everything slow: stat the file, extract its text, OCR it if needed, split it into chunks. None of that touches the database. There are two or three workers while the machine is idle and one while I'm searching, and PDFs and OCR each go one at a time.
  3. One writer thread takes the prepared results and applies them in short transactions, fifty files or a quarter of a second, whichever comes first.

During normal indexing, the write lock is now held only for writes, and only the writer thread asks for it.

Running prep in parallel created a new problem. A file can change while it's being prepared. If I save a document while the indexer is still extracting the previous version, what comes back describes a file that has already changed. So every path has a generation number, and every event for the path bumps it. Only one preparation of a path runs at a time. Events that arrive meanwhile are merged into a single pending one, which starts as soon as the current preparation finishes. When prepared work reaches the writer, it's dropped if a newer event has arrived for that path since, because a fresher preparation is already on its way:

bool Pipeline::isStalePreparedWork(const PreparedWork& prepared) const
{
    const std::string path = prepared.path.toStdString();

    std::lock_guard<std::mutex> lock(m_coordMutex);
    auto it = m_pathCoordinator.find(path);
    if (it == m_pathCoordinator.end()) {
        return false;
    }

    return prepared.generation < it->second.latestGeneration;
}

It's the same move as the generation half of an actor id in libjzx. Old work is allowed to exist, as long as it can be recognised when it turns up. The manual check I wrote for it overwrites one file 150 times in a loop, deletes it, and then confirms the index agrees the file is gone.

The indexer reports its queue as a handful of counters, among them preparing, writing, coalesced and staleDropped. preparing goes up to three. writing is always 0 or 1.

By then I'd made the same mistake once more, in the query service. Earlier that evening I'd written a rebuild for its vector index. It opened an immediate transaction, which takes the write lock straight away, cleared the old vector mappings, and then computed embeddings one file at a time before committing. While it ran, the indexer couldn't write at all. I moved the embeddings out the next afternoon and left a comment above the transaction:

// Keep lock scope short: expensive embedding work happens before this transaction.

It's the rule the indexer's writer thread follows too. Everything slow happens first, and the write lock is held for the writing.