
How to Fix SQLite Database Locked Errors in High-Throughput MCP Memory Servers on Linux
Diagnose SQLite writer contention without claiming WAL eliminates locks
An MCP memory server may report sqlite3.OperationalError: database is locked when two connections contend for an incompatible database operation. SQLite WAL helps readers and a writer coexist, but only one writer can hold the write lock at a time, and WAL-mode databases can still return SQLITE_BUSY.
Technical correction, November 13, 2026: An earlier version advertised a complete FastMCP server that tried to create an asyncio worker without a running event loop, left its checkpoint task unscheduled, used an unbounded queue, and could fail when setting an already-cancelled future. It also promised zero lock conflicts and prescribed an unverified 30 GB memory map. Those commands, fixed throughput figures and guaranteed-performance claims are removed.
The official SQLite WAL documentation describes the one-writer restriction and exceptions to the usual read/write concurrency benefits. The SQLite busy timeout API allows bounded waiting for a lock but may ultimately return SQLITE_BUSY.
1. A reproducible two-writer failure using a temporary fixture
This standard-library-only example creates a tiny, disposable SQLite database in a temporary directory. It does not open any production file, MCP service or network connection:
from pathlib import Path
from tempfile import TemporaryDirectory
import sqlite3
with TemporaryDirectory(prefix="sqlite-wal-fixture-") as folder:
path = Path(folder) / "fixture.sqlite3"
first = sqlite3.connect(path, timeout=0.0, isolation_level=None)
second = sqlite3.connect(path, timeout=0.0, isolation_level=None)
try:
assert first.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"
first.execute("CREATE TABLE test(id INTEGER PRIMARY KEY, value TEXT)")
first.execute("BEGIN IMMEDIATE")
first.execute("INSERT INTO test(value) VALUES (?)", ("one",))
try:
second.execute("BEGIN IMMEDIATE")
except sqlite3.OperationalError as exc:
assert "locked" in str(exc).lower(), exc
print("expected second-writer lock observed")
else:
second.execute("ROLLBACK")
raise AssertionError("second writer acquired the lock unexpectedly")
first.execute("COMMIT")
second.execute("BEGIN IMMEDIATE")
second.execute("INSERT INTO test(value) VALUES (?)", ("two",))
second.execute("COMMIT")
assert first.execute("SELECT count(*) FROM test").fetchone()[0] == 2
print("sequential synthetic writes PASS")
finally:
first.close()
second.close()
The script is a reader-reproducible demonstration, not an independently verified runtime benchmark. On a supported local SQLite installation, it intentionally starts a write transaction before the second writer attempts to obtain a lock. A successful run should print an expected lock-detected message followed by a sequential-commit success message. The example must not be generalized to throughput or error rates. The zero wait is intentional for this demonstration, not a production recommendation. Python’s sqlite3 reference covers connection timeouts and transaction behavior.
2. Diagnose the failure stage before changing settings
| Observation | Check first | Invalid conclusion |
|---|---|---|
| Two simultaneous write transactions conflict | Duration of transaction and actual writer connection ownership | WAL mode cannot work |
| Lock error persists under WAL | Other processes, exclusive locks, recovery or checkpoint interaction | An in-process queue controls all writers on the host |
| Requests wait without completing | Configured busy timeout, transaction duration and external dependencies | A larger timeout automatically improves throughput |
| WAL file grows while readers are active | Long-lived read snapshots and checkpoint progress | A periodic passive checkpoint guarantees a 10 MB maximum |
| Writer task stops after a cancelled request | Worker exception handling, future state and queued-item ownership | Every completed queue item was committed |
The WAL reference describes default automatic checkpoints near a threshold of 1000 pages, with long-running readers potentially preventing a checkpoint from finishing. It also documents cases where even WAL readers can see busy errors. Do not infer success from the presence of files named with -wal and -shm: those files can be cleaned up when connections close.
3. Inspect effective journal mode and timeout
On an authorized local database, these are observational SQL statements:
PRAGMA journal_mode;
PRAGMA busy_timeout;
PRAGMA wal_autocheckpoint;
If WAL is suitable, an administrator can test PRAGMA journal_mode=WAL in a staging copy and verify the returned value. The shared-memory implementation described by SQLite’s WAL guide has filesystem and deployment constraints. Do not apply the setting blindly to an arbitrary network filesystem or modify shared production data without approval.
The busy-timeout documentation says SQLite waits for a configured period while a table is locked before giving up. It does not promise every write will succeed within that time. A retry policy must consider transaction rollback, whether previous operations committed, and idempotency of the tool request.
Do not paste a bundle of unrelated tuning PRAGMAs such as 30 GB mmap_size, unmeasured cache limits or a durability mode chosen for someone else’s host. Durability tradeoffs should be evaluated against the actual workload and WAL durability guidance.
4. A single-writer queue requires its own tested lifecycle
It can be useful to serialize writes routed through a single application-owned queue, including connections that its worker controls. That queue does not coordinate any independent writer that bypasses it, including another process or separately used connection. It also does not automatically make failures, shutdown or cancellation safe.
Before implementing an async SQLite writer around a specific tested SDK:
- Initialize the database and create worker tasks inside an active application event loop, not after a temporary asyncio runner has exited.
- Limit queue size and submitted observation sizes; define backpressure and rejection behavior.
- Decide whether cancellation withdraws a pending write, awaits its durable acknowledgement, or reports that the outcome is unknown.
- Never blindly call
future.set_result()after an awaiting client has cancelled its future. - Catch write errors per item, rollback if necessary and keep the worker alive or stop safely under an explicit policy.
- Stop accepting writes before shutdown; drain or explicitly fail queued items, await the worker, then close connections.
- Monitor lock errors, actual WAL checkpoints, queue size, write latency and completed versus failed operations.
A full production MCP service additionally needs a verified FastMCP SDK/API version, data boundary, authentication, deployment and integration tests. A portable, guaranteed-correct drop-in server is not claimed here.
5. Evidence required for a real improvement
For a staging A/B comparison, hold constant the SQLite library version, file system, DB file, connections, write payloads, reader patterns and offered load. Record successful/failed writes, duplicate operations, lock waits, latency, queue length and checkpoint state. Only make one configuration change per controlled test. Successful operation of this tiny temporary fixture does not prove production load handling.
Verification boundary: This article provides a source-backed, executable synthetic example, not an independently verified runtime receipt. The publisher performed a local smoke check but that check is an editorial attestation, not external benchmark evidence. Neither aiosqlite, a live MCP service, distributed workers nor production storage are certified here.
Primary sources
- SQLite WAL locking, concurrency and checkpoint limitations
- SQLite busy timeout API
- Python sqlite3 connection and transaction API
Source review: November 13, 2026. The reader should independently execute the synthetic fixture on a compatible local SQLite installation; original publication date and hero asset preserved.