LLM Best Practices

MVCC (Multiversion Concurrency Control)

1 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Multiversion concurrency control gives each statement a snapshot of the data instead of making readers wait for writers. In PostgreSQL’s words, “reading never blocks writing and writing never blocks reading”. The price is that an UPDATE or DELETE leaves the old row version behind, so vacuum must reclaim it and long-running transactions cause bloat.

Rules

  • Snapshot scope follows transaction-isolation: Read Committed takes a new snapshot per statement; Repeatable Read and Serializable keep one for the whole transaction.
  • Writers still block writers on the same row. Use row locks or optimistic-locking for read-modify-write conflicts.
  • Every update writes a new row version, and new index entries unless it is a HOT update (write-amplification).
  • Old versions live until no snapshot can see them, so an idle-in-transaction session blocks cleanup. Keep transactions short.
  • Implementations differ: PostgreSQL keeps old versions in the table, while MySQL InnoDB keeps them in undo logs.
SELECT xmin, xmax, * FROM accounts WHERE id = 1;  -- row-version metadata

Backlinks