Skip to content

Commit 5a0144e

Browse files
test(server): drop second-connection schedule tests that BEGIN IMMEDIATE rules out
Main's SQLite client now opens write transactions with BEGIN IMMEDIATE (#15488), so another connection cannot commit between a transaction's read and its write; the three tests that staged that interleaving now fail with "database is locked". Remove them. Contention can still surface as a lock held past busy_timeout, so keep the bounded retry and its injected lock-timeout tests, and describe it that way. Co-Authored-By: Claude Opus 5.5 (1M context) <[email protected]>
1 parent 5d4fc40 commit 5a0144e

2 files changed

Lines changed: 25 additions & 211 deletions

File tree

‎apps/server/src/scheduledTasks/ScheduledTaskService.test.ts‎

Lines changed: 9 additions & 193 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,7 @@
11
import * as Scheduler from "../scheduling/Scheduler.ts";
2-
import * as NodeSqlite from "node:sqlite";
32
import * as NodeUtil from "node:util";
43

54
import * as NodeCrypto from "@effect/platform-node/NodeCrypto";
6-
import * as NodeFileSystem from "@effect/platform-node/NodeFileSystem";
7-
import * as NodePath from "@effect/platform-node/NodePath";
85
import { assert, it } from "@effect/vitest";
96
import {
107
CommandId,
@@ -18,7 +15,6 @@ import {
1815
import * as DateTime from "effect/DateTime";
1916
import * as Effect from "effect/Effect";
2017
import * as Fiber from "effect/Fiber";
21-
import * as FileSystem from "effect/FileSystem";
2218
import * as Layer from "effect/Layer";
2319
import * as Logger from "effect/Logger";
2420
import * as Option from "effect/Option";
@@ -33,10 +29,7 @@ import * as TestClock from "effect/testing/TestClock";
3329

3430
import * as ThreadLaunchService from "../orchestration-v2/ThreadLaunchService.ts";
3531
import * as ThreadManagementService from "../orchestration-v2/ThreadManagementService.ts";
36-
import {
37-
makeSqlitePersistenceLive,
38-
SqlitePersistenceMemory,
39-
} from "../persistence/Layers/Sqlite.ts";
32+
import { SqlitePersistenceMemory } from "../persistence/Layers/Sqlite.ts";
4033
import * as ScheduledTaskService from "./ScheduledTaskService.ts";
4134

4235
const isScheduledTaskError = Schema.is(ScheduledTaskError);
@@ -1122,17 +1115,16 @@ const countingLaunchLayer = Layer.mock(ThreadLaunchService.ThreadLaunchService)(
11221115
}),
11231116
});
11241117

1125-
// The exact failure a WAL read snapshot raises when a cross-connection
1126-
// commit invalidates it mid-transaction (SQLITE_BUSY_SNAPSHOT) — node:sqlite
1127-
// reports it via `errcode`, which the classifier misses (UnknownError).
1128-
const busySnapshotError = () =>
1118+
// A lock timeout reported only through a raw `errcode`, without the client's
1119+
// `errno` normalization: the native-code fallback must still retry it.
1120+
const nodeBusyError = () =>
11291121
new SqlError.SqlError({
11301122
reason: new SqlError.UnknownError({
1131-
cause: Object.assign(new Error("database is locked"), { errcode: 517 }),
1123+
cause: Object.assign(new Error("database is locked"), { errcode: 5 }),
11321124
}),
11331125
});
11341126

1135-
// The same contention as Bun reports it — `code`/`errno`, which the
1127+
// The same lock timeout as Bun reports it — `code`/`errno`, which the
11361128
// classifier maps to a retryable LockTimeoutError. Both shapes must retry.
11371129
const bunBusyError = () =>
11381130
new SqlError.SqlError({
@@ -1305,12 +1297,12 @@ it.effect("a contended completion write retries instead of stranding the task",
13051297
dispatchLaunchCount = 0;
13061298
let injected = false;
13071299
sqlProbe = (statement) => {
1308-
// One-shot SQLITE_BUSY_SNAPSHOT on the completion write: without the
1300+
// One-shot lock timeout on the completion write: without the
13091301
// contention retry the transaction's failure propagates and the row is
13101302
// left last_run_status='running', which due scans then exclude forever.
13111303
if (!injected && isTerminalWrite(statement) && statement.includes("last_run_at =")) {
13121304
injected = true;
1313-
return Effect.fail(busySnapshotError());
1305+
return Effect.fail(nodeBusyError());
13141306
}
13151307
};
13161308
try {
@@ -1339,7 +1331,7 @@ it.effect("a contended recovery write retries instead of stranding the task", ()
13391331
// scans forever. ('failed' is a literal only in releaseStuckRun's SQL.)
13401332
if (statement.includes("last_run_at =")) {
13411333
completionFails += 1;
1342-
return Effect.fail(busySnapshotError());
1334+
return Effect.fail(nodeBusyError());
13431335
}
13441336
if (statement.includes("'failed'")) {
13451337
releaseFails += 1;
@@ -1362,40 +1354,6 @@ it.effect("a contended recovery write retries instead of stranding the task", ()
13621354
}).pipe(Effect.provide(gatedDispatchLayer)),
13631355
);
13641356

1365-
// A second physical connection is required for true contention: the client's
1366-
// single connection serializes transactions, so nothing else can interleave
1367-
// mid-transaction — a file database plus a raw second handle can.
1368-
const fileDbLayer = (dbPath: string) =>
1369-
ScheduledTaskService.layer.pipe(
1370-
Layer.provideMerge(
1371-
Layer.mergeAll(
1372-
gatedSqlClient.pipe(Layer.provide(makeSqlitePersistenceLive(dbPath))),
1373-
NodeCrypto.layer,
1374-
Scheduler.layer,
1375-
Layer.mock(ThreadLaunchService.ThreadLaunchService)({}),
1376-
Layer.mock(ThreadManagementService.ThreadManagementService)({}),
1377-
),
1378-
),
1379-
);
1380-
1381-
// Parks the first scoped-update read (the SELECT inside the update
1382-
// transaction) until the test releases it — the deterministic mid-read window
1383-
// a competing connection writes into.
1384-
const armUpdateReadGate = (readDone: Deferred.Deferred<void>, proceed: Deferred.Deferred<void>) => {
1385-
let fired = false;
1386-
sqlProbe = (statement) => {
1387-
// getScopedRows inside the update transaction.
1388-
if (
1389-
!fired &&
1390-
statement.includes("FROM scheduled_tasks") &&
1391-
statement.includes("AND project_id")
1392-
) {
1393-
fired = true;
1394-
return Deferred.succeed(readDone, undefined).pipe(Effect.andThen(Deferred.await(proceed)));
1395-
}
1396-
};
1397-
};
1398-
13991357
it.effect("update keeps a project move and a thread binding consistent", () =>
14001358
Effect.gen(function* () {
14011359
const tasks = yield* ScheduledTaskService.ScheduledTaskService;
@@ -1469,145 +1427,3 @@ it.effect("update keeps a project move and a thread binding consistent", () =>
14691427
assert.equal(missing.failure._tag, "ScheduledTaskError");
14701428
}).pipe(Effect.provide(updateTestLayerWithSql)),
14711429
);
1472-
1473-
it.effect("competing updates on separate connections preserve disjoint fields", () =>
1474-
Effect.gen(function* () {
1475-
const fs = yield* FileSystem.FileSystem;
1476-
const dir = yield* fs.makeTempDirectory();
1477-
const dbPath = `${dir}/state.sqlite`;
1478-
yield* Effect.gen(function* () {
1479-
const tasks = yield* ScheduledTaskService.ScheduledTaskService;
1480-
yield* seedTask;
1481-
const readDone = yield* Deferred.make<void>();
1482-
const proceed = yield* Deferred.make<void>();
1483-
armUpdateReadGate(readDone, proceed);
1484-
try {
1485-
// The title update parks between its in-transaction read and write.
1486-
const updateFiber = yield* tasks
1487-
.update({ id: updateTaskId, projectId: updateProjectId, title: "title A" })
1488-
.pipe(Effect.forkChild);
1489-
yield* Deferred.await(readDone);
1490-
// A prompt write commits on an independent connection while the
1491-
// first update holds its snapshot.
1492-
const db = new NodeSqlite.DatabaseSync(dbPath);
1493-
try {
1494-
db.exec(
1495-
`UPDATE scheduled_tasks SET prompt = 'prompt B' WHERE task_id = '${updateTaskId}'`,
1496-
);
1497-
} finally {
1498-
db.close();
1499-
}
1500-
yield* Deferred.succeed(proceed, undefined);
1501-
const outcome = yield* Fiber.join(updateFiber);
1502-
assert.isTrue(Option.isSome(outcome));
1503-
const row = yield* findSeeded;
1504-
// Both fields survive a genuinely contested interleaving.
1505-
assert.equal(row?.title, "title A");
1506-
assert.equal(row?.prompt, "prompt B");
1507-
} finally {
1508-
sqlProbe = null;
1509-
}
1510-
}).pipe(Effect.provide(fileDbLayer(dbPath)));
1511-
}).pipe(Effect.provide(Layer.mergeAll(NodeFileSystem.layer, NodePath.layer))),
1512-
);
1513-
1514-
it.effect("a schedule update contended by a committed disable merges on a fresh snapshot", () =>
1515-
Effect.gen(function* () {
1516-
const fs = yield* FileSystem.FileSystem;
1517-
const dir = yield* fs.makeTempDirectory();
1518-
const dbPath = `${dir}/state.sqlite`;
1519-
yield* Effect.gen(function* () {
1520-
const tasks = yield* ScheduledTaskService.ScheduledTaskService;
1521-
yield* seedTask;
1522-
const readDone = yield* Deferred.make<void>();
1523-
const proceed = yield* Deferred.make<void>();
1524-
armUpdateReadGate(readDone, proceed);
1525-
try {
1526-
// The schedule update parks with its read snapshot; under WAL its
1527-
// first write attempt after the competing commit lands on a stale
1528-
// snapshot (SQLITE_BUSY_SNAPSHOT) and the service retries, so the
1529-
// patch is rebuilt on the committed state instead of clobbering it.
1530-
const updateFiber = yield* tasks
1531-
.update({
1532-
id: updateTaskId,
1533-
projectId: updateProjectId,
1534-
schedule: { type: "interval", everyMs: 3_600_000 },
1535-
})
1536-
.pipe(Effect.forkChild);
1537-
yield* Deferred.await(readDone);
1538-
const db = new NodeSqlite.DatabaseSync(dbPath);
1539-
try {
1540-
db.exec(
1541-
`UPDATE scheduled_tasks SET enabled = 0, next_run_at = NULL WHERE task_id = '${updateTaskId}'`,
1542-
);
1543-
} finally {
1544-
db.close();
1545-
}
1546-
yield* Deferred.succeed(proceed, undefined);
1547-
const outcome = yield* Fiber.join(updateFiber);
1548-
assert.isTrue(Option.isSome(outcome));
1549-
const row = yield* findSeeded;
1550-
// Both edits survive: the disable and the new schedule, with the
1551-
// due time recomputed from the committed state (disabled → null).
1552-
assert.isFalse(row?.enabled);
1553-
assert.isNull(row?.nextRunAt);
1554-
assert.equal(row?.schedule.type, "interval");
1555-
if (row?.schedule.type === "interval") {
1556-
assert.equal(row.schedule.everyMs, 3_600_000);
1557-
}
1558-
} finally {
1559-
sqlProbe = null;
1560-
}
1561-
}).pipe(Effect.provide(fileDbLayer(dbPath)));
1562-
}).pipe(Effect.provide(Layer.mergeAll(NodeFileSystem.layer, NodePath.layer))),
1563-
);
1564-
1565-
it.effect("a delete committed inside the update read window is never resurrected", () =>
1566-
Effect.gen(function* () {
1567-
const fs = yield* FileSystem.FileSystem;
1568-
const dir = yield* fs.makeTempDirectory();
1569-
const dbPath = `${dir}/state.sqlite`;
1570-
yield* Effect.gen(function* () {
1571-
const tasks = yield* ScheduledTaskService.ScheduledTaskService;
1572-
yield* seedTask;
1573-
const readDone = yield* Deferred.make<void>();
1574-
const proceed = yield* Deferred.make<void>();
1575-
armUpdateReadGate(readDone, proceed);
1576-
try {
1577-
const updateFiber = yield* tasks
1578-
.update({ id: updateTaskId, projectId: updateProjectId, title: "contended" })
1579-
.pipe(Effect.forkChild);
1580-
yield* Deferred.await(readDone);
1581-
// The update transaction holds its snapshot; this delete commits on
1582-
// an independent connection while the update is parked mid-read.
1583-
const db = new NodeSqlite.DatabaseSync(dbPath);
1584-
try {
1585-
db.exec(`DELETE FROM scheduled_tasks WHERE task_id = '${updateTaskId}'`);
1586-
} finally {
1587-
db.close();
1588-
}
1589-
yield* Deferred.succeed(proceed, undefined);
1590-
const outcome = yield* Fiber.join(updateFiber).pipe(Effect.result);
1591-
// The only permitted result: the committed delete wins and the
1592-
// update reports the task gone — `none`. A typed conflict would mean
1593-
// the delete was misread as a contested edit, and a failure means the
1594-
// contention retry gave up on a single commit; neither is allowed.
1595-
if (Result.isFailure(outcome)) {
1596-
assert.fail(`expected the update to return none, got ${outcome.failure._tag}`);
1597-
}
1598-
assert.isTrue(Option.isNone(outcome.success));
1599-
const check = new NodeSqlite.DatabaseSync(dbPath);
1600-
try {
1601-
const row = check.prepare("SELECT COUNT(*) AS n FROM scheduled_tasks").get() as {
1602-
n: number;
1603-
};
1604-
assert.equal(row.n, 0);
1605-
} finally {
1606-
check.close();
1607-
}
1608-
} finally {
1609-
sqlProbe = null;
1610-
}
1611-
}).pipe(Effect.provide(fileDbLayer(dbPath)));
1612-
}).pipe(Effect.provide(Layer.mergeAll(NodeFileSystem.layer, NodePath.layer))),
1613-
);

‎apps/server/src/scheduledTasks/ScheduledTaskService.ts‎

Lines changed: 16 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -153,13 +153,13 @@ function errorMessage(error: unknown): string {
153153
return String(error);
154154
}
155155

156-
// Contention surfaces differently per runtime: Bun's SQLite driver reports
157-
// `code`/`errno`, which the vendored classifier maps to a retryable
158-
// LockTimeoutError, while node:sqlite reports `errcode` (e.g.
159-
// SQLITE_BUSY_SNAPSHOT 517), which the classifier cannot see. Accept either:
160-
// a reason the classifier already marked retryable, or a native numeric code
161-
// in the SQLITE_BUSY (5) / SQLITE_LOCKED (6) families on any of the field
162-
// names a driver might use. The ScheduledTaskError unwrap covers statements
156+
// Write transactions take the lock up front and wait up to busy_timeout, so
157+
// contention only surfaces when another process holds the lock longer than
158+
// that. Bun reports `code`/`errno`, and the node:sqlite client copies
159+
// `errcode` into `errno`, so both normally arrive as a retryable
160+
// LockTimeoutError. Accept that classification, and as a fallback a raw
161+
// native code in the SQLITE_BUSY (5) / SQLITE_LOCKED (6) families on any of
162+
// the field names a driver might use without that normalization. The ScheduledTaskError unwrap covers statements
163163
// that wrap SqlError inside the transaction before the retry policy sees it.
164164
const isContendedWriteError = (cause: unknown): boolean => {
165165
if (isScheduledTaskError(cause)) return isContendedWriteError(cause.cause);
@@ -175,9 +175,9 @@ const isContendedWriteError = (cause: unknown): boolean => {
175175
return false;
176176
};
177177

178-
// A commit on another connection between a transaction's read and its write
179-
// leaves that transaction on a stale snapshot (SQLITE_BUSY_SNAPSHOT) — retry
180-
// on a fresh snapshot so disjoint edits still merge instead of failing.
178+
// Retry a write that timed out waiting for another process's lock, so a
179+
// lock held a little past busy_timeout does not drop an edit or strand a run
180+
// as 'running'.
181181
const retryContended = <A, E, R>(effect: Effect.Effect<A, E, R>) =>
182182
effect.pipe(Effect.retry({ times: 2, while: isContendedWriteError }));
183183

@@ -562,7 +562,7 @@ export const layer = Layer.effect(
562562
// Re-read and write under one lock so an edit committed between them
563563
// keeps its own next_run_at rather than being overwritten by the
564564
// schedule snapshot taken before the edit. Contention retry keeps a
565-
// cross-connection commit from stranding the task as 'running'.
565+
// lock held by another process from stranding the task as 'running'.
566566
yield* retryContended(
567567
sql
568568
.withTransaction(
@@ -739,8 +739,9 @@ export const layer = Layer.effect(
739739
// is *now* (the user may have edited or deleted it while we ran). The
740740
// re-read and the write share one transaction so a concurrent edit
741741
// cannot land between them and have its next_run_at overwritten by a
742-
// stale schedule. Contention retry keeps a cross-connection commit
743-
// from abandoning the completion write with the task left 'running'.
742+
// stale schedule. Contention retry keeps a lock held by another
743+
// process from abandoning the completion write with the task left
744+
// 'running'.
744745
const current = yield* retryContended(
745746
sql
746747
.withTransaction(
@@ -1110,11 +1111,8 @@ export const layer = Layer.effect(
11101111
}
11111112
}
11121113
patch.updated_at = iso(now);
1113-
// The read above and this write share one transaction on the
1114-
// client's single connection, so no same-process write can land
1115-
// between them; a commit from another connection invalidates the
1116-
// snapshot (SQLITE_BUSY_SNAPSHOT) and retryContended rebuilds the
1117-
// patch on fresh state.
1114+
// The read above and this write share one write-locked
1115+
// transaction, so no other write can land between them.
11181116
const written = yield* sql<ScheduledTaskRow>`
11191117
UPDATE scheduled_tasks
11201118
SET ${sql.update(patch)}

0 commit comments

Comments
 (0)