Apr 25, 2026 · Rejected

Experiment 103: Native nested transaction depth control

Date: 2026-04-25

Status: Rejected

Archive:archive/exp-103

Benchmark Run: None

Problem

Experiment 101 accepted cached prepared statements for the top-level

transaction controls:

• BEGIN IMMEDIATE
• COMMIT
• ROLLBACK

It intentionally left nested transaction controls alone because their SQL is

depth-dependent:

• SAVEPOINT sN
• RELEASE sN
• ROLLBACK TO sN

Experiment 102 tried caching the Dart-side native strings for those savepoint

statements, but the standard benchmark suite had no nested-transaction workload,

so there was no attributable signal.

This follow-up adds a focused nested-transaction benchmark and tests whether

moving depth-based transaction control into C is worth the complexity.

Hypothesis

The savepoint path pays fixed per-boundary costs in Dart:

• string interpolation for sN
• UTF-8 encoding
• native allocation/free for the SQL string
• generic resqliteExec(...) call path

A C helper can build the SQL into a stack buffer and execute it directly:

 resqlite_tx_depth_control(db, RESQLITE_TX_SAVEPOINT, depth); resqlite_tx_depth_control(db, RESQLITE_TX_RELEASE, depth); resqlite_tx_depth_control(db, RESQLITE_TX_ROLLBACK_TO, depth); 

That should remove Dart allocation/encoding work while preserving the existing

SQLite semantics.

Approach

Two passes were tested.

First pass:

• added three native functions:
• resqlite_tx_savepoint(db, depth)
• resqlite_tx_release(db, depth)
• resqlite_tx_rollback_to(db, depth)
• each function used snprintf(...) into a stack buffer and then

sqlite3_exec(...)

• replaced the nested branches in _handleBegin, _handleCommit, and

_handleRollback

Second pass:

• collapsed the native surface to one generic helper:
• resqlite_tx_depth_control(db, op, depth)
• added op constants instead of three Dart FFI bindings
• added a manual SQL builder to avoid snprintf(...) overhead
• broadened the focused benchmark to separate empty nesting overhead from

nested writes

Focused benchmark:

 dart run benchmark/experiments/nested_tx_control.dart \ --repeats=15 \ --cycles=5000 

The benchmark measures four cases:

• empty nested transaction that commits
• empty nested transaction that rolls back
• nested transaction with one write that commits
• nested transaction with one write that rolls back

Results

Validation:

• dart analyze native/resqlite.c native/resqlite.h lib/src/native/resqlite_bindings.dart lib/src/writer/write_worker.dart benchmark/experiments/nested_tx_control.dart
• dart test test/transaction_test.dart

Both passed in an external validation worktree with a fresh native build.

The second-pass snprintf(...) version had some encouraging intermediate

results:

But larger-cycle and reversed-order runs weakened the signal:

The final manual-SQL-builder pass did not improve things:

Decision

Rejected.

The first measurable signal came from a new focused nested-transaction benchmark,

which was useful, but the optimization itself does not clear the bar.

The best-case result is a small savepoint-only improvement. Once the benchmark

includes realistic nested work, the effect drops into noise or regresses. The

manual SQL builder also made the native code more specialized without producing

a stable win.

The added complexity is not justified:

• extra internal native API surface
• duplicated transaction-control path
• more C code around a niche nested-transaction workload
• no expected impact on the standard release suite

The implementation is archived at archive/exp-103 in case a future workload

shows deeply nested transactions as a real bottleneck. For now, exp 101 remains

the right boundary: cache the hot top-level transaction controls, leave

depth-dependent savepoints on the simpler existing path.