حل آموزشی تمرین‌های فصل 8Chapter 8 exercise solutions, explained

• Named product and version: PostgreSQL 18, per its transaction-isolation documentation; behavior below is product behavior, not the SQL minimum.
• Two concurrent sessions on one small accounts table; balances like 500/500 are teaching numbers (آموزشی), never benchmarks.
• PostgreSQL is NOT installed in this sandbox (no psql, no pg_ctl), so the SQL runs below are a local-run guide; the executed demo models the same histories and checks the same invariants in memory.

A two-session history identifies exactly what each read observed; unscheduled concurrent threads alone are not evidence of an anomaly. Name PostgreSQL 18 because SQL levels specify minima and products may be stronger. Write skew needs a cross-row invariant, illustrated by on-call doctors. Retry the whole transaction with a fresh snapshot to avoid reusing an obsolete decision.

1. Prove the forbidden absent Probe the forbidden first: with T1's UPDATE uncommitted, a READ UNCOMMITTED reader must still see 500 (history H1), and a rival UPDATE must wait on the row lock (history H2). Log invisibility and waiting as passes.
2. Skew the transfer Replay the transfer twice: under READ COMMITTED the reader straddles the commit and sums 1100 (history H3, reproduced); under snapshot isolation the same interleaving reads the start snapshot and sums 1000 (history H4, prevented).
3. Lose an update, then fix it Overlap two read-modify-write cycles on a counter at 10: both write 11 and one increment is lost (history H5). Replace the pair with two single-statement increments: each applies to the latest committed value and the counter ends at 12 (history H6) — the audit follow-up, answered in code.
4. Recount under a rival insert Count twice inside one transaction while a rival inserts: READ COMMITTED recounts 2 then 3 (history H7). On PostgreSQL, REPEATABLE READ re-reads the snapshot and stays at 2 (history H8) — product behavior, not a portable promise.
5. Skew the rota, then serialize it Let Alice and Bob each see {Alice, Bob} in their snapshot and both step down: the on-call set ends empty under snapshot isolation (history H9). Re-run at SERIALIZABLE: the second committer takes 40001, retries the whole transaction, and one doctor survives (history H10).

Two psql sessions, A and B, on one database. Table: accounts(id int primary key, balance int) with two rows of 500. Session A runs the writer history, session B the reader history; the interleaving is driven by hand (run each statement in the printed order). Set the level per run with BEGIN; SET TRANSACTION ISOLATION LEVEL .... For the blocking probe, run B's conflicting UPDATE before A commits and watch it wait; SELECT ... FOR UPDATE is not needed for the probe — the write lock is implicit.

Teaching numbers (آموزشی): transfer history sums 1100 under RC against 1000 under SI — the 100 gap is the anomaly, priced in the same units as the balances. Counter histories end at 11 (RMW pair) against 12 (statement pair) — the missing 1 is one lost increment. Recount histories read 2-then-3 (RC) against 2-then-2 (snapshot) — the extra 1 is the phantom. Rota histories end with 0 on call (SI) against 1 survivor (serializable) — the survivor is the invariant kept.

• Explicit locking instead of serializability: SELECT ... FOR UPDATE the rows your predicate depends on, or maintain a materialized conflict row and lock it. Same effect as serializable for a known predicate, but you must find every predicate by hand — miss one and the anomaly returns.
• Optimistic version check in the application: read a version column with the row and UPDATE ... WHERE version = read_version. Zero lock contention, but every writer must follow the protocol and retries are still whole-transaction.

• Serializability failure 40001 retried as a single statement: the retry reuses the stale snapshot reads and can still break the invariant. Retry means re-running the WHOLE transaction from BEGIN.
• An ORM that maps counter increments to read-then-absolute-write: the application looks correct and passes single-threaded tests, then loses updates under the first concurrent load.
• Assuming READ UNCOMMITTED shows dirty data because the standard allows it: on PostgreSQL 18 it does not, and a test written for the standard's minimum asserts a behavior the product never exhibits.

Run: javac AnomalyLab.java && java AnomalyLab — stdlib Java only, instant. PostgreSQL parts are NOT executed here; see the local-run guide in the checks section.

Expected output: PASS H1 dirty-read prevention (READ UNCOMMITTED) — reader saw 500, uncommitted 400 stayed invisible PASS H2 dirty-write blocking — T2 waited on the row lock, wrote only after T1 committed PASS H3 read skew (READ COMMITTED) anomaly present — T2 saw 500+600=1100, 100 appeared from nowhere mid-transfer; REPRODUCED as designed PASS H4 read skew (SNAPSHOT ISOLATION) — T2 read its start snapshot 500+500=1000; PREVENTED PASS H5 lost update (read-modify-write) — final=11 instead of 12; REPRODUCED PASS H6 atomic increment (UPDATE balance=balance+1) — final=12; row lock serialized the two writes PASS H7 phantom read (READ COMMITTED / SI re-read) — 2 then 3 in one transaction; REPRODUCED PASS H8 phantom (REPEATABLE READ snapshot re-read) — snapshot re-read stable at 2; new row invisible, PREVENTED PASS H9 write skew (SNAPSHOT ISOLATION) — on-call=[], invariant '>=1 on call' broken; REPRODUCED PASS H10 write skew (SERIALIZABLE) — survivor=[Bob]; exactly one committer, other retries; PREVENTED ---- histories run=10 passed=10 failed=0 PG-18 note: H1/H2 are prevention tests (blocking observed, anomaly absent).

Per-history invariants: H1 reader value == 500 while 400 uncommitted; H2 second UPDATE returns only after first commit/abort; H3 sum == 1100; H4 sum == 1000; H5 final == 11; H6 final == 12; H7 counts 2 then 3; H8 recount == 2; H9 on-call empty; H10 exactly one survivor. Local-run guide (needs a real PostgreSQL 18): open two psql sessions, BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; (or REPEATABLE READ / SERIALIZABLE), interleave the statements by hand, and use a second connection to confirm the H2 writer is waiting (it returns only after the first session commits). Measure lock-wait time and 40001 abort rate under a concurrent driver — those two numbers size your retry budget.

• Claiming a reproduced dirty read on PostgreSQL: the product forbids it at every level including READ UNCOMMITTED, so the 'reproduction' is either a different anomaly mislabeled or a test bug.
• Reading product behavior as the SQL standard: the standard never defined snapshot isolation, so 'repeatable read' means snapshot isolation in PostgreSQL, weaker MVCC in MySQL/InnoDB, and real serializability in Db2.
• Retrying only the failed statement after a 40001: the retried statement reuses the doomed snapshot, so the invariant can break again. Serializable retries always restart from BEGIN.

• One popular room R, interval bookings [start, end); discrete fixed slots are a different (easier) problem.
• PostgreSQL 18 with range types and exclusion constraints; latencies below are teaching numbers (آموزشی) from a local 50-thread race, never benchmarks.
• PostgreSQL is NOT installed in this sandbox, so the SQL runs are a local-run guide; the executed demo checks the overlap invariant over every history and runs a real 50-thread race on the room-row-lock design.

Two half-open intervals overlap when each starts before the other ends, yielding both inequalities. Equality checks miss 10–12 versus 11–13. Locking the existing room row serializes all compliant writers for that room; an empty query result has no row to coordinate them. Fifty contenders expose races, not real-world capacity. Vary concurrency and evaluate correctness separately from latency.

1. Define overlap, bury equality Write the invariant as a predicate over committed rows: for room R, no pair satisfies overlap. Show [9,11) vs [10,12) overlapping on [10,11) with zero shared endpoints — this single example kills every equality-based design, including UNIQUE.
2. Race the naive pair twice Interleave two check-then-insert histories: both checks see emptiness, both inserts land, one overlap (design A). Wrap the identical pair in READ COMMITTED transactions: same interleaving, same overlap (design B) — the level adds row locks, not predicate locks.
3. Serialize on what exists Lock the rooms row for R with SELECT ... FOR UPDATE before checking (design C): the second locker blocks, unblocks after the first commit, re-checks against the winner's row and refuses. Name the trap explicitly: FOR UPDATE on the empty reservation result would have locked nothing.
4. Constrain the write itself Move the guard into the write itself: the GiST exclusion constraint rejects the overlapping INSERT at commit time (design D), and SERIALIZABLE aborts the second committer with 40001 for a whole-transaction retry (design E). State the UNIQUE boundary: equality guards fixed slots only.
5. Race fifty, keep one Design the 50-way race: all fifty want [9,11) on R; assert winners == 1 and overlaps == 0; report abort/retry rate and median/p99 as teaching numbers; bound retries with backoff plus jitter and answer hopeless attempts with fast 409s so the hot room cools.

Schema: rooms(id text primary key) with one row 'R', plus reservations(id serial primary key, room text references rooms, during tstzrange, EXCLUDE USING gist (room WITH =, during WITH &&)). Write path per attempt: BEGIN, rail A — SELECT * FROM rooms WHERE id='R' FOR UPDATE — then overlap check then INSERT then COMMIT; rail B — plain INSERT and let the exclusion constraint reject overlaps. Read path: availability queries read committed rows only. API: POST /bookings returns 200 with the booking id or 409 with the conflicting range; 40001 (serializable rail) or 23P01 (exclusion violation) both mean retry-or-refuse, and retry always restarts from BEGIN with bounded backoff plus jitter.

Teaching numbers (آموزشی): designs A and B each commit 2 rows with 1 overlap — the double booking, priced in rows. Designs C, D and E each commit 1 row with 0 overlaps. The live 50-thread race on C prints winners 1, doubles 0, median 6us, p99 167us — the p99 is lock-queueing, not work. The extension probe ([9,11) extended to [9,12) against a meanwhile [11,13)) overlaps, so the guard must evaluate post-UPDATE ranges.

• SERIALIZABLE everywhere with whole-transaction retry: simplest to reason about, no lock design to audit — but a hot room answers contention with abort storms, and every 40001 costs a full re-execution plus backoff. Fine for cool rooms, painful for the popular one.
• Application-level distributed lock (one lock per room around check+insert): works across databases and ORMs, but adds lock infrastructure, fencing against stale holders, and a new failure mode — the lock service itself.

• Retrying only the failed INSERT after a 40001 or an exclusion violation: the retry skips the overlap check against the winner's fresh row and can double-book on the second attempt. Restarts begin at BEGIN.
• Unbounded immediate retries under the 50-way race: every refusal retries at once, the hot room never cools, and p99 climbs while throughput collapses. Bound the attempts, back off with jitter, and return 409 fast when the slot is truly gone.
• A UNIQUE constraint on (room, start) presented as an overlap guard: it rejects identical starts and waves [9,11) vs [10,12) straight through. Equality is not overlap.
• Extending a booking with a bare UPDATE that skips the guard: the new [9,12) silently covers the [11,13) booked meanwhile. Extensions must pass the same predicate — the exclusion constraint does this automatically, a hand-rolled check only if you remember.

Run: javac BookingRace.java && java BookingRace — stdlib Java only. The threaded race takes seconds; PostgreSQL parts are NOT executed here.

Expected output: [A] plain check+insert: rows=2 double-bookings=1 (REPRODUCED) [B] READ COMMITTED txn: rows=2 double-bookings=1 (REPRODUCED — RC has no predicate lock) [C] fixed room-row lock: rows=1 double-bookings=0 (PREVENTED) [D] exclusion at write boundary: rows=1 double-bookings=0 (PREVENTED) [E] serializable retries=1 [E] SERIALIZABLE + retry: rows=1 double-bookings=0 (PREVENTED) [F] extend-vs-next: extension overlaps next=true (exclusion re-checks the NEW range; equality/UNIQUE would miss it) [G] UNIQUE on fixed slot rejects exact duplicate=true but cannot express [9,11)-vs-[10,12) overlap [H] 50-thread race on room-row lock: winners=1 double-bookings=0 median=106us p99=225us (teaching numbers) INVARIANT HOLDS: yes

Invariant harness: after every history and every race, assert doubleBookings == 0 over committed rows and winners == 1 under contention. Contention metrics: abort/retry rate on the hot room, median and p99 attempt latency (teaching numbers), and time-to-first-409 for losers. Local-run guide (needs a real PostgreSQL 18): create rooms plus reservations with the EXCLUDE USING gist (room WITH =, during WITH &&) constraint; race two psql sessions through the printed interleavings; then drive fifty concurrent INSERT attempts at one room and confirm exactly one commits while the rest take exclusion violations or 409s.

• SELECT FOR UPDATE on the reservation search that returns no rows: it locks zero rows and the rival's INSERT lands freely — then the design review concludes 'locking doesn't work' instead of 'we locked nothing'.
• Presenting a READ COMMITTED transaction as the fix: the transaction wraps the same racy pair and RC adds no predicate lock, so the demo double-books identically — only slower.
• Retrying the failed statement instead of the transaction: the retry inherits the stale pre-commit read set and the second attempt can still overlap — aborts restart at BEGIN or they do not fix.

• An at-least-once queue: the broker redelivers after every crash and after every un-ACKed attempt; duplicates are normal transport behavior.
• A local store with real transactions holding both the dedup table and the ledger; the 30-day horizon is a teaching number (آموزشی), sized per product in production.
• No broker or database is installed in this sandbox, so the queue, the crashes and the ledger are modeled in memory; the executed demo asserts the same per-crash invariants a SQLite-backed lab would check.

A unique ID in the same transaction as the effect lets the constraint pick the winner; rollback removes both. ACK after commit avoids losing the message before its effect exists. The example 30-day horizon must cover supported retry/replay/restore windows. Longer replay requires longer retention or explicit rejection. None of this includes an external SMS in the local transaction.

1. Box the promise first Draw the four boundaries and label what each contains: LOCAL (ledger rows that roll back), TRANSACTION (key insert plus effect, all-or-nothing), EXTERNAL (mail/SMS/webhook, no rollback exists), HORIZON (keys live 30 days teaching; ACK removes nothing). Every later claim must name its box.
2. Commit, then ACK Fix the order: BEGIN, INSERT the key, apply the ledger effect, COMMIT, then ACK. Crash-before-commit leaves nothing durable; crash-after-commit leaves the key; crash-after-ACK still leaves the key — the demo asserts all three redeliveries end applied-exactly-once.
3. Price the alternative Remove the table and rerun the same three crashes: 6 applications for 3 messages, balance 350 against 175 — the exact price of luck. Keep this pair of numbers; they are the whole argument for the dedup table.
4. Bind payload, bound time Bind the payload and bound the time: store the payload hash with each key and reject mismatches with an alert (poison redelivery changes nothing); expire keys at the horizon and admit that a six-month replay past a 30-day horizon re-applies — then cover it with business timestamps, not hope.
5. Idempotent receivers Push the guarantee to the receivers: the naive sender proves 2 mails for 1 ledger application around a crash, so carry the idempotency key into every external job and dedup at the gateway. Exactly-once effect is a receiver property.

Tables: processed_messages(id text primary key, payload_hash text not null, processed_at timestamptz) and the business table (here a ledger). Consumer loop per delivery: BEGIN; INSERT INTO processed_messages — on conflict compare payload_hash (mismatch means reject plus alert, never apply); apply the ledger effect; COMMIT; then ACK. A retention job deletes keys older than the horizon (30 days, teaching) — nothing else may delete keys, especially not the ACK path. Poison messages ACK after alerting so they stop looping; operator replays carry the original id so the horizon rule applies to them too.

Teaching numbers (آموزشی): three messages (+100, +50, +25) land the ledger at 175 with 3 applications under dedup, against 350 with 6 applications without it — the extra 175 is three duplicated effects. The poison probe moves the ledger by 0. The horizon probe re-applies 1. The naive external sender emits 2 mails for 1 ledger row — the 2-to-1 ratio is the at-least-once boundary, priced in sends.

• 2PC/XA across the queue and the database: true atomic commit of consume-plus-effect, but the coordinator blocks participants while in-doubt, queues rarely support XA, and one slow participant stalls every consumer. Correct in theory, unusable in most real topologies — which is why the chapter recommends idempotency instead.
• Kafka transactions plus the idempotent producer: exactly-once inside a Kafka-to-Kafka pipeline (consume-transform-produce), but the guarantee ends at the topic edge — the final receiver (ledger, mailbox, webhook) still needs its own idempotency key.

• ACK before COMMIT: the broker forgets the message while the effect is still uncommitted, so a crash loses the message forever — the one outcome redelivery cannot repair. Commit first is not style, it is the protocol.
• Deleting the dedup key on the ACK path ('we're done with it'): the next redelivery — crash 3 guarantees there will be one — finds no key and applies the effect a second time. Keys die by horizon expiry, never by ACK.
• Dedup on the id alone without payload binding: a poisoned redelivery (same key, attacker's payload) applies as a legitimate retry. Store the payload hash with the key and reject mismatches with an alert.
• A horizon shorter than the oldest legitimate replay: month-end replays, disaster-recovery restores and operator retries silently double-apply. Size the horizon beyond the oldest replay you must honor, and reject anything older by business timestamp — not by hope.

Run: javac IdempotencyDemo.java && java IdempotencyDemo — stdlib Java only, instant. No broker or database needed; crashes are injected calls.

Expected output: [dedup ON] three crash points, one message m1 (+100): crash BEFORE commit for m1 -> redeliver crash AFTER commit BEFORE ack for m2 -> redeliver dedup hit for m2: effect skipped, safe to ACK crash AFTER ack BEFORE cleanup for m3 -> redeliver dedup hit for m3: effect skipped, safe to ACK ledger={acct=175} appliedEffects=3 (each message applied EXACTLY once) reject m3: same key, DIFFERENT payload (poison redelivery) ledger after poison redelivery={acct=175} (unchanged) [dedup OFF] same crashes, no id table: balance=350 applied=6 (6 applications for 3 messages — luck, not safety) [horizon] operator replays 6-month-old m9, keys expire after 30 days: re-applied after expiry=1 (guarantee ends at the horizon; replays beyond it need business rules) [external] e-mail send is outside the transaction boundary: crash AFTER commit BEFORE ack for m5 -> redeliver dedup hit for m5: effect skipped, safe to ACK careful sender: ledger applied=1, e-mails sent=1 naive sender crashes before commit -> redeliver naive sender: ledger applied=1 BUT e-mails sent=2 (external effects are at-least-once: make THEM idempotent too) RULE: commit-then-ACK; ACK never deletes the key; horizon bounds the promise.

Per-crash invariants with dedup: applied effects == 3 for 3 messages, ledger == 175, poison redelivery changes nothing, horizon replay re-applies exactly 1, naive external sender emits 2 mails for 1 application. Without dedup: 6 applications, balance 350. Operational metrics: redelivery rate, dedup-hit rate (should dominate after incidents), key-table growth against the retention job, and age of the oldest unexpired key — alert when replays approach the horizon.

• Treating ACK as a delete permit for the dedup key: crash 3 exists precisely to punish this — the redelivery after ACK finds no key and doubles the effect.
• Promising exactly-once across systems with 2PC: the coordinator, the in-doubt window and the non-participating mailbox make it a bigger failure domain than the duplicates it prevents.
• Dedup keys without payload hashes and without a horizon: the first invites poisoned redeliveries, the second grows the key table until it becomes the outage.