search()/query() iterators run outside the request transaction: a handler cannot see its own writes, and its scans and point reads disagree about the snapshot #2506

Summary

Every iterator read surface of a request transaction runs outside that transaction. A handler
that writes a row and then reads it back through search()/query() — index scan, full scan, or
even a condition on the primary key — does not see its own write. Only the point read
(tables.X.get(id)) does.

The same gap has a second consequence: those scans are not on the transaction's snapshot either, so
a row a different request commits while the transaction is open is visible to its full scan and
is not visible to its point read. One request's scans and point reads disagree about the state
of the database.

Reported from a live 5.2.7 deployment as a secondary-index bug. The secondary index is not what is
special — it was simply the surface the application used.

Measured

One request transaction, one staged write, four read surfaces (RocksDB, reproduced on main):

ownWrite: { pointGet: true, byPrimaryCondition: false, fullScan: false, byIndex: false }

Control, in the same transaction and the same index search: three rows committed before the
transaction opened are returned. So the index read works; only the transaction's own write is
missing from it.

Snapshot consequence — a row committed by another request 750 ms into an open transaction:

{ pointGet: false, fullScan: true, byIndex: false }

Waiting inside the transaction changes nothing (the reporter held it 10 s); a staged write's
visibility is not time-dependent.

LMDB misses the staged write on all four surfaces, point read included, which matches the
documented LMDB staging behaviour (DESIGN.md:117, HarperFast/documentation#531). The get-vs-scan
split is RocksDB-only.

Root cause

Below the Table layer, with the same read handle search() uses
(Table._readTxnForContext(context)):

store.get(id,      { transaction })          → true    (write batch consulted)
store.getRange({ start, end, transaction })  → false   (option accepted and dropped)

rocksdb-js Store.getRange() builds the native iterator from this._context and never reads
options.transaction, while get/getSync route it through getTxnId(). Filed as the root cause
in HarperFast/rocksdb-js#830.

Harper's query engine passes the option on every range read that should be transactional:

• resources/search.ts — executeConditions() resolves table._readTxnForContext(context) and
  hands it to searchByIndex(), which passes it as index.getRange({ …, transaction }); the
  primary-key and full-scan paths do the same.
• resources/Table.ts — _readTxnForContext() returns txnForContext(context).getReadTxn(),
  i.e. the transaction the handler's writes are staged into. The write really is in the native
  transaction; the point read proves it. Only the iterator cannot see it.

Impact

Silent. search() returns fewer rows, with no error and nothing in the log, so it is
indistinguishable from "those rows do not exist". The reporting application enforces a per-account
row cap by writing and then counting through an indexed search, and overshoots under concurrency —
6 concurrent adds against a limit of 3 leave 4 rows, 3 trials of 3. Any tenancy, quota,
dedupe, or state-machine check that writes and then scans in one request has the same exposure, and
so does any handler that assumes its scans and its point reads describe the same snapshot.

Not a data-corruption bug: committed state is correct, and a fresh request reads it correctly.

Reproduction

A fixture reproducing this is written but not pushed, so treat the recipe below as the spec rather
than looking for the branch. One custom Resource endpoint, inside its single request transaction:

1. await tables.Item.put({ id, groupId, … }) on a table whose groupId is @indexed;
2. read the row back four ways and report each as a boolean —
   tables.Item.get(id),
   search({conditions:[{attribute:'id', value:id}]}),
   search({conditions:[]}) (full scan),
   search({conditions:[{attribute:'groupId', value:groupId}]});
3. seed rows under the same groupId from earlier requests first, and assert they ARE returned by
   step 2's index search — the control that the index read works inside this transaction.

For the snapshot half: give the endpoint a sleep between the write and the reads, POST a second
request that commits a row under the same groupId while it sleeps, and assert the scans and the
point read agree about whether that row is visible.

npm run build
npm run test:integration -- "integrationTests/database/index-read-your-writes.test.ts"
HARPER_STORAGE_ENGINE=lmdb npm run test:integration -- "integrationTests/database/index-read-your-writes.test.ts"

Fix shape

Two spellings of the same change:

1. fix Store.getRange() in rocksdb-js so options.transaction means what it means on get(); or
2. iterate transaction.getRange() in Harper — the transaction as the iterator's context — which
   already works today.

Either way every transactional scan starts pinning its read transaction for the life of the
iterator, which lands squarely in DatabaseTransaction's read-transaction lifetime accounting
(readTxnsUsed, doneReadTxn, and the "read iterators held a committed transaction's snapshot past
the open-transaction limit" path). Scoping that is the substance of this issue; the rocksdb-js
option being inert is the substance of the other.

Not this

• Secondary index permanently diverges from the primary store, and rewriting the record does not repair it (5.2.1 / rocksdb-js 2.7.1) #2211 (replicated delete K; put K stripping a live record's index entries) is a
  different defect. Its fix, Fix a replicated delete+put of the same record dropping all of its secondary index entries #2235, first shipped in v5.2.4 and is present in the 5.2.7 that
  reproduces this — verified by merge-base --is-ancestor against that release's core pointer.
• Add ability to exclusively lock a record for performing a safe operation #483 (CAS / insert-if-absent) does not address this. The reporter's separate
  finding that concurrent create() on one primary key admits a nondeterministic number of racers
  is Add ability to exclusively lock a record for performing a safe operation #483's territory.
• Document transaction read-your-own-writes semantics + safe concurrent-write patterns (read-back-to-decide is unsafe) documentation#531 documents tables.X.get(id) read-your-writes semantics per engine
  and says nothing about search(); it should pick up whatever this resolves to.

Versions

Reproduced on main and observed live on harper-pro 5.2.7 (core f883a3bf). RocksDB only for the
get-vs-scan split; the no-own-write-on-scans half applies to both engines.