How ai-memory Handles Concurrent Reads and Writes to Its SQLite Database

The akitaonrails/ai-memory project eliminates SQLite locking conflicts by funneling all mutations through a single-writer actor while serving unlimited concurrent reads via a connection pool backed by Write-Ahead Logging (WAL) mode.

The akitaonrails/ai-memory crate stores all structured data in a single SQLite file and must support async workloads without triggering "database is locked" errors. To achieve safe concurrent reads and writes, the codebase splits database access into two distinct paths: a dedicated writer thread for mutations and a pooled reader infrastructure for queries.

Single-Writer Actor Pattern

All mutating SQL statements—INSERT, UPDATE, DELETE, and schema changes—flow through a single OS thread that owns the sole write-capable rusqlite::Connection. This architecture lives in crates/ai-memory-store/src/writer.rs.

The WriterHandle struct provides the public API. When spawned via WriterHandle::spawn(conn), it creates a background thread (named ai-memory-writer) running an mpsc channel loop. Callers submit work by sending a WriteCmd variant over the async channel and awaiting the result through an embedded oneshot reply.

Because there is exactly one writer at any time, SQLite never encounters writer collisions. The worker_loop inside writer.rs processes commands sequentially, executing each mutation inside a transaction to guarantee atomicity.

Read-Only Connection Pool with WAL Mode

Concurrent reads are handled by ReaderPool in crates/ai-memory-store/src/reader.rs. This structure maintains a collection of read-only rusqlite::Connection instances protected by a parking_lot::Mutex. When a read operation is required, ReaderPool::with_conn checks out a connection, runs the query on a Tokio blocking thread, and returns the connection to the pool.

The SQLite database operates in WAL (Write-Ahead Logging) mode, activated by executing pragma journal_mode = WAL; during store initialization in crates/ai-memory-store/src/ops.rs. In WAL mode, the writer appends changes to a separate WAL file while readers continue to read stable database pages without blocking. The pool simply bounds the number of open file descriptors, but any number of readers can execute concurrently alongside the single writer.

WAL Configuration Details

During Store initialization (ai_memory_store::Store::new), the connection is opened with flags OpenFlags::SQLITE_OPEN_READ_WRITE | OpenFlags::SQLITE_OPEN_CREATE | OpenFlags::SQLITE_OPEN_URI followed by the WAL pragma. This configuration allows the single writer to commit transactions while readers access the last committed state without interference.

Practical Implementation Examples

Submitting a Write Operation

To insert or update data, clone the WriterHandle and await the operation:

let writer: WriterHandle = store.writer_handle.clone();
let new_page = NewPage { /* fields omitted */ };
let page_id = writer.upsert_page(new_page).await?;
println!("Inserted page id: {}", page_id);

The upsert_page method internally constructs a WriteCmd, sends it across the mpsc channel (self.inner.tx), and returns the oneshot result.

Performing a Concurrent Read

Search operations use the reader pool without blocking the async runtime:

let reader = store.reader_pool.clone();
let hits = reader
    .with_conn(|conn| ops::search_pages(conn, "memory", 10))
    .await?;
for hit in hits {
    println!("{} – {}", hit.path, hit.title);
}

The closure executes on a blocking thread, preventing long-running queries from starving the Tokio scheduler.

Parallel Read Workloads

Because readers do not contend with the writer or each other, you can safely spawn many concurrent searches:

let reader = store.reader_pool.clone();
let queries = vec!["ai", "memory", "search"];
let futures = queries.into_iter().map(|q| {
    let r = reader.clone();
    async move {
        r.with_conn(|c| ops::search_pages(c, q, 5)).await
    }
});
let results = futures::future::join_all(futures).await;

Summary

• Single-Writer Serialization: All mutations route through WriterHandle in crates/ai-memory-store/src/writer.rs, ensuring one write at a time and eliminating lock contention.
• WAL-Mode Concurrency: SQLite's Write-Ahead Logging allows ReaderPool in crates/ai-memory-store/src/reader.rs to serve unlimited parallel reads while the writer commits transactions.
• Thread-Safe Abstraction: The parking_lot::Mutex guarding the reader pool and the mpsc/oneshot protocol for the writer provide async-safe APIs without exposing locking internals.
• Atomic Transactions: The writer's worker_loop wraps each WriteCmd in a transaction, guaranteeing that readers never observe partial updates.

Frequently Asked Questions

What prevents "database is locked" errors in ai-memory?

By construction, there is exactly one thread capable of writing. The WriterHandle owns the sole write-capable connection, serializing all INSERT, UPDATE, and DELETE operations through its mpsc channel. Because SQLite never encounters multiple writers competing for the same journal, the "database is locked" error cannot occur.

How does WAL mode improve read performance?

When journal_mode is set to WAL, the writer appends changes to a separate file rather than overwriting database pages. Readers acquire read locks on the last committed version of the database, allowing them to proceed without blocking while the writer appends new data. This enables the ReaderPool to checkout connections and execute queries concurrently with active writes.

Can multiple async tasks write to the database simultaneously?

No. While multiple tasks can invoke methods like upsert_page or insert_observation concurrently, the WriterHandle queues these requests in its mpsc channel and processes them sequentially in the dedicated writer thread. This serialization ensures data integrity but means writes do not scale horizontally; only reads do.

Where is the reader pool size configured?

The ReaderPool is initialized via ReaderPool::new(db_path, soft_cap) in crates/ai-memory-store/src/reader.rs. The soft_cap parameter bounds the number of cached connections, though the pool can grow temporarily under load. Each connection is returned to the pool after the query completes, ready for reuse by other async tasks.

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