Skip to content

Commit 0fe4fa4

Browse files
fix(server): sqlite transactions wait for the write lock instead of failing (#15488)
Co-authored-by: Claude Opus 5.5 (1M context) <[email protected]>
1 parent 4059607 commit 0fe4fa4

2 files changed

Lines changed: 114 additions & 24 deletions

File tree

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

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,8 +61,88 @@ layer("NodeSqliteClient", (it) => {
6161
assert.equal(error.reason.operation, "prepare");
6262
}),
6363
);
64+
65+
it.effect("classifies constraint failures by their SQLite result code", () =>
66+
Effect.gen(function* () {
67+
const sql = yield* SqlClient.SqlClient;
68+
yield* sql`CREATE TABLE constrained(name TEXT NOT NULL UNIQUE)`;
69+
yield* sql`INSERT INTO constrained VALUES ('taken')`;
70+
71+
const duplicate = yield* sql`INSERT INTO constrained VALUES ('taken')`.pipe(Effect.flip);
72+
assert(duplicate.reason._tag === "UniqueViolation");
73+
assert.equal(duplicate.reason.constraint, "constrained.name");
74+
75+
const missing = yield* sql`INSERT INTO constrained VALUES (NULL)`.pipe(Effect.flip);
76+
assert.equal(missing.reason._tag, "ConstraintError");
77+
}),
78+
);
6479
});
6580

81+
const makeTempDatabase = Effect.gen(function* () {
82+
const fs = yield* FileSystem.FileSystem;
83+
const path = yield* Path.Path;
84+
const directory = yield* fs.makeTempDirectoryScoped({ prefix: "t3-sqlite-transaction-" });
85+
const filename = path.join(directory, "state.sqlite");
86+
// node:sqlite connections fail a busy statement at once unless given a timeout.
87+
const other = yield* Effect.acquireRelease(
88+
Effect.sync(() => new NodeSqlite.DatabaseSync(filename)),
89+
(database) => Effect.sync(() => database.close()),
90+
);
91+
yield* Effect.sync(() => {
92+
other.exec(`
93+
PRAGMA journal_mode = WAL;
94+
CREATE TABLE counters(id INTEGER PRIMARY KEY, value INTEGER NOT NULL);
95+
INSERT INTO counters VALUES (1, 0);
96+
`);
97+
});
98+
return { filename, other };
99+
});
100+
101+
it.effect("keeps another connection from committing between a transaction's read and write", () =>
102+
Effect.gen(function* () {
103+
const { filename, other } = yield* makeTempDatabase;
104+
yield* Effect.gen(function* () {
105+
const sql = yield* SqlClient.SqlClient;
106+
yield* sql.withTransaction(
107+
Effect.gen(function* () {
108+
const [row] = yield* sql<{ readonly value: number }>`
109+
SELECT value FROM counters WHERE id = 1
110+
`;
111+
// With a deferred BEGIN this commit lands and the write below fails
112+
// with SQLITE_BUSY_SNAPSHOT, which no busy timeout can wait out.
113+
yield* Effect.sync(() =>
114+
assert.throws(
115+
() => other.exec("UPDATE counters SET value = value + 1 WHERE id = 1"),
116+
/database is locked/,
117+
),
118+
);
119+
yield* sql`UPDATE counters SET value = ${(row?.value ?? 0) + 10} WHERE id = 1`;
120+
}),
121+
);
122+
yield* Effect.sync(() => other.exec("UPDATE counters SET value = value + 1 WHERE id = 1"));
123+
assert.deepEqual(yield* sql`SELECT value FROM counters WHERE id = 1`.values, [[11]]);
124+
}).pipe(Effect.provide(SqliteClient.layer({ filename })));
125+
}).pipe(Effect.provide(NodeServices.layer)),
126+
);
127+
128+
it.effect("reports a transaction blocked by another writer as a lock timeout", () =>
129+
Effect.gen(function* () {
130+
const { filename, other } = yield* makeTempDatabase;
131+
yield* Effect.gen(function* () {
132+
const sql = yield* SqlClient.SqlClient;
133+
yield* sql`PRAGMA busy_timeout = 0`;
134+
const read = sql.withTransaction(sql`SELECT value FROM counters WHERE id = 1`.values);
135+
136+
yield* Effect.sync(() => other.exec("BEGIN IMMEDIATE"));
137+
const error = yield* read.pipe(Effect.flip);
138+
assert.equal(error.reason._tag, "LockTimeoutError");
139+
140+
yield* Effect.sync(() => other.exec("COMMIT"));
141+
assert.deepEqual(yield* read, [[0]]);
142+
}).pipe(Effect.provide(SqliteClient.layer({ filename })));
143+
}).pipe(Effect.provide(NodeServices.layer)),
144+
);
145+
66146
it.effect("returns a typed failure when the database cannot be opened", () =>
67147
Effect.gen(function* () {
68148
const error = yield* Effect.flip(

‎packages/shared/src/nodeSqliteClient.ts‎

Lines changed: 34 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ import * as Exit from "effect/Exit";
1313
import * as Fiber from "effect/Fiber";
1414
import { identity } from "effect/Function";
1515
import * as Layer from "effect/Layer";
16+
import * as Predicate from "effect/Predicate";
1617
import * as Schema from "effect/Schema";
1718
import * as Scope from "effect/Scope";
1819
import * as Semaphore from "effect/Semaphore";
@@ -82,6 +83,22 @@ const checkNodeSqliteCompat = () => {
8283
return Effect.void;
8384
};
8485

86+
/**
87+
* `node:sqlite` reports the SQLite result code as `errcode`, while
88+
* `classifySqliteError` reads `errno`. Copy it across so busy, locked and
89+
* constraint failures get their own reasons instead of `UnknownError`.
90+
*/
91+
const classifyError = (cause: unknown, message: string, operation: string) => {
92+
if (
93+
Predicate.hasProperty(cause, "errcode") &&
94+
typeof cause.errcode === "number" &&
95+
!Predicate.hasProperty(cause, "errno")
96+
) {
97+
Object.assign(cause, { errno: cause.errcode });
98+
}
99+
return classifySqliteError(cause, { message, operation });
100+
};
101+
85102
const make = Effect.fn("makeWithDatabase")(function* (
86103
options: SqliteClientConfig,
87104
): Effect.fn.Return<Client.SqlClient, SqlError, Scope.Scope | Reactivity.Reactivity> {
@@ -102,10 +119,7 @@ const make = Effect.fn("makeWithDatabase")(function* (
102119
}),
103120
catch: (cause) =>
104121
new SqlError({
105-
reason: classifySqliteError(cause, {
106-
message: "Failed to open database",
107-
operation: "open",
108-
}),
122+
reason: classifyError(cause, "Failed to open database", "open"),
109123
}),
110124
});
111125
yield* Scope.addFinalizer(
@@ -114,10 +128,7 @@ const make = Effect.fn("makeWithDatabase")(function* (
114128
try: () => db.close(),
115129
catch: (cause) =>
116130
new SqlError({
117-
reason: classifySqliteError(cause, {
118-
message: "Failed to close database",
119-
operation: "close",
120-
}),
131+
reason: classifyError(cause, "Failed to close database", "close"),
121132
}),
122133
}).pipe(Effect.orDie),
123134
);
@@ -138,10 +149,7 @@ const make = Effect.fn("makeWithDatabase")(function* (
138149
try: () => db.prepare(sql),
139150
catch: (cause) =>
140151
new SqlError({
141-
reason: classifySqliteError(cause, {
142-
message: "Failed to prepare statement",
143-
operation: "prepare",
144-
}),
152+
reason: classifyError(cause, "Failed to prepare statement", "prepare"),
145153
}),
146154
});
147155

@@ -168,10 +176,7 @@ const make = Effect.fn("makeWithDatabase")(function* (
168176
} catch (cause) {
169177
return Effect.fail(
170178
new SqlError({
171-
reason: classifySqliteError(cause, {
172-
message: "Failed to execute statement",
173-
operation: "execute",
174-
}),
179+
reason: classifyError(cause, "Failed to execute statement", "execute"),
175180
}),
176181
);
177182
}
@@ -201,10 +206,7 @@ const make = Effect.fn("makeWithDatabase")(function* (
201206
},
202207
catch: (cause) =>
203208
new SqlError({
204-
reason: classifySqliteError(cause, {
205-
message: "Failed to execute statement",
206-
operation: "execute",
207-
}),
209+
reason: classifyError(cause, "Failed to execute statement", "execute"),
208210
}),
209211
}),
210212
(statement) =>
@@ -216,10 +218,11 @@ const make = Effect.fn("makeWithDatabase")(function* (
216218
},
217219
catch: (cause) =>
218220
new SqlError({
219-
reason: classifySqliteError(cause, {
220-
message: "Failed to reset statement result mode",
221-
operation: "resetResultMode",
222-
}),
221+
reason: classifyError(
222+
cause,
223+
"Failed to reset statement result mode",
224+
"resetResultMode",
225+
),
223226
}),
224227
}).pipe(Effect.orDie),
225228
);
@@ -273,6 +276,13 @@ const make = Effect.fn("makeWithDatabase")(function* (
273276
acquirer,
274277
compiler,
275278
transactionAcquirer,
279+
// A deferred BEGIN only takes the write lock at the first write. If another
280+
// process commits after this transaction's first read, that write fails at
281+
// once with SQLITE_BUSY_SNAPSHOT, which busy_timeout cannot wait out. Taking
282+
// the lock up front makes it wait instead, at the cost of serializing
283+
// read-only transactions behind other processes' writers. Read-only
284+
// connections cannot write, so they keep the deferred BEGIN.
285+
beginTransaction: options.readonly === true ? "BEGIN" : "BEGIN IMMEDIATE",
276286
spanAttributes: [
277287
...(options.spanAttributes ? Object.entries(options.spanAttributes) : []),
278288
[ATTR_DB_SYSTEM_NAME, "sqlite"],

0 commit comments

Comments
 (0)