# How ai-memory Prevents Concurrent Write Conflicts in Its SQLite Database > Learn how ai-memory prevents SQLite write conflicts. Discover its single-writer actor thread strategy for safe concurrent database operations. - Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory) - Tags: internals - Published: 2026-09-06 --- **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`](https://github.com/akitaonrails/ai-memory/blob/main/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): ```rust //! 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: ```rust 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` 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): ```rust pub async fn insert_observation(&self, obs: Sanitized) -> StoreResult { 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` 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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/docs/ARCHITECTURE.md) and implemented in [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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: ```rust 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 { // 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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/reader.rs) module manages these independent connections, ensuring the single-writer thread never stalls waiting for read operations to complete.