How the Single-Writer SQLite Actor in ai-memory Prevents Write Races

The ai-memory crate eliminates write races by forcing all mutations through a dedicated writer task that owns the sole SQLite connection, using an async mpsc channel to serialize commands and ensure only one transaction executes at a time.

The ai-memory repository provides a high-performance embedded storage layer for AI applications built on SQLite. To solve the classic concurrency problem of multiple threads writing to an embedded database simultaneously, the project implements a single-writer SQLite actor that centralizes all mutations through a dedicated thread. This architectural pattern guarantees ACID compliance while preventing the "database is locked" errors and race conditions common in multi-threaded SQLite deployments.

Dedicated Writer Thread and Command Channel

The WriterHandle Implementation

In crates/ai-memory-store/src/writer.rs, the store initializes a WriterHandle that spawns a single writer task on its own thread. This task owns the exclusive SQLite connection, ensuring no other code path can acquire a write lock on the database file.

Asynchronous Command Queuing

Instead of accessing the database directly, client methods in crates/ai-memory-store/src/lib.rs—such as store::write_page and store::apply_batch—package SQL statements into command structs and push them onto an async mpsc channel. The writer thread dequeues these commands sequentially, eliminating concurrent write access. Each command includes a one-shot responder channel that the writer uses to return results to the caller.

How Serialization Prevents Write Races

Exclusive Access Guarantees

Because the single-writer actor holds the only mutable reference to the SQLite connection, the database file is never subject to concurrent write attempts. This design removes the need for SQLite's busy-handler retries and prevents race conditions where interleaved writes could corrupt indices or leave partial records.

Atomic Multi-Table Transactions

The writer executes each command within a single transaction. As noted in docs/ARCHITECTURE.md, this enforces the invariant that indexes commit in the same transaction as the data. When updating FTS5 indices or embedding vectors in crates/ai-memory-store/src/ops.rs, the changes persist atomically with their source records, ensuring consistency even if the system crashes mid-write.

Performance Characteristics of the Single-Writer Model

While writes serialize through one thread, the async runtime (tokio) allows the writer to manage many concurrent request futures without blocking. The channel-based API decouples callers from I/O latency—client code awaits a future while the writer batches operations where possible. This approach avoids SQLite's file-locking overhead and maintains high throughput for write-heavy workloads.

Practical Usage Example

use ai_memory_store::{Store, WritePage};

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    // Initialise the store (creates the writer thread internally)
    let store = Store::new().await?;

    // Prepare a page write request
    let page = WritePage {
        path: "notes/todo.md".into(),
        content: "# TODO\n- [ ] Write article".into(),

        ..Default::default()
    };

    // The call returns immediately; the actual SQLite write is
    // performed by the single‑writer actor on its thread.
    store.write_page(page).await?;

    Ok(())
}

In this example, store.write_page does not touch the database directly. Instead, it marshals the request into a command, sends it via the writer's mpsc channel, and awaits the responder. The writer thread receives the command, opens a transaction, updates the pages table and associated FTS5 indices via functions in crates/ai-memory-store/src/ops.rs, commits atomically, and signals completion.

Summary

• The single-writer SQLite actor in ai-memory centralizes all mutations through a WriterHandle spawned in crates/ai-memory-store/src/writer.rs.
• Commands flow through an async mpsc channel, ensuring only one write executes at a time.
• The writer uses one-shot responder channels to return results without blocking client async tasks.
• Atomic transactions guarantee that indexes and data commit together, preventing partial updates.
• This architecture eliminates SQLite file-lock contention and prevents write races by design.

Frequently Asked Questions

What happens if the writer thread crashes?

If the writer task panics or encounters an unrecoverable error, the WriterHandle drops, closing the mpsc channel. Subsequent write calls in crates/ai-memory-store/src/lib.rs will return an error indicating the writer is unavailable, preventing undefined behavior or silent failures.

Can read operations also use the single-writer actor?

Read operations typically execute through separate database connections to avoid blocking the writer. The single-writer constraint applies strictly to mutations; reads can occur concurrently on other threads while the writer processes transactions, maximizing read throughput without compromising write safety.

How does the channel-based API handle backpressure?

The mpsc channel has a bounded buffer; when full, callers attempting to enqueue commands (such as those calling store::apply_batch) will await capacity. This natural backpressure prevents memory exhaustion under heavy write loads while maintaining the serialization guarantees of the single-writer actor.

Does this pattern work with multiple processes?

No, the single-writer SQLite actor prevents races only within a single process. Multiple processes accessing the same SQLite file would still rely on SQLite's file-locking mechanisms. The ai-memory crate is designed for single-process deployment where the writer thread owns the exclusive connection.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}