Skip to content

Commit dd2121d

Browse files
committed
fix(server): wait for concurrent SQLite writers instead of failing with SQLITE_BUSY
The CLI and server open the same state database from separate processes, but no busy_timeout was ever set, so a write that hit another process's write lock failed immediately with "database is locked" instead of waiting for the lock to clear.
1 parent 1a003e3 commit dd2121d

2 files changed

Lines changed: 68 additions & 0 deletions

File tree

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
1+
// @effect-diagnostics nodeBuiltinImport:off
2+
import * as NodeChildProcess from "node:child_process";
3+
import * as NodeFS from "node:fs";
4+
import * as NodeOS from "node:os";
5+
import * as NodePath from "node:path";
6+
7+
import * as NodeServices from "@effect/platform-node/NodeServices";
8+
import { assert, it } from "@effect/vitest";
9+
import * as Effect from "effect/Effect";
10+
import * as Layer from "effect/Layer";
11+
import * as SqlClient from "effect/unstable/sql/SqlClient";
12+
13+
import { SqlitePersistenceMemory, makeSqlitePersistenceLive } from "./Sqlite.ts";
14+
15+
const lockHolderSource = `
16+
const { DatabaseSync } = require("node:sqlite");
17+
const db = new DatabaseSync(process.argv[1]);
18+
db.exec("BEGIN IMMEDIATE");
19+
process.stdout.write("locked\\n");
20+
setTimeout(() => {
21+
db.exec("COMMIT");
22+
db.close();
23+
}, Number(process.argv[2]));
24+
`;
25+
26+
const spawnWriteLockHolder = (dbPath: string, holdMs: number) =>
27+
Effect.promise(
28+
() =>
29+
new Promise<void>((resolve, reject) => {
30+
const holder = NodeChildProcess.spawn(
31+
process.execPath,
32+
["-e", lockHolderSource, dbPath, String(holdMs)],
33+
{ stdio: ["ignore", "pipe", "ignore"] },
34+
);
35+
holder.stdout.once("data", () => resolve());
36+
holder.on("error", reject);
37+
holder.on("exit", () =>
38+
reject(new Error("lock holder exited before acquiring the write lock")),
39+
);
40+
}),
41+
);
42+
43+
it.effect("waits out a concurrent writer instead of failing with SQLITE_BUSY", () => {
44+
const tempDir = NodeFS.mkdtempSync(NodePath.join(NodeOS.tmpdir(), "t3-sqlite-busy-"));
45+
const dbPath = NodePath.join(tempDir, "state.sqlite");
46+
47+
return Effect.gen(function* () {
48+
const sql = yield* SqlClient.SqlClient;
49+
yield* sql`CREATE TABLE busy_probe(id INTEGER PRIMARY KEY)`;
50+
yield* spawnWriteLockHolder(dbPath, 300);
51+
yield* sql`INSERT INTO busy_probe(id) VALUES (${1})`;
52+
const rows = yield* sql<{ readonly id: number }>`SELECT id FROM busy_probe`;
53+
assert.deepEqual([...rows], [{ id: 1 }]);
54+
}).pipe(
55+
Effect.provide(makeSqlitePersistenceLive(dbPath).pipe(Layer.provide(NodeServices.layer))),
56+
Effect.ensuring(Effect.sync(() => NodeFS.rmSync(tempDir, { recursive: true, force: true }))),
57+
);
58+
});
59+
60+
it.effect("applies busy_timeout in the shared persistence setup", () =>
61+
Effect.gen(function* () {
62+
const sql = yield* SqlClient.SqlClient;
63+
const rows = yield* sql<{ readonly timeout: number }>`PRAGMA busy_timeout`;
64+
assert.equal(rows[0]?.timeout, 5000);
65+
}).pipe(Effect.provide(SqlitePersistenceMemory)),
66+
);

‎apps/server/src/persistence/Layers/Sqlite.ts‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,8 @@ const makeRuntimeSqliteLayer = Effect.fn("makeRuntimeSqliteLayer")(function* (
3333
const setup = Layer.effectDiscard(
3434
Effect.gen(function* () {
3535
const sql = yield* SqlClient.SqlClient;
36+
// CLI and server write from separate processes; wait rather than fail with SQLITE_BUSY.
37+
yield* sql`PRAGMA busy_timeout = 5000;`;
3638
yield* sql`PRAGMA foreign_keys = ON;`;
3739
yield* sql`PRAGMA journal_mode = WAL;`;
3840
yield* runMigrations();

0 commit comments

Comments
 (0)