session: two server processes sharing one opencode.db allocate session_message.seq independently - UNIQUE(seq) collisions fail sessions #53146

Description

When two opencode server processes share a single opencode.db (a long-lived opencode serve --service instance plus a TUI-embedded server from opencode -c), both allocate session_message.seq from independent in-memory counters. The second server's counter starts from a stale/near-empty view of the session, and as it advances it collides with rows already persisted by the first server: every colliding insert fails with UNIQUE constraint failed: session_message.session_id, session_message.seq, surfacing as "Session failed" in the UI and permanently breaking the session on that server.

OpenCode version

2.0.22 (channel latest); the same topology exists fleet-side on 1.18.34.

Operating System

Linux x64

Plugins

None installable; MCP servers only (via config).

Steps to reproduce

1. opencode serve --service on a data dir (server B, fresh TUI clients attach here)
2. Run opencode -c (TUI with embedded server, no TCP listener) on the SAME data dir
3. Continue working the same session from both processes
4. Server B's in-memory seq counter advances into seqs already persisted by server A -> UNIQUE constraint failed: session_message.session_id, session_message.seq, turns fail

Screenshot and/or share link

Log + DB evidence inline below (no share link available).

Observed failure

ERROR "Failed to drain Session"
  cause: EffectDrizzleQueryError: Failed query: insert into "session_message" (...) values (?, ...)
  [cause]: SQLiteError: UNIQUE constraint failed: session_message.session_id, session_message.seq
    at SessionRunner.publishLLMEvent -> SessionStep.attempt -> SessionRunner.runStep -> SessionRunner.drain

5 occurrences on one day, all against a single long-running session.

Evidence (from the live DB after the incident)

Two interleaved write streams into the same session_id, each with its own counter:

• Stream A (original server): counter intact across a day - wrote seqs 385-736 between 06:18-06:45 UTC.
• Stream B (second server): initialized from a near-empty view of the session - first write landed on seq 2 at 06:51 UTC, then advanced ~19 seqs per assistant turn (2, 17, 32, 51, 70, 89, 113 ... 710 by 12:52 UTC).
• Stream B's inserts succeed only where Stream A left gaps (its ephemeral/unpersisted seqs) and fail exactly where Stream A persisted rows:
  • seq 108: occupied since the previous day -> fail
  • seq 585 (+cascade 586): occupied 48 min earlier -> fail
  • seq 684: occupied 6 h earlier -> fail
  • seq 709: occupied 6 h earlier -> fail (the "Session failed" the user saw)
• Follow-up idle messages (+1 seq) inserted fine immediately after each failure (109, 685, 710 were free), which is why the session half-works and the failure looks intermittent.

Ruled out: disk space, SQLITE_BUSY, FK violations (session_v2 row exists), DB corruption (quick_check ok).

Root cause

session_message.seq is allocated per-process from in-memory runner state, while the database is shared. Nothing prevents two server processes from serving the same data directory and the same session, and nothing reconciles their counters. The first server to touch the session wins; the second replays an overlapping seq space and aborts turns on collisions. TUI restarts make this easy to hit: the old server keeps running while a new one spawns (both hold the DB).

Suggested directions

1. Allocate seq at insert time from the database (SELECT max(seq)+1 ... inside the write transaction, or an AUTOINCREMENT-style per-session counter table) instead of from in-memory runner state.
2. On UniqueViolation for (session_id, seq), re-read the current max seq and retry the insert once instead of failing the whole session run.
3. Guard the data directory against a second live server (lock/heartbeat row), or make a later server adopt the session state (re-read max seq at session load).

Related

• core: continuing a V1-created session fails with UNIQUE constraint on event seq #52395 - same UNIQUE constraint on seq, but triggered by continuing a V1-created session (migration path), not by dual live servers.