MySQL basics and isolation audit, issue #33
This is the existing-content semantic evidence audit under #31. The baseline fixed point is 5aa49d981efa1e5902a7c0622501bc827c1d1b6d, which includes the accepted glossary and #32 handoff. This revision also reconciles the inventory and attribution findings from review of implementation commit bb5182a363d9f86e69ba509e18e7603e663b3ab7.
Coverage
The assigned surface is all nine MySQL basics/isolation lessons, five basics Scenarios, sixteen isolation Scenarios, their 21 generated Transcripts and timelines, and their exact ledger records and llms sections. 33-claims.tsv inventories original and current occurrences, including headings, opening/closing prose, every catalog cell, SQL/setup, assertions, claims, notes, comments, timeline labels, generated results, and metadata. There are 1,127 original and 1,224 current units, 2,351 total, across 53 assigned files. These are coverage units, not distinct universal database guarantees. Completeness is checked by exact source occurrence identity and text, including the final column of every table, rather than by counts alone.
33-evidence-registry.json supplies the source, operations, levels, conditions, official manual sections, assertions, and mirror identity for each registry reference. A YAML occurrence retains its exact field identity, such as scenarios/mysql/02-isolation/read-skew.yaml#steps[3].expect[0].balance. Generated occurrences retain the complete Scenario path and the exact line in the part or llms section. Thus repeated statements and the separate RU/RC, RC/RR, and SERIALIZABLE/autocommit schedules are not collapsed into one anonymous claim. Baseline references are against the baseline fixed point; current references are against the source tree containing this inventory.
Source fields are authoritative. The 919 original and 1,013 current source units give each YAML scalar and lesson paragraph/cell its own identity. Each revision additionally inventories all 21 complete Transcript/timeline ranges, all 21 exact llms section ranges, and every field in its 21 ledger records (166 original and 169 current fields). Generated ranges mirror the individual source-field claims and asserted SQL/results; they are not anonymous replacement claims or independent evidence of all schedules. Ledger claims also supply built descriptions and TechArticle metadata. Raw runtime logs, browser captures, and the full checked-commit QA report remain in temporary storage. The inventory, dispositions, and cross-surface corrections are durable so later tickets do not depend on that storage.
Retained YAML fields map to the corresponding ordered operation, session, transaction phase and observation before text equality is considered. A repeated SQL statement or result in another step is not a substitute. Replaced fields name their corresponding current occurrence; removed fields name the step from which they were removed. Table dispositions follow row subject and column meaning, including the split of the combined G2-item/G2 row.
For YAML steps, scope records connection state immediately before the named step; state_after records state after that scheduled step. Both revisions use this convention in the inventory, and the current registry uses the same convention. A SET operation therefore has its previous setting in scope and its new setting in state_after. BEGIN consumes the next-transaction level, and COMMIT, full ROLLBACK or deadlock rollback ends that transaction. Outside an open transaction the recorded level is the effective next level. With autocommit=0, implicit transaction mode persists across transaction boundaries. A blocked dispatch remains pending; its later success step asserts completion. These labels describe the ordered schedule, not an independently observed server-state trace.
Semantic dispositions
| Decision | Subject | Supported disposition |
|---|---|---|
| A1 | Transactional boundary | Scoped to InnoDB table writes on 8.4.11; all assigned CREATE TABLE setup explicitly selects InnoDB. The CHECK error does not itself undo bob's earlier credit: A's intermediate SELECT asserts 200, then explicit ROLLBACK restores 50. Nontransactional writes, DDL, crashes, network faults, and external effects are outside the executed boundary. MySQL 8.4 section 15.3.1 documents nontransactional exceptions. |
| A2 | Autocommit and visibility | Successful standalone statement commits are distinguished from errors and explicit transactions. The Scenario asserts autocommit=1 and the pinned default isolation. B's standalone SELECTs take fresh snapshots; an already established RR snapshot need not refresh. Section 17.7.2.2 documents autocommit=0 and connection settings. |
| A3 | Error scope | 1062 without IGNORE preserves earlier/later INSERTs through COMMIT. Deadlock transaction rollback and timeout statement rollback are separate contracts, with innodb_rollback_on_timeout=ON explicitly changing timeout scope. Section 17.20.5 supports these error cases. Missing-COMMIT-response advice is separately marked as an inference from section 15.3.1: successful commit with a lost reply and COMMIT never reaching the server have different outcomes that the missing reply cannot distinguish. No network execution is claimed. Removed blanket comparisons that describe every PostgreSQL error/command alike. |
| A4 | Savepoints and DDL | Added intermediate branch-state and released-savepoint assertions. Section 15.3.4 documents deleted later savepoints, release, full-end cleanup, reused names, and retained in-memory row locks, including the inserted-row undo exception. Section 15.3.3 documents implicit-commit DDL, START TRANSACTION ending prior work, and temporary-table create/drop exceptions. These contracts are not presented as executions of every command. |
| A5 | Consistent snapshots and current operations | Snapshot timing is the first consistent table read, not every first query or BEGIN. The expanded stable-snapshot schedule asserts a pre-first-read commit, excluded later commit/insert, visible own write, and fresh post-COMMIT view. Current UPDATE/DELETE and locking reads are distinguished from consistent SELECTs. Removed “no 40001 errors” because InnoDB deadlocks use that SQLSTATE. Sections 17.7.2.1 and 17.7.2.3 support the operation limits; 15.3.1 supports WITH CONSISTENT SNAPSHOT at RR. |
| A6 | READ COMMITTED and rechecks | Existing value, phantom, skew, wait, and zero-affected-row observations remain asserted. Added the successful resumed UPDATE count. Semi-consistent UPDATE qualification and re-read are documented by 17.7.2.1's READ COMMITTED subsection. Removed all-SELECT/all-statement rules, universal no-wait promises, and unmeasured performance preferences. Metadata locking is documented separately in 10.11.4. |
| A7 | Lost updates | Both RC and explicit RR capture/assert 100 and commit stale writes ending at 110. Removed blanket ORM claims and “only one isolation/repair can fix this” statements. Structural repair examples are linked to their existing Scenarios, with writer/decision boundaries and version-conflict handling. SERIALIZABLE protection for this exact lost-update race is explicitly an inference from retained read locks, not an executed SERIALIZABLE lost-update schedule. |
| A8 | SERIALIZABLE and rule preservation | Corrected the on-call classification to G2-item. The schedule asserts detection enabled, B's 1213, one remaining doctor, and a fresh B count-and-decline attempt. Added standalone autocommit=1 and autocommit=0 SELECT schedules to assert nonlocking versus waiting behavior. Victim choice, immediate detection, retry success, all-level exclusivity, and measured cost are not promised. Rule protection is a marked derivation requiring serial rule preservation and writer participation, supported by 17.7.2.1/17.7.2.4/17.7.3. |
| A9 | Dirty writes, drafts, cycles and OTV | Dirty write is an overwrite before the prior writer ends, not a mixed final state. Narration now scopes the observed wait/final prices. G1b's RC repeat reads 110 during draft 555, not an unexecuted 100-to-110 repeat. The RU dirty-cross-read cycle now commits both transactions and asserts their final values, then resets before its separate RC schedule. The RC two-old-read cycle is not serial-equivalent. OTV text is limited to the asserted split-view operation and does not promise immutable committed values or one snapshot across RC statements. Application actions are not invented from SELECT results. |
| A10 | Complete catalog | Every level cell uses D, M, a marked derivation, or “Not executed here”. G2-item and predicate G2 have separate rows. No new-row current-read phantom execution or full Hermitage coverage is claimed. Own writes, mixed current/consistent reads, participating transaction boundaries, autocommit exceptions and configuration-sensitive error scope are explicit. Universal exclusions use the exact consistent-read/lock contracts and their derivation, not deterministic generation alone. |
Official-manual support
The linked MySQL 8.4 sections were read during this audit. The section identifiers below make the registry's contract support reviewable without treating an entire manual as an unexplained citation:
| Section | Contract used |
|---|---|
| 17.20.5, InnoDB Error Handling | Statement versus transaction rollback; duplicate-key without IGNORE; deadlock; configured timeout scope. |
| 17.7.2.2, autocommit | Successful individual statements; explicit BEGIN; autocommit=0; transaction end. |
| 15.3.1, transaction commands, 15.3.3, implicit commits, 15.3.4, savepoints | Nontransactional rollback limits; snapshot modifier; DDL/temporary-table/nesting exceptions; savepoint lifecycle and lock retention. |
| 17.7.2.1, isolation levels, 17.7.2.3, consistent reads, 15.3.7, SET TRANSACTION | RR default; fresh RC/first-consistent-read RR snapshots; own writes; current DML; UPDATE semi-consistent qualification; RU inconsistent reads; SERIALIZABLE/autocommit exception; next/session setting scope. |
| 17.7.2.4, locking reads, 17.7.3, locks by statement, 17.7.5.2, detection, 10.11.4, metadata locks | Retained shared/exclusive/index-range locks; current locking-read values; nonuniversal victim selection; disabled detection; DDL conflicts. |
Cross-surface handoff
These are outside the assigned chapters. Locations refer to the fixed point. Generated parts, llms sections, CLI titles/claims, and ledger metadata must follow their named Scenario source. The following required corrections belong to the corresponding remaining audit slices, not retrospective feature candidates.
| Owner | Exact occurrences | Required reconciliation |
|---|---|---|
| #40, shared error/visibility summaries | docs/faq.md:16,20,32,36,44,52,56; docs/concepts/what-is-a-transaction.md:35-40; docs/errors/1205.md:3,11,27; docs/errors/1213.md:3,11,33,41 | Scope errors by engine/transaction/configuration; do not infer open transaction from a standalone timeout; qualify snapshot/locking rules, SERIALIZABLE autocommit and retry/victim/timing. Update descriptions and visible answers together. |
| #40, shared isolation/anomaly summaries | docs/concepts/isolation-levels.md:39-59; docs/concepts/anomalies-by-engine.md:24-34,46-59; docs/concepts/isolation-anomalies.md:9,23,53-54; docs/concepts/write-skew.md:27-44; docs/concepts/dirty-read.md:8-17,28-33; docs/concepts/lost-update.md:36-54 | Distinguish D/M/derived support and own writes, consistent/current operations, in-transaction reads, G2-item versus unexecuted predicate G2, dirty-write definition, and no external action established by a SELECT. InnoDB 1213 also has SQLSTATE 40001. |
| #35, locking slice | docs/mysql/03-locking/deadlocks.md:20-21; docs/mysql/03-locking/nowait-skip-locked.md:13-25; scenarios/mysql/03-locking/deadlock.yaml:claim; scenarios/mysql/03-locking/lock-timeout.yaml:claim,steps[5].note; scenarios/mysql/03-locking/for-update-blocks.yaml:title,claim; scenarios/mysql/03-locking/lock-mode-matrix.yaml:claim; scenarios/mysql/03-locking/ddl-lock-timeout.yaml:claim; scenarios/mysql/03-locking/alter-table-outage.yaml:claim,steps[5].note | Detection is configurable; distinguish row/metadata timeouts, standalone/explicit transaction scope, conflicting locking reads, and scoped no-wait assertions. Follow all generated mirrors of these exact source occurrences. |
| #35, MVCC slice | docs/mysql/04-mvcc/read-views.md:5-12,37-44; scenarios/mysql/04-mvcc/read-views.yaml:claim,steps[9].note; scenarios/mysql/04-mvcc/undo-logs.yaml:claim,steps[2].note,steps[5].note | First consistent read is level-scoped, WITH CONSISTENT SNAPSHOT needs RR, and reader cost is unmeasured. Do not describe own-write/current-DML visibility as a fixed world or all readers as lock-free. |
| #37, patterns slice | docs/mysql/05-patterns/orm-pitfalls.md:11-23,39-64; scenarios/mysql/05-patterns/implicit-commit.yaml:claim,steps[9].note; scenarios/mysql/05-patterns/on-duplicate-key.yaml:steps[11].note; scenarios/mysql/05-patterns/retry-deadlocks.ts:claim | Qualify ORM configuration and DDL/temporary-table boundaries; remove “no 40001” blanket wording; IGNORE does not suppress every possible error; a successful demonstrated retry does not guarantee success on every attempt. |
| #40, evidence promises | README.md:6,15,18,21-22,55-56; docs/about/methodology.md:11,20,62,70,75,84-90; scripts/gen-transcripts.ts:152-153; docs/public/llms.txt:4-5; docs/index.md:3,8,20; docs/start-here.md:46-48 | Green execution and deterministic generation support particular asserted schedules, not every prose statement or all possible schedules. Keep manual contracts and derivations distinct. Shared generated index wording must change at its generator. |
Verification and limits
The completion handoff records checked commits, observed database and independent-driver results, two-generation stability, structural checks, and fresh-context rendered-reader QA separately. This inventory alone is not runtime or browser proof. No in-scope unsupported claim is intentionally deferred. Unexecuted alternative engine versions, error configurations, predicate schedules, crashes, networks, external effects, and benchmarks are either removed from promises or expressly treated as Documented contracts/marked derivations.
No practice, guide, diagram feature, or shipping action belongs to this slice. Review and follow-up verification remain separate from database and browser evidence. The parent audit gate remains open until the remaining audits reconcile their assigned surfaces.