All For Science

Posted on Aug 19

SapixDB docs education: Explain the MVCC isolation model using a real-time banking reconciliation scenario.

#mvcc #database #banking #concurrency

How SapixDB's MVCC Isolation Model Works — A Real-Time Banking Reconciliation Walkthrough

Imagine it is 11:58 PM on the last business day of the quarter. Your reconciliation engine fires up and begins tallying every account balance across thousands of records. Simultaneously, the payment processor is still running — debits, credits, wire transfers, all happening in real time. In a traditional locking database, one of these systems has to wait for the other. In SapixDB, both run in full parallel, neither aware of the other's existence, and the reconciliation job still produces a mathematically perfect, auditable result. That is MVCC — Multi-Version Concurrency Control — and understanding it changes how you think about data consistency at scale.

What MVCC Actually Means

Multi-Version Concurrency Control is a concurrency strategy built on a deceptively simple idea: instead of locking a record when someone reads it, keep multiple versions of it and let each reader see the version that was current at the moment their query started.

Writers don't overwrite. They append a new version. Readers don't block writers. They read from a consistent snapshot pinned to a specific timestamp. Nobody waits for anybody else.

In most databases, MVCC is implemented as a feature layered on top of a mutable storage engine — old versions are eventually vacuumed away, and if you miss the garbage collection window, you lose your history.

In SapixDB, MVCC is not a feature. It is the architecture. SapixDB is an append-only database. Every record written to a strand is immutable. Superseded records remain in the chain forever. There is no vacuum process racing against your reads, because old versions are never physically removed — they are part of the permanent, cryptographically-linked strand. This is what makes SapixDB's time-travel capability structurally guaranteed rather than operationally maintained.

The Banking Reconciliation Scenario

Let's build this out concretely.

System setup:

ledger-agent — an SapixDB agent owning the strand of all financial transaction records
reconciliation-engine — a process that runs at end-of-quarter to verify that all debits equal all credits
payment-processor — a live system continuously appending new transaction records to the same strand

The challenge: the reconciliation engine must produce a consistent, balanced ledger snapshot — even though the payment processor is appending new transactions to the same strand in real time.

Step 1 — Pinning the Reconciliation Snapshot

When the reconciliation job starts, the very first thing it does is record the current HLC (Hybrid Logical Clock) timestamp. This becomes its snapshot anchor — the point in time it will read from for the entire reconciliation run.

In SapixDB, HLC timestamps are computed as:

import time
HLC = int(time.time() * 1000) * 65536

The reconciliation engine captures this value at job start — call it T_recon — and all of its queries will be pinned to this timestamp for the duration of the run.

Querying the Snapshot with SaQL

To read all transaction records as they existed at T_recon, the reconciliation engine issues an as_of query via SaQL's HTTP API:

HLC=1748736000000000   # T_recon — captured at job start

curl -s -X POST http://localhost:7475/v1/agents/ledger-agent/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d "{
    \"type\": \"as_of\",
    \"timestamp_hlc\": $HLC,
    \"limit\": 100
  }" | python3 -m json.tool

The response returns every record that existed in the ledger-agent strand at exactly T_recon — no earlier, no later. Records appended by the payment processor after T_recon are invisible to this query. They don't appear in the result set. They don't affect the totals. The reconciliation engine is reading from a frozen moment.

Step 2 — The Payment Processor Keeps Running

While the reconciliation engine is paginating through thousands of transaction records, the payment processor is still live. It appends new nucleotides (records) to the ledger-agent strand:

curl -s -X POST http://localhost:7475/v1/records \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "payload": {
      "_type": "transaction",
      "account_id": "ACC-00291",
      "amount": -450.00,
      "currency": "USD",
      "description": "Wire transfer outbound",
      "direction": "debit"
    }
  }'

This write succeeds immediately. It gets its own record_id, its own hash, its own timestamp_hlc — all greater than T_recon. It chains onto the strand head. The payment processor does not know, and does not care, that a reconciliation job is reading the strand right now.

And the reconciliation engine does not see this new record. Its as_of query is pinned to T_recon. This is MVCC in action: zero contention, zero blocking, zero coordination between reader and writer.

Step 3 — Windowed Reconciliation for a Specific Period

The reconciliation job doesn't just need a point-in-time snapshot — it needs to audit a specific time window (say, the previous quarter). SapixDB supports this through time_range queries, which accept from_hlc and to_hlc bounds:

FROM_HLC=1740787200000000   # Quarter start
TO_HLC=1748736000000000     # T_recon — quarter end

curl -s -X POST http://localhost:7475/v1/agents/ledger-agent/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d "{
    \"type\": \"time_range\",
    \"from_hlc\": $FROM_HLC,
    \"to_hlc\": $TO_HLC,
    \"limit\": 1000
  }" | python3 -m json.tool

This returns every transaction record whose timestamp_hlc falls within the quarter window. The reconciliation engine can now sum all debits and credits across that range — and because the upper bound is pinned, no new records can enter the window mid-run. The sums are stable from the first page to the last.

Why Append-Only Changes Everything for MVCC

In a traditional MVCC database, old row versions accumulate in a "version chain" and a background VACUUM process eventually reclaims them. If a long-running reconciliation transaction holds an old snapshot, it stalls garbage collection — a phenomenon called MVCC horizon stagnation — causing storage bloat and performance degradation.

SapixDB sidesteps this problem structurally. Because the strand is permanently append-only, there is no version chain to vacuum and no GC process to stall. Every historical state is a first-class citizen of the strand forever. The reconciliation engine can hold T_recon pinned for hours, and it costs nothing — no storage pressure, no GC interference, no operational risk.

The strand is the version history. It doesn't need a separate mechanism to maintain it.

Cryptographic Trust on Every Read

One thing that makes SapixDB's MVCC model unique in a banking context is that consistency is not just temporal — it is cryptographic.

Every record in the strand is:

Signed by its owning agent's Ed25519 private key
Hashed with BLAKE3 and chain-linked to its predecessor by parent hash
Encrypted at rest using AES-256-GCM, keyed from the SAPIX_MASTER_SEED via HKDF

When the reconciliation engine reads a record at T_recon, it isn't just getting a consistent snapshot — it is getting a snapshot whose integrity is mathematically verifiable. An auditor can take the hash and parent_hash from any returned record, recompute the chain, and prove that no record was altered between the time it was written and the time it was read.

For SOX, Basel III, and PCI-DSS compliance, this is not a nice-to-have. It is the difference between an audit trail and a trustworthy audit trail.

What the Reconciliation Output Looks Like

After paginating through all time_range results, the reconciliation engine has:

Category	Total (USD)
Debits in window	4,821,340.00
Credits in window	4,821,340.00
Delta	0.00

Balanced. Verified. Cryptographically anchored. And the payment processor never paused for a single millisecond.

The Mutant Watches Schema Evolution

One more layer worth understanding: as transaction volume grows, SapixDB's Mutant agent may observe that new field patterns are emerging in transaction payloads — perhaps a new fee_category field appearing across a majority of recent records. The Mutant proposes a schema evolution, tests it internally, and sends it to a human administrator for approval before anything changes in production.

This means the reconciliation engine never wakes up to find unexpected schema drift. Every schema change is deliberate, approved, and recorded permanently in the strand.

Start Building on the Right Foundation

MVCC isn't magic — it's a design choice about what you optimize for. Most databases optimize for write simplicity at the cost of read consistency complexity. SapixDB optimizes for both, simultaneously, by treating append-only immutability not as a constraint but as the source of truth.

If you are building financial systems, audit pipelines, compliance reporting, or any application where "what did the data look like at 11:58 PM on March 31st?" is a question that must have a verifiable answer — SapixDB's architecture was built precisely for that requirement.

Explore the full SapixDB documentation at sapixdb.com/docs and start with the quickstart to spin up your first agent. The strand is already keeping history. You just have to start writing to it.

DEV Community