Skip to content
KEDBYTE
Site navigation
How Data Works
Chapter
25

Versions, Snapshots and Optimistic Concurrency

Part D · Correct Changes|3,895 words|about 17 min read|Volume D

25.0 What this chapter gives you#

  1. A record can have more than one relevant version: the engine’s internal versions, a saved business revision and the copy someone is editing on a screen. Confusing these versions creates subtle bugs.
  2. You will trace snapshot visibility, understand why old readers affect cleanup, implement an explicit revision check and choose a conflict policy that does not silently discard someone’s work.
  3. The examples introduce a new draft quotation and do not change the agreed historical prices in O-1042 or O-1043. Engine version metadata is not treated as an everlasting business audit log.

25.1 Old and new row versions#

25.1.1 PLAIN — in simple words#

  1. When a database changes a record, an earlier version may remain available for readers that still need it. The engine can decide which version each reader is allowed to see.
  2. This helps readers and writers coexist without requiring every reader to wait for every change. It does not mean that all old versions are kept forever.
  3. A business history has a different purpose. If the shop must explain who approved an agreed price, it needs deliberately recorded history and retention rules rather than assuming the database’s temporary internal versions will answer later.

25.1.2 PLAIN — a picture in your head#

  1. Imagine a shared document where a reader can finish the edition they opened while an editor prepares a newer edition for later readers.
  2. The printing office keeps old sheets only as long as its current readers require them. An archive keeper has a separate job if editions must be preserved for years.
  3. Where the comparison breaks: a database does not necessarily copy an entire document for each edit. Physical version storage and visibility checks vary by engine, and cleanup can reclaim old storage.

25.1.3 PLAIN — a worked example#

  1. In a new draft example, D-EDIT has quantity 3 at business revision 7. A transaction changes it to quantity 4 at revision 8.
  2. An older permitted snapshot may still read the earlier quantity while a later snapshot reads the committed replacement. The application revision identifies the edit state chosen by the application; the engine may use other internal metadata to decide visibility.
  3. After old snapshots are no longer relevant, the engine may reclaim obsolete versions. A future request for “the reason revision 7 existed” cannot rely on that reclaimed physical version.
  4. If reasons and approvals matter, record them in an explicit history model with a defined scope and retention policy.

25.1.4 PLAIN — what is really happening inside#

  1. Multi-version concurrency control, or MVCC, associates versions with transactional visibility information. Readers evaluate visibility according to their snapshot rules.
  2. An UPDATE can therefore create storage and cleanup work beyond changing one displayed number. Index maintenance and space reuse also depend on the engine’s implementation.
  3. MVCC is not one universal algorithm. Use the general concept to ask useful questions, then consult the actual engine for version layout, cleanup and conflict behaviour.

25.1.5 TECHNICAL — the engineer’s version#

  1. PostgreSQL uses MVCC so statements can see appropriate snapshots while concurrent transactions modify data. Its internal tuple metadata participates in visibility, but it is not a substitute for application-level history. [S119] [S124]
  2. PostgreSQL’s ctid identifies a physical row-version location and can change. Transaction identifiers also have lifecycle and wraparound considerations. Neither should casually replace an immutable business key or an explicitly designed revision protocol. [S124] [S123]
  3. An application revision should have a documented domain, increment rule and generation boundary. Its correctness depends on every relevant writer advancing or otherwise respecting it.

25.1.6 WORDS — remember these#

  1. MVCC: let readers use suitable versions while changes occur — multi-version concurrency control using versioned data and transactional visibility rules. Business revision: the edit state named by the application — an explicit version value used for conflict detection or documented history. Physical row location: where one stored version currently resides — engine metadata such as PostgreSQL’s ctid, not an enduring logical identity.

25.2 Snapshot visibility#

25.2.1 PLAIN — in simple words#

  1. A snapshot decides which committed work from other transactions belongs in a reader’s view. “Newest record on disk” is not necessarily the version that reader should receive.
  2. A reader can therefore obtain an older valid version without receiving a corrupt or random answer. The answer must be interpreted under the reader’s observation contract.
  3. A screen’s age is another matter. After the database returns a result, the browser can keep displaying it indefinitely. Database snapshot guarantees do not continuously refresh every previously returned screen.

25.2.2 PLAIN — a picture in your head#

  1. A visitor enters an exhibition arranged as it was at opening time. New exhibits can be prepared for later visitors without rearranging the earlier visitor’s tour halfway through.
  2. Once the visitor leaves with a photograph, the exhibition staff no longer control how long they keep using that photograph.
  3. Where the comparison breaks: snapshots also interact with a transaction’s own writes and conflict checks. They are not merely wall-clock photographs, and not every engine starts them at BEGIN.

25.2.3 PLAIN — a worked example#

  1. Reader R establishes a retained snapshot while D-EDIT is at revision 7. Writer W commits revision 8. R may still read revision 7 under the retained-snapshot contract, while a new reader N sees revision 8.
  2. R’s user later submits an edit based on revision 7. The server should not assume the submitted revision is current merely because it once came from a valid database read.
  3. A write protocol can require “apply this change only if the current business revision is still 7.” If revision 8 now exists, return a conflict and preserve the user’s proposed change for review.
  4. This separates reading an old valid view from overwriting newer work without permission.

25.2.4 PLAIN — what is really happening inside#

  1. The snapshot belongs to the database operation or transaction, not to every future action of the client that received its result.
  2. A revision returned with a resource is therefore useful as a precondition for a later update. It connects the update to the state on which the editor actually based the change.
  3. A revision supplied by a client is still untrusted input. Validate its type and scope, and authorize the action independently before treating it as a precondition.

25.2.5 TECHNICAL — the engineer’s version#

  1. PostgreSQL Read Committed and Repeatable Read establish and reuse snapshots differently, as detailed in Chapter 23. An ordinary earlier SELECT does not reserve its observed version for a later independent HTTP request. [S66]
  2. SQLite WAL readers can retain a snapshot while another connection commits changes. A reader attempting to upgrade a stale snapshot to a writer can encounter SQLITE_BUSY_SNAPSHOT; restarting the transaction is different from overwriting the newer state. [S54] [S77]
  3. Treat a client edit as (resource identity, expected revision, proposed change), plus separately established authority. Do not use the revision field as a password or authorization token.

25.2.6 WORDS — remember these#

  1. Visible version: the row state allowed by a reader’s snapshot — a version satisfying the engine’s transaction-visibility rules. Edit precondition: the state an update expects to replace — a condition checked at the write boundary to detect intervening changes. Stale client copy: an earlier result still held outside the database — data whose original read validity does not guarantee current edit validity.

25.3 Cleanup and long readers#

25.3.1 PLAIN — in simple words#

  1. Keeping every internal version forever would consume growing amounts of storage. Engines need to reclaim versions that no relevant reader or recovery rule still requires.
  2. A long-running transaction can delay that cleanup because its older view may still need versions newer readers no longer use.
  3. The operational cost is easy to miss: the reader may appear quiet while keeping old storage relevant. A dashboard showing only active query duration can overlook an idle open transaction.

25.3.2 PLAIN — a picture in your head#

  1. A library keeps an old edition on a return trolley because one registered reader still has a claim to consult it. Hundreds of newer editions cannot be fully cleared while that claim remains open.
  2. Closing the reader’s session can allow the cleanup process to proceed under its rules.
  3. Where the comparison breaks: version cleanup is not simply deleting the oldest timestamp. Visibility horizons, replication and engine-specific transaction metadata can affect what is safe to reclaim.

25.3.3 PLAIN — a worked example#

  1. Suppose a synthetic workload replaces 50,000 rows per hour, each producing 200 bytes of obsolete row-version payload in a simplified accounting model. That is 10,000,000 bytes per hour before indexes, page overhead and reuse.
  2. If a long reader prevents relevant cleanup for six hours, the simplified payload accumulation is 60,000,000 bytes, about 60 MB decimal. This is a planning calculation, not a measured PostgreSQL storage prediction.
  3. Real storage growth must be measured because row layout, updates, indexes, fill space and vacuum behaviour change the result.
  4. The lesson is not that every long reader causes exactly 60 MB of bloat. It is that reader lifetime can become a storage and maintenance input.

25.3.4 PLAIN — what is really happening inside#

  1. PostgreSQL vacuuming reclaims space from obsolete row versions when it is safe to do so and performs other essential maintenance, including transaction-ID-related work.
  2. Ordinary VACUUM commonly makes space reusable inside the table rather than promising that the operating-system file immediately shrinks by the same amount.
  3. Cancelling a long reader is an operational decision with consequences. Identify its owner, purpose and restart/recovery behaviour before treating it as disposable.

25.3.5 TECHNICAL — the engineer’s version#

  1. PostgreSQL’s routine vacuuming documentation covers dead-row-version cleanup, planner statistics and transaction-ID wraparound protection. Autovacuum is a maintenance system, not a guarantee that every workload remains healthy without monitoring. [S123]
  2. Distinguish reusable free space, dead tuples, table-file size and live logical data size. They are related but not interchangeable measurements.
  3. Monitor transaction age and relevant maintenance progress under the chosen engine. Avoid prescribing a global timeout or aggressive cleanup command without workload evidence and a reviewed rollback or restart plan.

25.3.6 WORDS — remember these#

  1. Obsolete version: an earlier stored state no longer needed by relevant readers — a version eligible for reclamation under the engine’s visibility and maintenance rules. Vacuuming: maintenance that reclaims and manages old storage — PostgreSQL’s process for handling obsolete tuples and other required maintenance tasks. Cleanup horizon: the boundary beyond which old state can be reclaimed — the visibility or retention limit constraining safe version removal.

25.4 Version columns#

25.4.1 PLAIN — in simple words#

  1. A version column gives an editable record a counter or other explicit revision marker. The server advances it whenever a change relevant to the editing contract is accepted.
  2. A client sends the revision it originally read. The server compares that expectation with the current revision before applying the proposed replacement.
  3. The counter works only when its rules are followed consistently. A maintenance script that changes the record without advancing the revision can make an old client appear current.

25.4.2 PLAIN — a picture in your head#

  1. A quotation has an edition number printed in its corner. Dev’s correction says, “This change is based on edition seven.”
  2. If Mira has already approved edition eight, the desk does not silently paste Dev’s old replacement over it. The difference must be resolved.
  3. Where the comparison breaks: an edition counter can wrap, reset or be reused after deletion. A complete protocol must define the resource’s lifetime as well as its current number.

25.4.3 PLAIN — a worked example#

  1. D-EDIT starts with quantity=3 and revision=7. Mira submits quantity=4 with expected revision=7. The accepted update sets quantity=4 and revision=8.
  2. Dev then submits quantity=5 with expected revision=7. The revision condition no longer matches, so his update affects zero rows and must not be reported as a successful replacement.
  3. The response can return a conflict and the current revision, subject to access policy. Preserve Dev’s proposed quantity so he can compare and intentionally resubmit against the newer state.
  4. This policy rejects stale replacement; it does not decide whether quantity 4 or 5 is the better business choice.

25.4.4 PLAIN — what is really happening inside#

  1. The revision check and increment belong in the same protected write. Reading the revision first and then performing an unconditional replacement reintroduces the gap.
  2. Define which changes advance the revision. A counter for editable quotation fields might intentionally ignore a background “last viewed” timestamp, but that decision must be explicit.
  3. Bound the revision domain and handle exhaustion rather than wrapping silently into a previously used value. Large counters make exhaustion unlikely; they do not remove the need for a defined limit.

25.4.5 TECHNICAL — the engineer’s version#

  1. A typical pattern is UPDATE ... SET quantity=?, revision=revision+1 WHERE id=? AND revision=?. The uniqueness of id, bounded revision domain and affected-row check are part of the protocol.
  2. Add a generation identifier when a logical identifier can be deleted and recreated. Compare (resource_id, generation_id, revision) so a new object’s revision 1 is not confused with the old object’s revision 1.
  3. Do not substitute PostgreSQL xmin or ctid casually for this application contract. Their implementation purpose and lifecycle differ from a deliberately stable business revision. [S124] [S123]

25.4.6 WORDS — remember these#

  1. Version column: a field naming the accepted edit state — an application-managed revision value checked and advanced by relevant writes. Generation identifier: distinguish separate lifetimes of a reused key — an immutable identity component that changes when a resource is recreated. Revision exhaustion: no unused value remains in the chosen domain — a boundary requiring an explicit policy rather than silent counter reuse.

25.5 Compare-and-set#

25.5.1 PLAIN — in simple words#

  1. Compare-and-set means “make this change only if the current state still matches my expectation.” It turns an old observation into an explicit precondition instead of an unconditional command.
  2. Comparing only the displayed value may miss intervening changes that return to the same value. A revision can reveal that history of accepted edits even when the final quantity looks unchanged.
  3. A successful comparison still does not authenticate the caller, validate every business rule or identify a repeated request. Those are separate controls.

25.5.2 PLAIN — a picture in your head#

  1. Dev leaves a note saying, “Change the sign only if it is still the edition I saw.” That is stronger than saying, “Change it if it still happens to display the same number.”
  2. The sign could have changed twice and returned to that number while the edition advanced.
  3. Where the comparison breaks: a counter records only changes that the protocol includes. It is not an omniscient record of every physical event or bypass mutation.

25.5.3 PLAIN — a worked example#

  1. Start with quantity=3, revision=7. An accepted edit changes it to quantity=4, revision=8. Another accepted edit changes it back to quantity=3, revision=9.
  2. A stale client expecting quantity=3 passes a value-only comparison even though two edits intervened. This is the ABA pattern: A became B and later became A again.
  3. A stale client expecting revision=7 fails against revision=9. The explicit revision detected intervening protocol-respecting edits.
  4. If the record was deleted and recreated with revision=7 again, the revision alone could mislead the client. A fresh generation identifier separates the lifetimes.

25.5.4 PLAIN — what is really happening inside#

  1. The write predicate encodes the expectation; the database decides whether the current target satisfies it at the protected write boundary.
  2. On failure, avoid guessing the reason from zero affected rows alone. Missing identity, different generation, stale revision or a scope condition can all make the predicate fail.
  3. After a successful write whose response is lost, replaying the old expected revision will usually conflict. That conflict does not prove the original attempt failed. Stable request identity is needed to resolve the original outcome.

25.5.5 TECHNICAL — the engineer’s version#

  1. The educational compare-and-set helper uses bound values, a complete identity, a positive bounded proposed quantity and an expected revision. It owns a transaction and accepts exactly one changed row under its schema.
  2. The protocol protects stale replacement of that resource. It does not enforce unrelated cross-row limits or prevent authorized users from intentionally making undesirable but permitted changes.
  3. Separate optimistic concurrency from idempotency: the former detects conflicting edit state; the latter identifies repeated attempts at the same intent and resolves their outcomes. Chapter 26 combines these concerns where appropriate. [S126]

25.5.6 WORDS — remember these#

  1. Compare-and-set: write only when the expectation still matches — an atomic conditional mutation based on an expected value, revision or generation. ABA problem: a value changes and returns, hiding intervening work — a limitation of equality-only checks that do not identify the relevant state history. Optimistic concurrency: detect a conflict when saving rather than holding a long edit lock — a protocol that validates an earlier observation at the write boundary.

25.6 Choosing a conflict policy#

25.6.1 PLAIN — in simple words#

  1. Detecting a conflict is not the end of the user experience. The application must decide whether to reject, ask for review, merge safely or retry a newly computed operation.
  2. Different data needs different treatment. Two independent additions to a counter can sometimes combine naturally; two replacements of a delivery address may require a person to choose.
  3. “Last write wins” is a policy that discards something when changes compete. It can be appropriate for selected low-risk data, but it should never be mistaken for the absence of data loss.

25.6.2 PLAIN — a picture in your head#

  1. Two editors correct different spelling errors in a document; a careful merge may keep both. Two editors choose different delivery destinations; combining the street from one and the city from the other can create a destination neither intended.
  2. The merge rule must understand the meaning of the fields, not just avoid a software error.
  3. Where the comparison breaks: automatic text merging can still produce a syntactically valid but semantically wrong result. Business invariants need explicit validation after any merge.

25.6.3 PLAIN — a worked example#

  1. Mira changes a draft quantity from 3 to 4. Dev changes the same draft’s delivery note, both from revision 7.
  2. A whole-record revision policy rejects Dev’s stale update even though the fields differ. This is conservative and simple, but may create avoidable review work.
  3. A field-aware merge can preserve both only if the application checks that the changes are independent under its rules. If the note says “pack exactly three,” it depends on quantity and is not independent after all.
  4. Record the chosen resolution and resulting revision. Do not hide an automatic overwrite behind a generic “synchronised” message.

25.6.4 PLAIN — what is really happening inside#

  1. Conflict granularity is a design trade-off. One revision per document is easier to reason about; finer-grained revisions may reduce false conflicts while increasing metadata and invariant complexity.
  2. A retry of a commutative operation, such as adding a separately identified increment, differs from retrying an absolute replacement. Duplicate delivery still needs its own identity handling.
  3. Preserve the user’s unsaved proposal and show the relevant current state only within their permissions. Conflict messages must not become a way to reveal another tenant’s records.

25.6.5 TECHNICAL — the engineer’s version#

  1. Specify the conflict response schema, retry eligibility, merge rules, audit requirements and authorization checks. A version mismatch should have a stable API meaning rather than being flattened into an unexplained server error.
  2. Tests should cover two stale editors, ABA, identity recreation, bypass-writer assumptions, response loss, malformed revision and unauthorized scope. The provided lab exercises a trusted local data protocol, not a complete authenticated API.
  3. Evaluate conflict rate and user resolution cost alongside throughput. Reducing visible conflicts by silently overwriting edits is not a correctness improvement.

25.6.6 WORDS — remember these#

  1. Conflict resolution: decide what to retain after incompatible changes — a policy for rejection, reviewed replacement, safe merge or recomputation. Merge granularity: the unit at which changes are compared — a document, row, field or operation boundary used by the conflict protocol. Last-write-wins policy: accept the selected later replacement — a conflict rule that can discard competing state and requires a defined ordering basis.

25.97 Practice and worked answers#

  1. Separate version purposes. Can internal MVCC versions replace an approval history? Answer: no. Cleanup and implementation lifecycles differ from a deliberate business-history and retention contract.
  2. Name a stable key. Should PostgreSQL ctid identify a customer’s permanent account? Answer: no. It identifies a physical row-version location, not an immutable business identity.
  3. Trace an edit conflict. Quantity 3/revision 7 becomes 4/revision 8. A client still expects revision 7. Answer: the conditional replacement should fail without overwriting revision 8.
  4. Find ABA. Quantity changes 3 → 4 → 3. Answer: a quantity-only comparison can miss both edits. A consistently advanced revision distinguishes the states.
  5. Find identity reuse. A deleted key is recreated with the old revision number. Answer: include a new generation identifier or otherwise prohibit ambiguous lifetime reuse.
  6. Interpret a lost response. A saved edit advances revision 7 to 8, but the caller receives no reply. Answer: retrying expected revision 7 can conflict even though the original succeeded. Resolve request identity rather than treating the conflict as proof of original failure.
  7. Calculate simplified old-version payload. Fifty thousand changes/hour at 200 bytes for six hours. Answer: 60,000,000 bytes before overhead and reuse; this is not a measured engine-size forecast.
  8. Review a merge. One user changes quantity and another changes a note saying “pack exactly three.” Answer: the fields have a semantic dependency. A blind field merge can violate the intended meaning.

25.98 Common wrong ideas#

  1. Wrong: MVCC means every historical row is kept forever. Right: internal versions are subject to visibility and cleanup rules.
  2. Wrong: a physical row location is a permanent record identity. Right: storage location and business identity serve different purposes.
  3. Wrong: an earlier valid read authorizes a later overwrite. Right: later writes need current preconditions and independent authority checks.
  4. Wrong: VACUUM always shrinks the table file immediately. Right: ordinary maintenance often makes internal space reusable instead.
  5. Wrong: comparing the current value detects every intervening edit. Right: ABA can return to the same value.
  6. Wrong: a revision counter works even when some writers ignore it. Right: every relevant mutation must follow the revision contract.
  7. Wrong: optimistic concurrency is idempotency. Right: stale-state detection and repeated-intent resolution solve different problems.
  8. Wrong: automatic merging is always more correct than rejecting. Right: the merge must preserve the meaning and invariants of the data.

25.99 Chapter summary in 20 lines#

  1. Internal row versions, business revisions and client copies are different concepts.
  2. MVCC uses versions and visibility rules to coordinate readers and writers.
  3. An older visible version can be valid under a retained snapshot.
  4. Internal version retention is not an everlasting business audit log.
  5. Physical tuple locations are not permanent logical identities.
  6. A client’s earlier read does not reserve the record for a later edit.
  7. Return explicit revisions when later writes need stale-state detection.
  8. Cleanup reclaims obsolete versions when relevant visibility rules permit it.
  9. Long readers can delay reclamation and affect maintenance cost.
  10. Reusable space and operating-system file size are different measurements.
  11. A version column requires a defined increment and exhaustion policy.
  12. Every relevant writer must follow the same revision contract.
  13. Compare the expected revision and mutate the record in one protected operation.
  14. Affected-row failure must not be reported as successful replacement.
  15. ABA can hide intervening changes from value-only comparisons.
  16. Generation identifiers distinguish separate lifetimes of a reused key.
  17. A lost response can make a successful original edit appear as a later conflict.
  18. Request identity resolves retry outcomes separately from edit concurrency.
  19. Choose rejection, review or merge according to the data’s meaning.
  20. Preserve user proposals and validate invariants after conflict resolution.

Return to contents