Writer Isolate and Invalidation Data

Problem Statement

The stream engine made one thing unavoidable: writes were not just commands that changed the database. They were also the source of truth for reactive invalidation. If writes were unordered, ran on the wrong isolate, or failed to report what changed, streams would either miss updates or do unnecessary work.

SQLite in WAL mode supports many concurrent readers but only one writer. resqlite needed to preserve that serialization rule, keep write work off the main isolate, support interactive transactions, and report the dirty-table facts that reactive streams depend on.

The write path was therefore not just a queue of execute() calls. It was the owner of mutation order, transaction state, and invalidation data.

Background

Three shapes were considered as the library moved from read experiments toward a complete database runtime:

Run writes on the main isolate.
Spawn a one-off isolate for each write.
Keep a persistent writer isolate that owns the write connection.

The persistent writer matched SQLite's model best. It processes write messages sequentially and keeps transaction state alive across calls. That matters for interactive transactions, where BEGIN, multiple reads/writes, nested savepoints, and COMMIT all need one consistent writer context.

The writer also owns the preupdate hook. As SQLite mutates rows, resqlite records touched table names. When the write finishes, the response includes affected rows, last insert ID, and the dirty-table set consumed by the stream engine.

The SQLite constraints are explicit here. In write-ahead logging mode, readers and a writer can overlap, but there is still only one active writer. SQLite's transaction rules also make transaction lifetime meaningful: a BEGIN, subsequent statements, savepoints, and COMMIT have to run against a coherent connection state. resqlite's writer isolate maps that native constraint onto Dart by giving mutation work one owner.

The hot path is still the ordinary prepared-statement API. Parameters are attached with the sqlite3_bind_*() family, statements are stepped, reset, and reused, and transaction-control SQL can be executed either through sqlite3_exec() or cached prepared statements. The experiments below are about reducing repeated work in that lifecycle, not changing SQLite's serialization model.

Hypothesis

A persistent writer isolate should make write behavior easier to reason about, and native batching should keep write throughput competitive despite the isolate boundary. If the writer also returns dirty tables with each completed mutation, stream invalidation can stay tied to the write that caused it.

What We Tried

The core design put all mutations on a single writer isolate and moved batch execution into native code. executeBatch() serializes parameter sets into native storage, crosses the FFI boundary once, and lets C loop through bind, step, reset, and commit.

Several follow-up experiments then reduced fixed costs:

Experiment 014 accepted BEGIN IMMEDIATE and removed redundant sqlite3_clear_bindings, while rejecting locking_mode=EXCLUSIVE because it blocked readers.
Experiment 101 replaced repeated sqlite3_exec("BEGIN/COMMIT/ROLLBACK") calls with cached transaction-control statements.
Experiment 109 packed text/blob parameter bytes into the reusable parameter buffer and passed explicit UTF-8 lengths to SQLite.

Results

The baseline write path was already fast enough that many single changes fell near the noise floor. The later accepted wins were concentrated where the hypothesis predicted: transaction boundaries and bind-heavy workloads.

Cached transaction statements removed repeated prepare/finalize work:

Workload	Baseline	Cached tx statements	Delta
Batched write inside transaction, 100 rows	0.59 ms	0.52 ms	-13%
Growing-stream invalidation, batch insert 100	0.60 ms	0.52 ms	-14%
Interactive transaction	0.05 ms	0.05 ms	within noise

Inline-packed parameters removed per-text/blob native allocations and skipped SQLite's internal strlen walk:

Workload	Baseline	Inline param buffer	Delta
Single inserts, 100 sequential	1.88 ms	1.61 ms	-14%
Batch insert, 10,000 rows	4.21 ms	3.68 ms	-13%
Batched write inside transaction	0.43 ms	0.39 ms	-10%
No-streams write throughput, 200 inserts	4.03 ms	3.40 ms	-16%

The rejected piece in Experiment 014 is also part of the evidence. PRAGMA locking_mode=EXCLUSIVE looked attractive as a writer optimization, but it caused SQLITE_BUSY on reader connections. That made it incompatible with the reader-pool architecture.

Outcome

The writer isolate remained a first-class subsystem because it owns three connected responsibilities:

Execute mutations in SQLite's single-writer order.
Preserve transaction semantics across async calls.
Publish dirty-table data for reactive streams.

The deeper walkthrough is now part of resqlite Architecture. The experiment record shows that the durable write-path wins came less from changing SQLite semantics and more from removing repeated setup from hot paths.

With reads, streams, and writes now organized around stable subsystems, the project entered a different phase. The hard part became deciding which optimizations were worth carrying forward and which ones should be documented as dead ends.