Your Agent Didn't Lose Its Memory. SQLite Said 'Database Is Locked' and Dropped the Write.

Your agent sent the same Telegram summary twice this morning. Or it “forgot” a task you queued yesterday. Or a dedupe key that should have stopped a repeat post just isn’t in the table.

You check the logs and nothing looks wrong. The job ran. It exited 0. No alert fired.

Somewhere in there, if you look hard enough, is one line: sqlite3.OperationalError: database is locked. That write never landed. The agent didn’t lose its memory. It never saved it.

Why it happens

SQLite is a great fit for a Pi or a small VPS: one file, no server, no tuning. But it allows only one writer at a time, and how it handles “someone else is writing” depends on settings most scripts never touch.

The usual setup that hits this:

  • A long-running agent process that keeps a connection open to state.db
  • A cron job or systemd timer that also writes to it
  • Maybe a dashboard or a sqlite3 shell you left open, reading the same file

Here’s what goes wrong.

Rollback journal mode lets readers block writers. The default journal mode is DELETE, a rollback journal. In that mode a writer needs every reader to finish before it can commit. A dashboard polling the DB, or a slow SELECT in your agent loop, is enough to make a writer wait.

The default wait is often zero. In the SQLite C API the busy timeout defaults to 0. If the lock isn’t free, you get SQLITE_BUSY back immediately. The sqlite3 command-line tool doesn’t set one either. Python’s sqlite3 module is kinder: timeout defaults to 5.0 seconds. But five seconds isn’t always enough when another job is holding a write transaction open while it waits on a model API.

Deferred transactions can fail without waiting at all. A plain BEGIN is deferred. It takes no lock until the first read, and only tries to get the write lock at the first write. If your code reads, decides, then writes, the upgrade from reader to writer can fail with SQLITE_BUSY right away. In some cases SQLite won’t call the busy handler at all, because waiting could deadlock.

In WAL mode there’s a specific version of this. Your transaction reads from a snapshot. If another connection commits in between, your snapshot is stale and can’t be upgraded to a write. You get SQLITE_BUSY_SNAPSHOT, and no timeout will fix it. You have to roll back and start over.

Then the script does what a lot of quick automation does: except Exception: log.warning(...), carries on, exits 0. The scheduler sees success. The next run has no record of what happened.

How to confirm it

Start with the logs:

grep -ri "database is locked\|SQLITE_BUSY" /var/log/ ~/agent/logs/ 2>/dev/null
journalctl -u my-agent.service -u my-agent-job.service --since "7 days ago" | grep -i "locked"

Check which journal mode the database is in:

sqlite3 ~/agent/state.db "PRAGMA journal_mode;"

delete means rollback journal. wal means WAL is already on.

See who has the file open:

fuser -v ~/agent/state.db*
lsof ~/agent/state.db*

The glob matters. In WAL mode you’ll see state.db-wal and state.db-shm next to the main file. If you spot a forgotten sqlite3 shell or a dashboard process holding them, that’s a suspect.

The fixes

None of these are exotic. Together they turn most lock errors into a short wait, and turn the rest into a loud failure.

1. Switch to WAL mode

sqlite3 ~/agent/state.db "PRAGMA journal_mode=WAL;"

This is persistent. It’s stored in the database file, so you set it once, not on every connection.

In WAL mode, readers don’t block writers and writers don’t block readers. You still get one writer at a time, but your dashboard stops getting in the way.

The caveats:

  • Not on network filesystems. WAL relies on shared memory. Don’t use it on NFS or SMB mounts. Honestly, don’t put an actively written SQLite file on a network share at all.
  • Same host only. Every process opening the database must be on the same machine.
  • The -wal and -shm files must stay with the database and be writable by every user that opens it. The directory needs to be writable too, so those files can be created. This one bites when your agent runs as agent and a cron job runs as root or pi. Whoever creates the -shm file first owns it, and the other user then gets errors. Run everything that touches the DB as the same user, or fix the group permissions on the directory.

2. Set busy_timeout on every connection

PRAGMA busy_timeout = 10000;

Unlike journal_mode, this is per connection. Every script, every process, every time it connects. Something in the 5000–10000 ms range is reasonable for agent workloads. With it set, a writer that finds the lock taken retries for up to that long before giving up.

3. Use BEGIN IMMEDIATE for anything that writes

If a transaction will write, take the write lock at the start:

BEGIN IMMEDIATE;
-- reads and writes
COMMIT;

The point is that BEGIN IMMEDIATE waits for the lock up front, and the busy timeout applies there. You avoid the read-then-upgrade failure, including SQLITE_BUSY_SNAPSHOT in WAL mode.

4. Keep write transactions short

Never call a model, hit an API, or send a message inside a write transaction. Do the slow work first, then open the transaction, write, and commit. A transaction that spans a 40-second LLM call holds the write lock for 40 seconds, and everyone else’s timeout starts running out.

5. synchronous=NORMAL in WAL

PRAGMA synchronous = NORMAL;

In WAL mode this is a reasonable tradeoff. The database stays consistent. A power loss can roll back the most recent commits, but an application crash won’t. It’s per connection, so set it alongside busy_timeout. If losing the last transaction on a power cut is unacceptable, leave it at FULL.

Python: wait, retry, then fail loudly

import random, sqlite3, sys, time
from datetime import date

DB = "/home/agent/agent/state.db"

def connect():
    conn = sqlite3.connect(DB, timeout=10.0, isolation_level=None)
    conn.execute("PRAGMA busy_timeout = 10000")
    conn.execute("PRAGMA synchronous = NORMAL")
    return conn

def write_with_retry(conn, fn, attempts=5):
    for i in range(1, attempts + 1):
        try:
            conn.execute("BEGIN IMMEDIATE")
            fn(conn)
            conn.execute("COMMIT")
            return
        except sqlite3.OperationalError as e:
            if conn.in_transaction:
                conn.execute("ROLLBACK")
            msg = str(e).lower()
            if ("locked" not in msg and "busy" not in msg) or i == attempts:
                raise
            delay = min(2 ** i, 30) * random.uniform(0.5, 1.5)
            print(f"db busy (attempt {i}/{attempts}), retry in {delay:.1f}s",
                  file=sys.stderr)
            time.sleep(delay)

def record_sent(conn):
    conn.execute(
        "INSERT INTO sent (key, sent_at) VALUES (?, datetime('now'))",
        (f"daily-summary-{date.today()}",),
    )

if __name__ == "__main__":
    try:
        write_with_retry(connect(), record_sent)
    except Exception as e:
        print(f"FATAL: state write failed: {e}", file=sys.stderr)
        sys.exit(1)

isolation_level=None stops Python from silently opening its own deferred transactions, so your explicit BEGIN IMMEDIATE is what actually runs. The retry is bounded, with jitter so two jobs don’t retry in lockstep. After the last attempt it raises, and the script exits non-zero.

Retries mean a write might be attempted more than once. Pair this with idempotency keys so a retried write doesn’t double-send. A unique key column plus INSERT ... ON CONFLICT DO NOTHING is often enough.

Bash: the same idea with the sqlite3 CLI

#!/usr/bin/env bash
set -euo pipefail
DB=/home/agent/agent/state.db

if ! sqlite3 -bail -cmd ".timeout 10000" "$DB" <<'SQL'
BEGIN IMMEDIATE;
INSERT INTO queue (task, created_at) VALUES ('reindex', datetime('now'));
COMMIT;
SQL
then
  echo "FATAL: queue write failed" >&2
  exit 1
fi

.timeout 10000 sets the busy timeout. -bail stops at the first error so a failed BEGIN doesn’t let later statements run, and the CLI exits non-zero. Check that exit status. Don’t pipe it into something that swallows it.

Make dropped writes loud

Most of the damage here comes from the error being quiet, not from the error itself.

  • No except: pass. No except Exception: log.warning(...) around a state write followed by business as usual.
  • If the write that records “I did this” failed, the run failed. Exit non-zero so systemd marks the unit failed and cron’s mail or your alerting picks it up.
  • Count failures. Even a counter in a log line or a small failures table makes a trend visible before it becomes a double-send.

And reduce contention at the source. If two runs of the same job can overlap, they will eventually. Add overlap locks so two runs don’t write at once.

Reproduce it in two terminals

Use a scratch file:

sqlite3 /tmp/lock-test.db "CREATE TABLE t (x INTEGER);"

Terminal 1, hold the write lock for 15 seconds:

( echo "BEGIN IMMEDIATE;"; sleep 15; echo "COMMIT;" ) | sqlite3 /tmp/lock-test.db

Terminal 2, while that’s running:

sqlite3 /tmp/lock-test.db "INSERT INTO t VALUES (1);"; echo "exit=$?"

You should see database is locked right away, because the CLI has no busy timeout by default. Now try:

sqlite3 -cmd ".timeout 20000" /tmp/lock-test.db "INSERT INTO t VALUES (2);"; echo "exit=$?"

This one waits until terminal 1 commits, then succeeds. Same contention, different outcome, one setting. Point your real scripts at a test copy and run them against a held lock to see what they do.

Backups and WAL growth

Don’t cp a live WAL database. Recent commits may still be in the -wal file, and copying files mid-write can give you an inconsistent snapshot. Use SQLite’s own tools:

sqlite3 ~/agent/state.db ".backup '/backups/state-$(date +%F).db'"
# or
sqlite3 ~/agent/state.db "VACUUM INTO '/backups/state-$(date +%F).db';"

Then prove the backup opens and has your data with a restore drill.

One more WAL gotcha. A reader that never closes its transaction, like a dashboard that keeps one open, can stop checkpoints from completing, so the -wal file keeps growing. Fix the long-lived reader first, then add a maintenance step:

sqlite3 -cmd ".timeout 10000" ~/agent/state.db "PRAGMA wal_checkpoint(TRUNCATE);"

Run it from a quiet-hours timer and check the output. A non-zero first column means it was blocked.

Make it a standing check

This isn’t a one-time fix. Every new script that touches the state DB is a chance to reintroduce it. Grab the free prompts and turn one into a routine that reviews any script touching the state DB for a missing busy_timeout, writes without BEGIN IMMEDIATE, and swallowed exceptions.

If you’d rather not build the plumbing yourself, the OpenClaw Agent Ops Toolkit has wrappers, locks, preflight checks and alerting ready to drop in. Or get the Ultimate Bundle, which includes everything for $49.

More from the build log

Suggested

Want the full MarketMai stack?

Get the core MarketMai guides and operator playbooks in one premium bundle for $49.

View Bundle