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

> Discover how ai-memory prevents SQLite locking issues. Learn about its single-writer actor for mutations and unlimited concurrent reads using WAL mode and a connection pool.

- Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory)
- Tags: internals
- Published: 2026-08-31

---

**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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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:

```rust
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:

```rust
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:

```rust
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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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.