MHNSW shared graph cache is populated through a stale REPEATABLE READ read view; afterwards all sessions miss rows committed after that snapshot until FLUSH TABLES

Description

A vector search runs inside a REPEATABLE READ transaction whose consistent snapshot is older than some committed vector inserts. MHNSW then loads graph nodes into the table's shared cache (MHNSW_Share) through that transaction's read view. Those cached nodes do not have the neighbour links that later transactions committed. When the stale transaction ends, every other session, including fresh autocommit sessions that should see all committed rows, can no longer reach the rows committed after the snapshot through the index. The state persists until FLUSH TABLES t, or until a later write happens to invalidate the affected nodes.

The reader's own view excluding the newer rows is correct REPEATABLE READ behaviour. The defect is that the reader's view leaks into a cache shared by all sessions.

How to repeat (1024-dim, M=32, DISTANCE=cosine, InnoDB, binlog either on or off, default mhnsw_* otherwise; random unit vectors):

CREATE TABLE t (
id INT PRIMARY KEY,
v VECTOR(1024) NOT NULL,
VECTOR INDEX (v) M=32 DISTANCE=cosine
) ENGINE=InnoDB;

– load 1,000 random unit vectors (ids 1..1000) in autocommit batches of 100,
– e.g. INSERT INTO t VALUES (1, VEC_FromText('[...]')), ...;

– session C (autocommit): warm the cache with a few searches, e.g.
SELECT id FROM t ORDER BY VEC_DISTANCE_COSINE(v, VEC_FromText('<vector of id 1>')) LIMIT 1;

– session A:
START TRANSACTION WITH CONSISTENT SNAPSHOT;

– session B (autocommit): insert 300 NEW random unit vectors (ids 1001..1300), committed in batches of 100

– session A: run searches (any query vectors; we searched for 200 of the OLD rows), then end the transaction
SELECT id FROM t ORDER BY VEC_DISTANCE_COSINE(v, VEC_FromText('<vector of id 1>')) LIMIT 1;
– ... (repeat for more old rows)
COMMIT;

– session C (fresh autocommit statements): self-search each NEW row
SELECT id FROM t ORDER BY VEC_DISTANCE_COSINE(v, VEC_FromText('<vector of id 1001>')) LIMIT 1;
– expected: 1001 (and likewise for 1002..1300)
– actual: a different id, for ALL 300 new rows

FLUSH TABLES t;
– after the flush, every new row finds itself again

Observed
Case New rows missed immediately After FLUSH TABLES t
Stale reader present, 8 of 8 runs 300 / 300 0 / 300
Control (no stale reader), 8 of 8 runs 0 / 300 0 / 300

The state survived 28 s with no writes to the table.
On a small graph (about 1,300 rows), the next commit to the table cleared it. We expect a single later commit to heal a large graph only partly, since it invalidates only the nodes it touches; we did not measure that.

Expected

The shared cache should never contain node versions from a read view older than the latest committed state visible to new transactions. At least, a search in a transaction with an older snapshot should not publish nodes into the shared cache, or should mark them private to that transaction.

Analysis (from reading sql/vector_mhnsw.cc at tag mariadb-11.8.9)

For InnoDB, writers use the per-transaction MHNSW_Trx. Commit invalidates only nodes already present in the shared cache at commit time.
Readers load missing nodes into MHNSW_Share through the current statement's handler, and therefore through its read view.
A reader whose read view predates a commit can therefore insert pre-commit node versions into the shared cache after that commit's invalidation has already run.

MDEV-36869 (closed as Not a Bug) discussed that the cache does not exactly mirror InnoDB transactional semantics. This report is a deterministic, user-visible consequence: committed rows become unreachable for all sessions.

Secondary observation, weaker evidence. With the binary log off, MHNSW_Trx::do_commit, which performs the cache invalidation, appears to run before InnoDB makes the transaction visible. A concurrent reader in that gap could re-cache old neighbour lists. We saw this once (13 rows) and could not reproduce it in 4,000 targeted commits. With log_bin=ON the InnoDB commit_ordered runs first and the window closes.

Workaround we apply: run every vector search as an autocommit statement or under READ COMMITTED, never inside a REPEATABLE READ transaction with an earlier snapshot.

Related:

MDEV-36869, cache transactional semantics.
MDEV-37559, search non-determinism (the visited-set Bloom filter keyed by node address).