Skip to content

Commit 47667aa

Browse files
committed
fix(server): SQLite writes no longer block the event loop
The server's writer connection ran node:sqlite DatabaseSync on the main thread, so a slow statement or transaction on a large statev2.sqlite froze every HTTP and relay request until it finished. The writer now runs in a worker thread that reuses the reader worker's message protocol, behind the same SqlClient interface. If the worker cannot start, writes fall back to the main thread as before.
1 parent cefb544 commit 47667aa

4 files changed

Lines changed: 272 additions & 127 deletions

File tree

‎apps/server/src/orchestration-v2/FoundationPersistence.test.ts‎

Lines changed: 29 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,3 @@
1-
import * as NodeSqlite from "node:sqlite";
21
import * as NodeServices from "@effect/platform-node/NodeServices";
32
import { assert, it } from "@effect/vitest";
43
import {
@@ -47,8 +46,6 @@ import * as Tracer from "effect/Tracer";
4746
import * as SqlClient from "effect/sql/SqlClient";
4847
import * as Statement from "effect/sql/Statement";
4948

50-
import { vi } from "vite-plus/test";
51-
5249
import * as ServerConfig from "../config.ts";
5350
import * as DatabaseStorage from "../persistence/DatabaseStorage.ts";
5451
import * as ServerSettings from "../serverSettings.ts";
@@ -253,46 +250,41 @@ it.effect("discovers on the reader worker and deletes on the writer", () =>
253250
const path = yield* Path.Path;
254251
const filename = path.join(yield* fs.makeTempDirectoryScoped(), "fixture.sqlite");
255252
yield* Effect.gen(function* () {
256-
const sql = yield* SqlClient.SqlClient;
257253
yield* seedCompaction();
258-
// Establish the reader before recording main-thread statements.
259-
yield* sql`SELECT 1`.pipe(NodeSqliteClient.readOnly);
260-
const reads: string[] = [];
261-
const writes: string[] = [];
262-
const all = NodeSqlite.StatementSync.prototype.all;
263-
const run = NodeSqlite.StatementSync.prototype.run;
264-
yield* Effect.acquireRelease(
265-
Effect.sync(() => {
266-
const readSpy = vi
267-
.spyOn(NodeSqlite.StatementSync.prototype, "all")
268-
.mockImplementation(function (this: NodeSqlite.StatementSync, ...params) {
269-
reads.push(this.sourceSQL);
270-
return all.apply(this, params);
271-
});
272-
const writeSpy = vi
273-
.spyOn(NodeSqlite.StatementSync.prototype, "run")
274-
.mockImplementation(function (this: NodeSqlite.StatementSync, ...params) {
275-
writes.push(this.sourceSQL);
276-
return run.apply(this, params);
277-
});
278-
return { readSpy, writeSpy };
279-
}),
280-
({ readSpy, writeSpy }) =>
281-
Effect.sync(() => {
282-
readSpy.mockRestore();
283-
writeSpy.mockRestore();
284-
}),
285-
);
254+
const statements: Array<{ readonly query: unknown; readonly connection: unknown }> = [];
255+
const tracer = Tracer.make({
256+
span(options) {
257+
const span = new Tracer.NativeSpan(options);
258+
const end = span.end.bind(span);
259+
span.end = (time, exit) => {
260+
end(time, exit);
261+
const query = span.attributes.get("db.query.text");
262+
if (query === undefined) return;
263+
statements.push({ query, connection: span.attributes.get("db.connection") });
264+
};
265+
return span;
266+
},
267+
});
286268
const maintenance = yield* ProjectionMaintenance.ProjectionMaintenanceV2;
287-
const result = yield* maintenance.compactEventStore();
269+
const result = yield* maintenance
270+
.compactEventStore()
271+
.pipe(
272+
Effect.withTracer(tracer),
273+
Effect.provideService(Statement.SpanPropagationEnabled, true),
274+
);
288275
assert.equal(result.deletedEventCount, 148);
289276
assert.equal(result.deletedReceiptCount, 100);
277+
const connectionsFor = (pattern: RegExp) =>
278+
new Set(
279+
statements
280+
.filter(({ query }) => typeof query === "string" && pattern.test(query))
281+
.map(({ connection }) => connection),
282+
);
283+
assert.deepEqual(connectionsFor(/^\s*(SELECT|WITH)\b/), new Set(["reader"]));
290284
assert.deepEqual(
291-
reads.filter((query) => /\bSELECT\b/.test(query)),
292-
[],
285+
connectionsFor(/^\s*DELETE FROM orchestration_(events|command_receipts)\b/),
286+
new Set(["writer"]),
293287
);
294-
assert(writes.some((query) => query.includes("DELETE FROM orchestration_events")));
295-
assert(writes.some((query) => query.includes("DELETE FROM orchestration_command_receipts")));
296288
}).pipe(
297289
Effect.provide(
298290
ProjectionMaintenance.layer.pipe(

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

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -54,7 +54,9 @@ export const layerFromPath = Effect.fn("makeSqlitePersistenceLive")(function* (d
5454
NodeSqliteClient.layer({
5555
filename: dbPath,
5656
readerWorker: true,
57-
// Run checkpoint fsyncs off the event loop so slow disk flushes cannot stall clients.
57+
// Run writes off the event loop so slow statements and transactions cannot stall clients.
58+
writerWorker: true,
59+
// Keeps checkpoint fsyncs off the event loop if the writer has to fall back to the main thread.
5860
checkpointWorker: true,
5961
spanAttributes: {
6062
"db.name": path.basename(dbPath),

‎packages/shared/src/nodeSqliteClient.test.ts‎

Lines changed: 54 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -168,30 +168,32 @@ const makeTempDatabase = Effect.gen(function* () {
168168
return { filename, other };
169169
});
170170

171-
it.effect("keeps another connection from committing between a transaction's read and write", () =>
172-
Effect.gen(function* () {
173-
const { filename, other } = yield* makeTempDatabase;
174-
yield* Effect.gen(function* () {
175-
const sql = yield* SqlClient.SqlClient;
176-
yield* sql.withTransaction(
177-
Effect.gen(function* () {
178-
const [row] = yield* sql<{ readonly value: number }>`
171+
it.effect.each([{ writerWorker: false }, { writerWorker: true }])(
172+
"keeps another connection from committing between a transaction's read and write (writerWorker=$writerWorker)",
173+
({ writerWorker }) =>
174+
Effect.gen(function* () {
175+
const { filename, other } = yield* makeTempDatabase;
176+
yield* Effect.gen(function* () {
177+
const sql = yield* SqlClient.SqlClient;
178+
yield* sql.withTransaction(
179+
Effect.gen(function* () {
180+
const [row] = yield* sql<{ readonly value: number }>`
179181
SELECT value FROM counters WHERE id = 1
180182
`;
181-
// Deferred BEGIN permits a competing commit, causing SQLITE_BUSY_SNAPSHOT that no timeout can resolve.
182-
yield* Effect.sync(() =>
183-
assert.throws(
184-
() => other.exec("UPDATE counters SET value = value + 1 WHERE id = 1"),
185-
/database is locked/,
186-
),
187-
);
188-
yield* sql`UPDATE counters SET value = ${(row?.value ?? 0) + 10} WHERE id = 1`;
189-
}),
190-
);
191-
yield* Effect.sync(() => other.exec("UPDATE counters SET value = value + 1 WHERE id = 1"));
192-
assert.deepEqual(yield* sql`SELECT value FROM counters WHERE id = 1`.values, [[11]]);
193-
}).pipe(Effect.provide(SqliteClient.layer({ filename })));
194-
}).pipe(Effect.provide(NodeServices.layer)),
183+
// Deferred BEGIN permits a competing commit, causing SQLITE_BUSY_SNAPSHOT that no timeout can resolve.
184+
yield* Effect.sync(() =>
185+
assert.throws(
186+
() => other.exec("UPDATE counters SET value = value + 1 WHERE id = 1"),
187+
/database is locked/,
188+
),
189+
);
190+
yield* sql`UPDATE counters SET value = ${(row?.value ?? 0) + 10} WHERE id = 1`;
191+
}),
192+
);
193+
yield* Effect.sync(() => other.exec("UPDATE counters SET value = value + 1 WHERE id = 1"));
194+
assert.deepEqual(yield* sql`SELECT value FROM counters WHERE id = 1`.values, [[11]]);
195+
}).pipe(Effect.provide(SqliteClient.layer({ filename, writerWorker })));
196+
}).pipe(Effect.provide(NodeServices.layer)),
195197
);
196198

197199
it.effect("waits for another process's lock while the database opens", () =>
@@ -222,21 +224,41 @@ it.effect("waits for another process's lock while the database opens", () =>
222224
}).pipe(Effect.scoped, Effect.provide(NodeServices.layer)),
223225
);
224226

225-
it.effect("reports a transaction blocked by another writer as a lock timeout", () =>
227+
it.effect.each([{ writerWorker: false }, { writerWorker: true }])(
228+
"reports a transaction blocked by another writer as a lock timeout (writerWorker=$writerWorker)",
229+
({ writerWorker }) =>
230+
Effect.gen(function* () {
231+
const { filename, other } = yield* makeTempDatabase;
232+
yield* Effect.gen(function* () {
233+
const sql = yield* SqlClient.SqlClient;
234+
yield* sql`PRAGMA busy_timeout = 0`;
235+
const read = sql.withTransaction(sql`SELECT value FROM counters WHERE id = 1`.values);
236+
237+
yield* Effect.sync(() => other.exec("BEGIN IMMEDIATE"));
238+
const error = yield* read.pipe(Effect.flip);
239+
assert.equal(error.reason._tag, "LockTimeoutError");
240+
241+
yield* Effect.sync(() => other.exec("COMMIT"));
242+
assert.deepEqual(yield* read, [[0]]);
243+
}).pipe(Effect.provide(SqliteClient.layer({ filename, writerWorker })));
244+
}).pipe(Effect.provide(NodeServices.layer)),
245+
);
246+
247+
it.effect("keeps the event loop free while a write waits on another connection's lock", () =>
226248
Effect.gen(function* () {
227249
const { filename, other } = yield* makeTempDatabase;
228250
yield* Effect.gen(function* () {
229251
const sql = yield* SqlClient.SqlClient;
230-
yield* sql`PRAGMA busy_timeout = 0`;
231-
const read = sql.withTransaction(sql`SELECT value FROM counters WHERE id = 1`.values);
232-
233252
yield* Effect.sync(() => other.exec("BEGIN IMMEDIATE"));
234-
const error = yield* read.pipe(Effect.flip);
235-
assert.equal(error.reason._tag, "LockTimeoutError");
236-
237-
yield* Effect.sync(() => other.exec("COMMIT"));
238-
assert.deepEqual(yield* read, [[0]]);
239-
}).pipe(Effect.provide(SqliteClient.layer({ filename })));
253+
const write = yield* Effect.forkChild(
254+
sql`UPDATE counters SET value = value + 1 WHERE id = 1`,
255+
{ startImmediately: true },
256+
);
257+
// A writer on this thread would spin in busy_timeout, so the holder could never commit.
258+
yield* Effect.sync(() => other.exec("UPDATE counters SET value = 10 WHERE id = 1; COMMIT"));
259+
yield* Fiber.join(write);
260+
assert.deepEqual(yield* sql`SELECT value FROM counters WHERE id = 1`.values, [[11]]);
261+
}).pipe(Effect.provide(SqliteClient.layer({ filename, writerWorker: true })));
240262
}).pipe(Effect.provide(NodeServices.layer)),
241263
);
242264

0 commit comments

Comments
 (0)