MySQL patterns and distributed work audit, issue #37
This existing-content audit applies #31 to #37. The baseline is 154b2207542ba51c3470128cedba35a640ad338f. Complete occurrence inventories, baseline snapshots, execution logs, artifact hashes, and QA reports remain in temporary storage. The issue completion comment identifies the checked commit and verification results.
Coverage and occurrence identity
The assigned surface contains ten lessons, fourteen Scenarios, fourteen generated Transcript families, and their fourteen ledger records and llms sections. Thirteen Scenarios are shared YAML; the deadlock retry is TypeScript client code with no Python parity. The MySQL chapter 5/6 sidebar labels and README coverage cells identify subjects rather than promise additional outcomes. Built lesson descriptions use the lead Scenario claim, which is corrected at its source.
The inventory records exact paths, paragraph or nested YAML-field locations, step indices, sessions, assertions, and generated event identities. Each generated statement, note, pending resolution, and timeline occurrence is associated in source order with its own operation. Repeated SQL is not joined by its first matching text. The llms section must contain the complete corresponding Transcript body, and the ledger record must retain that Scenario's exact claim and version. Returned columns not listed in a subset assertion are observations, not separately asserted guarantees.
The baseline remains available as a source snapshot and line inventory. Rewritten lesson paragraphs supersede the baseline under the decisions below; there is no invented paragraph-to-paragraph equivalence. Occurrence units can contain several sentences or only execution context, so their count is not a count of independent guarantees. Scope annotations identify the tested configuration, not an independent engine-state trace. Executed artifacts report MySQL 8.4.11; manual contracts target MySQL 8.4 InnoDB. The assigned transactional schedules use the pinned server's default REPEATABLE READ. XA detachment is explicitly set and asserted.
Semantic dispositions
| Decision | Subject | Evidence and final disposition |
|---|---|---|
| A1 | Lost-update repairs | The arithmetic, locking-read, and version-predicate schedules each assert final balance 120. B's arithmetic UPDATE waits; its locking read returns 110 after waiting; its stale version write affects 0 rows before fresh-transaction recovery and final version 3. Removed any-isolation and lock-free write promises. The single-row guarantee is marked † with a locking/predicate derivation and cooperative-writer limits. Human edits require reconsideration; these recipes do not enforce arbitrary cross-row rules. |
| A2 | Check, uniqueness, and duplicate handling | Two plain checks without UNIQUE lead to count 2 at default REPEATABLE READ. With UNIQUE, B waits for A's commit, receives 1062, then an upsert increments attempts to 2 with affected count 2; IGNORE skips the duplicate with count 0. Removed all-isolation and every-error claims. Upsert does not name a conflict target; multiple unique indexes and other errors require separate handling. No first-inserter rollback branch is executed. |
| A3 | Idempotency | Postcommit and in-flight duplicates affect 0 rows, with asserted balances 70 then 45 and stored amount 30. The connection excludes CLIENT_FOUND_ROWS. Removed network-response-loss and external exactly-once promises. The retained-key database guarantee is marked † and derived from uniqueness plus one commit boundary. Payload validation, expiry, and complete response recovery are advice, not implemented execution. |
| A4 | Named locks | GET_LOCK results 1/0, COMMIT retention, explicit release, and two released names are asserted. Elapsed time, rollback, session termination, and negative timeout are not executed. Manual contracts distinguish those cases, NULL/error outcomes, repeated acquisition, server-wide names, and user-lock deadlock from InnoDB 1213. Pool retention is a marked session-lifetime inference, not a tested pool or prompt-crash-release guarantee. |
| A5 | Queue | A and B select jobs 1 and 2; explicit rollback makes job 2 selectable again, and both final states are done. Removed real-crash, no-loss, no-repeat, and fairness promises. Task strings execute no email or invoice service. SKIP LOCKED is limited to row-lock conflicts. Database-local claim/work atomicity is marked †; external work, disconnect detection, starvation, and contention rates are not tested. |
| A6 | ORM and implicit commits | CREATE INDEX makes the preceding INSERT visible to B; later ROLLBACK leaves it visible. Removed universal framework/default/retry claims, a narrated migration exception, and every-DDL wording. Temporary-table DDL exceptions are documented. Existing lock, metadata, snapshot, and undo lessons support conditional transaction-lifetime guidance. No ORM, migration framework, or external API is executed. |
| A7 | Retry | The forced deadlock emits 1213; a new transaction executes both UPDATEs on attempt 2, with balances 115 and 85. Removed guaranteed-success and measured-deadlock-rate wording. The helper handles only normalized 1213 and has a default five-attempt limit, without backoff or general cleanup. The documented 1205 scope depends on innodb_rollback_on_timeout. Exhaustion and non-1213 propagation are visible code paths, not exercised Scenario outcomes. |
| A8 | Dual writes and outbox | The separately written local broker stand-in gives asserted counts 1/0 and 0/1, the latter after CHECK error 3819. No process or downstream consumer runs. Added order SELECTs assert both commit and rollback boundaries beside the existing outbox assertions. Relay DELETE rollback permits reselection; committed DELETE gives pending count 0. Removed observed HTTP publication, redelivery, and unconditional eventual delivery. The duplicate-publication window is marked † with an external/database boundary derivation. Polling cost and latency are unmeasured advice. |
| A9 | Saga | The autocommit Reader sees 4 seats between steps; the hotel UPDATE affects 0 rows; compensation restores 5 seats, now also asserted by Reader after COMMIT. Removed global-visibility and no-local-isolation wording. The committed-step boundary is marked †. Two local tables model services; no coordinator, progress table, crash recovery, failed compensation, or repeat-safe compensation protocol is implemented. |
| A10 | XA | SET and SELECT establish xa_detach_on_prepare=ON. XA RECOVER lists the branch and NOWAIT returns 3572 before and after A's session is killed. Another permitted session commits by XID and reads 200. Removed coordinator-crash, server-restart, survives-anything, undo-growth, and database-wide-lock assertions. Only one MySQL resource branch runs; the Transaction Manager's global decision and recovery are not demonstrated. XA RECOVER lists prepared branches, not only orphans. |
† denotes an Entailed guarantee or boundary inference with a derivation and an explicit execution limit. Demonstrated behavior requires executed assertions; Documented contracts retain versioned manual support. Application advice and model descriptions are not execution evidence.
Official support
The MySQL root llms endpoints were unavailable, so support was checked in the versioned HTML manuals: locking functions, locking reads, InnoDB locking, upserts, INSERT and IGNORE, UPDATE, affected-row client settings, implicit commits, isolation levels, error handling, XA resource management, and XA states and detachment. The temporary registry records the claim-specific support and execution assertions.
Outside-slice reconciliation
These are exact baseline locations. They remain with their assigned owners, not unresolved #37 claims. The inherited #33 ORM, DDL, IGNORE, and retry handoffs and #36 queue, idempotency, dual-write, outbox, and saga handoffs are resolved at the MySQL sources and their generated mirrors. Earlier audit reports remain historical records.
| Owner | Baseline occurrences | Required correction |
|---|---|---|
| #40 | docs/concepts/transactional-outbox.md:2,7-10,14-25,44-55,59-65 | Distinguish local stand-ins and explicit rollback from crashes and transport. Mark duplicate-delivery inference, condition eventual delivery, and remove measured polling-latency and universal transaction promises. |
| #39, reconciled by #40 | docs/mysql/07-pitfalls/compendium.md:29-32,43-45,63-66,73-77,104-109,121-132 | Scope single-row repairs, database-local idempotency and CLIENT_FOUND_ROWS, timeout configuration, implicit-commit statements, ORM defaults, delivery models, XA detachment, and recovery by XID under a coordinated decision. |
| #39, reconciled by #40 | docs/mysql/08-production/alerting-checklist.md:23-33 | Retry advice needs a bounded complete-transaction and external-effect scope. Single-row patterns are not blanket protection for cross-row write skew. Diagnostic patterns and rates are workload-dependent. |
| #40 | docs/faq.md:28,56; README.md:6,15-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/.vitepress/theme/components/HomeCurriculum.vue:17 | Separate asserted schedules, manual contracts, and marked derivations. Retry can fail, stale edits need reconsideration, and external effects are outside the protected transaction. Correct shared generated claims at their generator source. |
| #40 | docs/.vitepress/config.ts:237,291-293 | The provenance footer says every Transcript was re-proven through psycopg/PyMySQL when the Python stamp exists. That stamp covers shared YAML, not the TypeScript deadlock-retry Scenario. Keep driver coverage specific. |
Each assigned Scenario's generated part, llms section, ledger claim, and derived lesson description belongs to this slice and is reconciled here. No new exercise, guide, diagram, failure lab, delivery integration, or alternate harness is introduced.
Verification boundaries
The completion comment reports real-database execution, independent Python YAML coverage, generated stability, static checks, mutation checks, and fresh-context reader QA separately. Mutation checks verify that an order committed outside the outbox transaction, an uncommitted compensation, and disabled XA detachment fail the new assertions. TypeScript retry has no Python parity. No real payment/email/broker delivery, server restart, indeterminate COMMIT, exhaustive schedule search, workload benchmark, or learner outcome is claimed.
No intentionally unsupported in-scope claim remains. The parent audit gate stays open for the other slices and shared reconciliation. Code review and shipping are separate stages.