How ai-memory Prevents Concurrent Write Conflicts in Its SQLite Database

ai-memory eliminates SQLite write conflicts by serializing every mutating operation through a dedicated single-writer actor thread that exclusively owns the sole rusqlite::Connection capable of writes, ensuring exactly one writer exists at all times by construction.

The akitaonrails/ai-memory repository implements a robust concurrency strategy for its SQLite-backed storage layer that completely avoids the classic SQLITE_BUSY ("database is locked") errors through architectural design rather than retry logic or busy timeouts. By enforcing a single-writer invariant via an actor pattern, the system guarantees that all database mutations execute sequentially while allowing concurrent reads through separate connections in WAL mode.

The Single-Writer Actor Architecture

At the heart of ai-memory's concurrency safety lies the single-writer actor pattern implemented in crates/ai-memory-store/src/writer.rs. This design dedicates a single operating system thread to own the only database connection permitted to perform writes, preventing multiple threads from simultaneously attempting to acquire the SQLite write lock.

The module documentation explicitly states this invariant (lines 1–8):

//! Single-writer SQLite actor.
//!
//! Every mutating SQL statement flows through one dedicated OS thread that
//! owns the writer [`rusqlite::Connection`]. … This pattern eliminates the
//! `database is locked` failure mode … there is exactly one writer at all
//! times, by construction.

All write operations funnel through this exclusive thread via a multi-producer single-consumer (MPSC) channel, decoupling the async API surface from the synchronous SQLite operations.

Write Command Serialization via MPSC Channel

The WriterHandle::spawn method (lines 10–14) establishes the communication bridge between the async runtime and the dedicated writer thread:

pub(crate) fn spawn(conn: Connection) -> Self {
    let (tx, rx) = mpsc::channel(1024);
    let handle = thread::Builder::new()
        .name("ai-memory-writer".into())
        .spawn(move || worker_loop(conn, rx))
        .expect("spawn writer thread");
    // ... handle storage
}

The worker_loop function consumes the Receiver<WriteCmd> end of the channel, processing commands sequentially in a blocking loop. Because SQLite itself is serialized within this single thread, the underlying database engine never encounters concurrent write attempts that would trigger busy errors.

Public Write API and Sequential Execution

Client code interacts with the writer through the WriterHandle struct, which provides async methods like insert_observation. These methods create oneshot reply channels to await results while the actual work occurs on the dedicated thread (lines 27–36):

pub async fn insert_observation(&self, obs: Sanitized<NewObservation>) -> StoreResult<ObservationId> {
    let (tx, rx) = oneshot::channel();
    self.send(WriteCmd::InsertObservation {
        obs: obs.into_inner(),
        reply: tx,
    })
    .await?;
    rx.await.map_err(|_| StoreError::WriterClosed)?
}

Every WriteCmd variant carries the operation data plus a reply: oneshot::Sender<T> for returning results. This mechanism ensures FIFO ordering of writes—operations execute in the exact sequence they were submitted, providing natural transaction serialization without explicit locking primitives.

Concurrent Reads with Separate Connections

While the single-writer thread handles all mutations, read operations bypass this channel entirely. The system maintains separate database connections for queries, configured with SQLite's WAL (Write-Ahead Logging) mode to permit concurrent readers. The crates/ai-memory-store/src/reader.rs file implements this read-only path, allowing multiple threads to query the database simultaneously without blocking on the writer or being blocked by it.

Eliminating Database Locked Errors by Design

The architectural decision documented in docs/ARCHITECTURE.md and implemented in writer.rs explicitly targets the elimination of SQLITE_BUSY errors. As the source comment notes, because there is exactly one writer at all times by construction, the traditional SQLite concurrency hazard of multiple processes or threads attempting to acquire the write lock simultaneously cannot occur. The actual SQL execution (defined in crates/ai-memory-store/src/ops.rs) runs within transactions on this exclusive connection, ensuring atomicity without contention.

Practical Implementation Example

The following pattern demonstrates how client code utilizes the single-writer safety:

use ai_memory_store::{WriterHandle, StoreResult, Sanitized};
use ai_memory_core::NewObservation;

// Initialize the writer (typically handled by Store initialization)
let conn = rusqlite::Connection::open("memory.db")?;
let writer = WriterHandle::spawn(conn);

// Async write operation - safely serialized behind the scenes
async fn record_observation(
    writer: WriterHandle, 
    data: NewObservation
) -> StoreResult<ObservationId> {
    // Sanitization happens before sending to writer
    let clean = Sanitized::new(data);
    // This call sends WriteCmd::InsertObservation via MPSC and awaits the oneshot reply
    writer.insert_observation(clean).await
}

All mutations—whether inserting observations, purging projects, or compacting data—follow this identical path through the WriterHandle, ensuring atomic, serialized execution regardless of how many async tasks initiate writes concurrently.

Summary

• Single-threaded writer: The writer.rs module dedicates one OS thread to own the sole write-capable rusqlite::Connection, preventing lock contention by design.
• MPSC channel serialization: All writes traverse an async channel (WriteCmd enum) to reach the writer thread, guaranteeing FIFO processing of commands.
• Oneshot reply pattern: Async callers await results through oneshot channels while blocking SQLite work happens exclusively on the dedicated thread.
• No lock contention: By construction, exactly one writer exists, eliminating SQLITE_BUSY ("database is locked") errors without retry loops or busy timeouts.
• Concurrent reads: Separate reader connections in reader.rs utilizing WAL mode allow parallel queries without interfering with the write stream.

Frequently Asked Questions

How does ai-memory handle multiple async tasks trying to write simultaneously?

All async tasks send their write requests via the WriterHandle to a single MPSC channel. The dedicated writer thread processes these requests sequentially in its worker_loop, ensuring that SQLite never receives concurrent write commands regardless of how many async tasks initiate operations.

What happens if the writer thread panics or crashes?

The WriterHandle stores a JoinHandle for the spawned thread. If the writer thread panics, subsequent attempts to send commands via WriterHandle::send will return a StoreError::WriterClosed error, propagating the failure to callers awaiting their oneshot receivers.

Why not use SQLite's built-in busy timeout or retry logic instead?

The akitaonrails/ai-memory repository explicitly avoids reliance on SQLite's busy handling by enforcing a single-writer invariant at the application level. As documented in writer.rs lines 1–8, this architectural choice "eliminates the database is locked failure mode" entirely rather than managing it through timeouts or retries, providing deterministic behavior under high concurrency.

Can readers block the writer thread in this architecture?

No. The system uses WAL (Write-Ahead Logging) mode for SQLite, which allows readers to operate from separate connections without acquiring locks that would block the writer. The reader.rs module manages these independent connections, ensuring the single-writer thread never stalls waiting for read operations to complete.

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