duckdb-file-lock-contention-diagnosis

Answer

Root cause chain: DuckDB enforces a single-process writer via a whole-file lock. The production :3000 fleet daemon opens the default namespace's DuckDB file at startup and holds that lock (lsof shows 25uW = fd 25, read/write mode, whole-file Write lock) for the process's lifetime. Per-session stdio MCP servers that try to open the same file — even READ_ONLY — collide with that lock and surface as DUCKDB_CONNECTION_LOST. The stdio servers were innocent: the lock holder was the fleet daemon all along. A separate bug (BUG-034) lets stale tsx test daemons linger and hold locks on test-namespace files.

Fix rules (in priority order):

1. Never kill the fleet daemon. Diagnose with lsof first; only kill a PID that lsof proves holds the lock and that matches a disposable pattern (test daemon). A blanket pkill -f duckdb takes down production.
2. Route default-ns access through the HTTP :3000 fallback from per-session stdio servers — they must never open the default-ns file directly.
3. Use explicit namespaces → one DuckDB file per namespace so independent sessions open distinct files and never contend.
4. Kill stale tsx test daemons (BUG-034) surgically, pattern-matched and lsof-re-verified.

Code: lock-holder diagnosis (run before touching any process)

// scripts/who-holds-lock.ts  (Node/tsx, run as: tsx who-holds-lock.ts <dbPath>)
import { execSync } from "node:child_process";

const dbPath = process.argv[2] ?? "data/ns/default.db";

// FD column: "<fd><mode><lock>", e.g. "25uW" = fd 25, read/write, whole-file write lock
const lsof = execSync(`lsof "${dbPath}"`, { encoding: "utf8", stdio: ["ignore","pipe","pipe"] });
const holders = lsof.split("\n").slice(1).filter(Boolean).map(line => {
  const [cmd, pid, user, fd, ...rest] = line.split(/\s+/);
  return { cmd, pid, user, fd, lock: fd.includes("W") ? "whole-file-WRITE" : fd.includes("w") ? "partial-write" : "other" };
});

console.table(holders);
const w = holders.filter(h => h.lock === "whole-file-WRITE");
if (w.length) {
  console.error(`LOCK HELD BY: ${w[0].cmd} pid=${w[0].pid} fd=${w[0].fd} — do NOT kill if this is the :3000 fleet daemon`);
  process.exit(1); // caller must route via HTTP instead of killing
}
console.log("No write-lock holder — safe to open.");

Code: namespace routing (the actual fix in DuckBrain)

// src/store.ts
import path from "node:path";

const DATA_DIR = process.env.DUCKBRAIN_DATA ?? "data";
const FLEET_DAEMON_URL = process.env.DUCKBRAIN_HTTP ?? "http://<ip-address>:3000";
const isFleetDaemon = () => process.env.DUCKBRAIN_FLEET === "1";

/** One DuckDB file per namespace — explicit namespaces never collide. */
export function nsPath(ns: string): string {
  return path.join(DATA_DIR, "ns", ns === "default" ? "default.db" : `${ns}.db`);
}

export async function openNamespace(ns: string, opts: { readOnly?: boolean } = {}) {
  // Rule 2: the fleet daemon owns default-ns from startup. Stdio servers must
  // NOT open that file (even read-only) — route through the HTTP fallback.
  if (ns === "default" && !isFleetDaemon()) {
    return DuckHttpClient.open(FLEET_DAEMON_URL, ns, opts); // :3000 proxies DuckDB
  }
  // Rule 3: non-default namespaces are separate files → no lock contention.
  return DuckDBConnection.open(nsPath(ns), { readOnly: opts.readOnly ?? false });
}

Code: BUG-034 stale test-daemon cleanup (surgical, never blanket)

// scripts/kill-stale-test-daemons.ts
import { execSync } from "node:child_process";

const dbPath = "data/ns/test-e2e.db";
const lsof = execSync(`lsof "${dbPath}"`, { encoding: "utf8" }).trim();
if (!lsof) { console.log("no lock holder — nothing to do"); process.exit(0); }

for (const line of lsof.split("\n").slice(1)) {
  const [cmd, pid, , fd] = line.split(/\s+/);
  const cmdline = execSync(`ps -p ${pid} -o args=`, { encoding: "utf8" });
  const isTestDaemon = /tsx .*--test|test-runner|vitest/.test(cmdline);
  if (fd.includes("W") && isTestDaemon) {
    process.kill(Number(pid), "SIGTERM");              // kill ONLY the stale test daemon
    console.log(`killed stale test daemon pid=${pid} (fd=${fd})`);
  }
}
// Fleet daemon never matches the pattern → never touched.

Code: resilience at the call site

// When opening default-ns directly fails, fall back to HTTP instead of retrying the file.
async function connectDefaultNs() {
  try { return await DuckDBConnection.open(nsPath("default"), { readOnly: true }); }
  catch (e) { if (/Conflicting lock|Could not set lock/.test(String(e))) {
    return DuckHttpClient.open(FLEET_DAEMON_URL, "default"); }  // :3000 fallback
    throw e;
  }
}