ECC2 SQLite "database is locked" errors under normal concurrent usage – Any recommended workaround?

[s1993-oss]

Hi Affaan,

First of all, thanks for building and maintaining ECC. I'm really enjoying using it.

I'm running three independent ECC2 daemons on Ubuntu, each with its own configuration directory and SQLite database. Each daemon runs 1–2 Claude Code sessions, along with scheduled tasks (picker every 10 minutes, merge check hourly, and reports every 4 hours).

However, I'm repeatedly encountering database is locked errors during normal usage.

The errors appear in different parts of the daemon:

Worktree auto-prune
Scheduled task dispatch
Session checks
Session runner startup
Session output persistence

Over the last 24 hours, I've recorded 14 lock events across the three daemons, including 12 failed runner starts in a single morning.

Some sessions get stuck in pending state without ever starting a process. I've also experienced skipped scheduled tasks and lost session output.

I investigated the source code and noticed a few potential contributing factors:

SQLite uses a 5-second busy_timeout, with no explicit WAL journal mode configuration.
StateStore::open() appears to execute schema initialization and an INSERT OR IGNORE on every invocation, including CLI invocations.
append_output_line() performs multiple database writes for every output line.
Transient database lock errors appear to propagate without retry in some critical paths.

I also tried increasing heartbeat_interval_secs from 30 to 120, but it didn't make any noticeable difference.

My questions are:

Have you encountered this issue before?
Is there a recommended configuration or workaround to prevent SQLite locking?
Would enabling WAL mode be a safe and recommended approach for ECC2?
Are there any plans to improve SQLite concurrency handling in ECC2?

I haven't modified the source code or database settings locally. I'd be happy to provide logs and help test potential fixes.

Thanks in advance for your help!

[QuantIntellect]

WAL is safe here and needs no code change, but expect far fewer errors, not zero. You found most of the pieces (links are to main @ b38be6a). The one that dominates is refresh_session_board_meta(): open() and every insert_session, update_state and update_pid re-upsert a board row for every session in the DB, each in its own auto-commit. If your picker, merge and report jobs are ECC2 schedules, each run adds a session (about 170 a day per daemon) and nothing prunes them, so it gets worse every day.

• open() never sets journal_mode, so the DB is in rollback-journal mode, where readers and writers block each other, and append_output_line adds 3 writes per output line.
• A runner opens the store 3 times (main.rs, manager.rs, runtime.rs). If any of them fails, the session stays pending, with the error in <working dir>/.claude/ecc2/logs/<id>.runner-stderr.log. A single failed output write ends capture for that stream, which explains the lost output.
• The daemon can strand sessions too. insert_session commits the row before rebuilding the board, so if the rebuild hits the lock, scheduled dispatch stops before spawning the runner. The row stays pending with no runner log; the only trace is Scheduled task dispatch pass failed in the daemon's log. The schedule isn't marked as run, so it fires again on the next pass, and the other schedules due in that pass wait too. Queued worktree sessions start inside the daemon, so their failures show up there as Failed to start queued worktree session, and those rows stay pending too.
• So the 5 s busy_timeout isn't the problem on its own: each lock error is a connection that waited the full 5 s while those back-to-back commits kept taking the lock. heartbeat_interval_secs only spaces out the daemon loop and runner heartbeats, which is why 120 didn't help.

Workaround, per daemon. You need the sqlite3 CLI (sudo apt install sqlite3). Run ECC2's own commands below (the README runs them as cargo run -- stop <session-id>) from the directory and environment you start that daemon from, so they open its DB.

1. Close any dashboard open on these DBs, and don't leave one running. Every ~250 ms tick it calls refresh_session_durations(), which rebuilds the whole board, so it's probably your heaviest writer. Switching to WAL also needs the DB to itself for a moment, so while a dashboard runs, the PRAGMA below will likely fail with database is locked.
2. Stop the daemons and wait until pgrep -af run-session prints nothing. Then:

   DB=~/.claude/ecc2.db   # set to this daemon's db_path (default shown)
   sqlite3 "$DB" 'PRAGMA journal_mode=WAL;'       # must print "wal"
   sqlite3 "$DB" 'SELECT count(*) FROM sessions;'  # rows re-upserted on every open/state change

   WAL is persistent, and ECC2 never resets it. Readers and the writer stop blocking each other, but there's still only one writer at a time.
3. That count is roughly how many separate commits every open and state change makes. If it's in the hundreds, pruning helps more than WAL, but d deletes one finished session per keypress and there's no bulk command. Running the 10-minute picker less often slows the growth. If your schedules share a minute (say all on :00), spread them out, e.g. merge check at :05 and reports at :35, so runner starts land in different daemon passes (schedule remove, then schedule add with the new --cron). To clear the backlog, this deletes finished sessions older than a day that have no worktree, with their output, tool log and messages, the same rows d deletes:

   sqlite3 "$DB" ".backup '$DB.bak'"
   sqlite3 -bail "$DB" <<'SQL'
   PRAGMA foreign_keys=ON;
   BEGIN;
   CREATE TEMP TABLE prune AS SELECT id FROM sessions
    WHERE state IN ('completed','failed','stopped') AND worktree_path IS NULL
      AND julianday(updated_at) < julianday('now','-1 day');
   DELETE FROM session_output WHERE session_id IN (SELECT id FROM prune);
   DELETE FROM tool_log WHERE session_id IN (SELECT id FROM prune);
   DELETE FROM messages WHERE from_session IN (SELECT id FROM prune) OR to_session IN (SELECT id FROM prune);
   DELETE FROM sessions WHERE id IN (SELECT id FROM prune);
   COMMIT;
   SELECT count(*) FROM sessions;
   SQL

   The other child tables cascade or set null, and if it stops on an error, nothing has changed. Sessions that still have a worktree are better deleted with d, which also removes the worktree.
4. Stuck pending rows never age out: d refuses pending/running/idle sessions, and the heartbeat check only looks at running/stale ones. Leave any that are still in pending_worktree_queue; those are just waiting for a worktree slot. For a scheduled run, stop <id> and then d, since the schedule runs again anyway. For a one-off session you still want, resume <id> respawns its runner. In the dashboard these are s and u.

Then restart the daemons. WAL caveats:

• Same host, local disk only: no NFS or SMB.
• If you copy the DB, copy ecc2.db-wal with it.
• The Node control pane reads the raw .db into sql.js, so it misses changes still in the WAL until a checkpoint.

Proper fixes. I couldn't find an existing report of this; #3018 is the closest. I can't speak for plans, but it already proposes batching output writes (§5) and fixing the board rebuild (§4). Not covered there:

• WAL plus synchronous=NORMAL in open() (synchronous is per-connection, so it needs a code change)
• no init or board writes for read-only commands
• retrying SQLITE_BUSY on runner start and on output writes
• a longer busy_timeout as a stop-gap (it's hard-coded in open())

Your lock counts and logs would be useful on #3018 or a new issue.