Free learning / 40-minute collection consistency lab

Keep a changing collection intact across pages

Run actual SQLite pagination, expose repeated and missing records, and compare a changing list with a retained read snapshot. Define what completeness means for the receiving team.

Begin reading Download a working copy

No account needed. Use your own copy for writing and keep the original as a reference.

Engineering lab library / Run locally

Choose your next agent lab.

Start with the durable workflow reference, then choose the boundary you want to test. Open its walkthrough for setup, commands and expected observations.

Synthetic records. No provider key or model inference. Runs locally with the standard library.

Start here / Python 3.11+ · Standard library
Pagination and read snapshots · SQLite
Compare offset, keyset, upper-bound and snapshot traversal while a separate writer commits changes.
Python 3.11+ · Standard library · Temporary SQLite WAL database · No network
Open walkthrough Download file

40-minute integration lab · Python 3.11+ · Standard library · No account or network

An enterprise importer can receive successful responses and still miss a customer case. An agent can summarize only the pages it saw and report an apparently complete list. Before promising completeness, the engineer must define which records belong to the task and what happens while that collection changes.

This original exercise runs real SQLite queries and commits over fictional IDs in a temporary database. It does not call an API, authenticate a user, execute an agent or benchmark a production service. The diagram is an explanation; the downloaded program supplies the executed evidence.

You will keep: first predictions, actual ordered IDs, two independent population comparisons, one changed-page-size experiment and a receiving-team contract. You need to recognize a SELECT query and a sorted list. If those are new, start with Data 101.

1. Define which collection the receiver needs

Pagination divides a result into smaller portions. A page size limits each portion. A continuation tells the producer where to resume. These mechanics do not by themselves say whether later portions describe the same point in time.

For a live work queue, a changing view might be acceptable if operators understand its behavior. For a reconciliation export, the receiver may need a defined population at an agreed cutoff. Do not silently substitute one contract for the other. Record the filter, ordering, unique key, cutoff meaning, mutation policy and receiving purpose before counting rows.

The lab begins with four fictional records: 10, 20, 30, 40. IDs are unique, immutable integers. Page size defaults to two. A separate writer changes the table immediately after the first page. Each case starts from a fresh database; evidence never carries across cases.

Illustrative learning artifact

One event. Two transaction boundaries.

Source transactionCase update + outbox eventCommit both or neither

event-42 → delivery may repeat →

Receiver transactionEvent identity + taskDuplicate delivery → existing result

2. Follow a page through a source change

An offset counts positions to skip. In ORDER BY id LIMIT 2 OFFSET 2, the skipped positions belong to the query's visible collection. An insertion or deletion before that position can move the boundary.

Keyset pagination resumes after the last ordered key, such as WHERE id > 20 ORDER BY id LIMIT 2. It avoids shifting an offset, but later inserts behind the cursor may never be seen. Real nonunique sort fields need a unique tie-breaker and a corresponding continuation predicate. Changing sort values requires its own policy; this lab fixes immutable IDs.

A high-water bound retains an upper limit such as the initial largest ID. It can bound this traversal, but it does not preserve deleted rows, changed values or inserts below that limit. A snapshot retains a particular database view under an explicit transaction/isolation contract. Here the snapshot case keeps one SQLite reader transaction open in write-ahead logging (WAL) mode while a second connection commits changes.

UNDERSTAND THE SYSTEM

A page boundary is not a frozen collection

Follow five independent SQLite experiments with a page size of two. The diagram explains the authored cases; run the Python companion to inspect actual queries and rows. Each case starts again with IDs 10, 20, 30 and 40.

FOLLOW THE WORK

Watch one example, step by step.

5 authored stepsYou control the pace

01 / EXPLORE THE PARTSControls loading · written explanations below

Direction of information or workReturn / feedback

Read the full worked example

01
1 · Offset insert
After 10 and 20, insert 5. OFFSET 2 now starts at 20, so the received sequence is 10, 20, 20, 30, 40. ID 20 repeats; newly inserted 5 is never read. Every SELECT can succeed while the traversal is inconsistent.
DEFAULT RUN: Received 10, 20, 20, 30, 40
02
2 · Offset delete
Delete 10 after page one. OFFSET 2 now skips past 30 and returns 40. The receiver gets 10, 20, 40: it holds a row that was read before deletion and misses 30, which is still present. Deduplication cannot restore that missing row.
DEFAULT RUN: Received 10, 20, 40
03
3 · Keyset insert
Insert 15 and 50 after reading 10 and 20. WHERE id > 20 continues through 30, 40 and 50. It avoids an offset shift but never returns the newly inserted 15 behind its cursor. No duplicates does not mean a frozen population.
DEFAULT RUN: Received 10, 20, 30, 40, 50
04
4 · Upper bound
Retain initial MAX(id)=40 and add id <= 40 to the keyset query. ID 50 stays outside the bound, but inserted 15 is still behind the cursor. The received IDs happen to match the initial set in this case; an upper bound alone does not freeze membership or values.
DEFAULT RUN: Received 10, 20, 30, 40
05
5 · Read snapshot
The reader begins a transaction and establishes its snapshot before the writer inserts 15 and 50 and deletes 30. Its later pages still return 30 and 40. Ending that transaction exposes the changed set to a fresh read. This is a consistent initial view, not the latest view.
DEFAULT RUN: Snapshot 10, 20, 30, 40

Read the connections in plain English

01
Population contract → First page
Retain the original expected population independently of the returned rows.
02
First page → Concurrent writer
The writer commits only after the first page has been observed.
03
Concurrent writer → Next query
Determine whether the next read sees the changed collection or an existing snapshot.
04
Next query → Received records
Read the sequence and differences, not just a successful exit status.
05
Received records → Receiving decision
A complete initial snapshot and a current live view answer different questions.

Check your understanding: An export has no duplicate IDs and stops at the initial maximum. Is it a snapshot?

No. The high-water case misses an insertion below the cursor. An upper bound does not preserve rows, values or permission changes. Define the required population and verify the receiving database or export service's actual consistency contract.

3. Predict and run five independent cases

← Back to lab library Download Pagination and read snapshots · SQLite ↓

Download Pagination consistency · Python. Save it in a new local practice folder and run:

Code example

python3 fde-pagination-consistency.py all

If your python3 is older than 3.11, use an approved Python 3.11+ interpreter. No packages are installed. The program creates and removes its own temporary database; it never accepts a production database path. WAL must be available in that temporary directory.

Before running, write the IDs you expect to receive. Then compare your prediction with received_ids, keeping the duplicate occurrence rather than cleaning it away.

Case	Writer after the first page	Default received sequence	What to inspect
`offset-insert`	Insert 5	10, 20, 20, 30, 40	Duplicate 20; final-current 5 missing
`offset-delete`	Delete 10	10, 20, 40	Still-present 30 missing
`keyset-insert`	Insert 15 and 50	10, 20, 30, 40, 50	New 50 read; new 15 missed
`high-water`	Insert 15 and 50	10, 20, 30, 40	Initial IDs match here; current 15 is still missed
`snapshot`	Insert 15 and 50; delete 30	10, 20, 30, 40	Initial snapshot retains 30; fresh view differs

Run one case for a smaller report:

Code example

python3 fde-pagination-consistency.py snapshot

Inspect pages: each entry keeps the actual SQL, parameters, returned IDs and reader transaction state. mutation records the writer statements and committed IDs. The loop ends with an empty SQL page in this small bounded fixture. That is this program's termination rule, not a universal API rule: a service may return a short or empty page with a continuation token. Follow the receiving API's documented completion signal.

4. Compare against two different populations

initial_ids is the independently captured starting set. final_current_ids is the committed set after the writer changes it. Neither reference is rewritten to match the received result.

duplicate_ids finds repeated IDs in the received sequence.
missing_from_initial_ids finds starting records not received.
newly_read_ids finds received IDs outside the starting set.
final_current_ids_missed finds final-current records absent from the received set.
fresh_reader_ids shows what that reader sees after any retained transaction ends.

In offset-delete, ID 30 is missing from both reference populations. ID 10 was actually read before it was deleted. In snapshot, ID 30 is legitimately present in the agreed initial view, while final-current IDs 15 and 50 are absent. A current-set mismatch alone is therefore not a defect in an initial-snapshot contract. State the requested population before choosing a judgment.

The high-water case matches the initial IDs under these particular inserts. That observation does not prove a general snapshot guarantee. Discuss a deletion of an unread record or a value update: an upper bound contains no historical copy to recover them.

5. Change one boundary and explain the result

Keep your original output. Predict the offset deletion with a page size of one, then execute:

Code example

python3 fde-pagination-consistency.py offset-delete --page-size 1
python3 fde-pagination-consistency.py snapshot --page-size 1

The mutation still occurs after page one. The offset case now reads 10, 30, 40 and misses 20; the snapshot still retains 10, 20, 30, 40. Explain why the missed ID moved: changing page size also changed how much was read before the same mutation. Do not call it a performance comparison; no load or latency is measured.

An optional next experiment uses --page-size 4. Page one already contains all initial IDs. Predict which late inserts can be returned by each continuation. Preserve those outputs separately; a passing large-page example does not repair the smaller-page failure.

6. Give the receiving team a reproducible contract

Work in pairs if a reviewer is available. Each learner writes a first prediction before seeing the other account. The builder runs one case and hands over the exact script, command and output. The receiver chooses a different case, finds its query and population difference, then reproduces it without the builder taking over. Record any assistance. A solo run remains a solo run.

Text example

Receiving purpose and required population:
Script revision or exact retained copy:
Original prediction / actual command / observed IDs:
Initial set / final-current set / which reference applies:
Duplicate or missing record and the SQL boundary causing it:
Changed page size / preserved original / revised explanation:
Receiving action actually attempted / help received:
Unresolved contract question / accountable owner / next evidence:

The API owner defines filter binding, ordering, continuation expiry and retry behavior. The data owner defines the cutoff and which records belong in the export. The receiving engineer verifies the real consumer's traversal and error handling. The pod lead coordinates the unresolved decision and next check; a local lab result grants none of these production permissions.

A production page token should be treated as opaque by the client; it is not permission to access records. Enforce current authorization independently on every request. If access changes during a long export, the security and data owners must resolve what may be delivered. Do not expose a stale authorized snapshot merely because pagination is consistent.

Holding a database transaction across disconnected HTTP requests is not a deployment design supplied by this lab. A real service may need a bounded export job, materialized result, documented token lifetime or another supported snapshot mechanism. Establish resource limits and cancellation, and test the chosen database's actual isolation. Continue in Enterprise integration to combine this with consumer compatibility and event reconciliation.

7. Explain it without the diagram

Explain: Why can a successful SELECT produce a wrong export? Name the population, page boundary and concurrent change, then show the IDs.

Debug: A receiver removed duplicates and still missed a case. Which evidence would expose the loss? Keep the original ordered sequence and compare against an independently defined set.

Design: An API offers an opaque token and an estimated total. What is still unknown? Define consistency, completion, scope binding, expiry, retry, deleted records and current access before promising a complete export.

Keep the initial answer, revised explanation and help received. This exercise demonstrates local observations, not production readiness or client-placement qualification.

Sources

Primary references checked 27 October 2026. The exercise and fictional records are original LockedIn Labs material.