Zyel
By Chason ArthurRevision 118.063Published Updated

Eight steps / Explained code / Checkpoints

Build the safeguards.

A transaction keeps related database writes together. A guarded update protects availability. An idempotency key identifies a repeated decision. These mechanisms solve different parts of the same ordering problem.

Prerequisites: Try the playground first. The first pass needs only a modern browser. Editing the source needs the toolchain listed in the downloadable setup guide.

Choose a pace: 15 minutes to compare the failures; about 60–90 minutes for a first guided pass; extra time for the independent challenge.

  1. Observe the stale read
  2. Move the availability decision into the database
  3. Give retries an identity
  4. Commit a complete decision or restore all of it
  5. Write the intent to deliver beside the order
  6. Inspect the plan and preserve the answer
  7. Use Laravel database components without hiding their behavior
  8. Separate browser proof from a MySQL test

Step 01

Observe the stale read

Run the stock example without safeguards. The script first reads inventory for customer A, then reads it for B, before either writes. Both decisions use the same old value. The two insertions are real SQL, but their order is deliberately controlled so the failure is reproducible. The stock total alone looks plausible: zero. The order count reveals the broken invariant.

firstRead = 1; secondRead = 1;
// Both shoppers then save an order and write stock = 0.

Tradeoff and pitfall. This replay is useful for explaining a lost update. It cannot tell you how two independent database connections, a particular isolation level or a production lock wait will behave.

Check before moving on. The result shows 2 orders for an initial stock of 1. Describe the exact read/write order before running the safeguarded version.

Step 02

Move the availability decision into the database

The guarded statement subtracts stock only if enough stock exists at the instant of the update. The affected-row count is the result of that decision. An accepted order is written only when one inventory row changed. This avoids the stale application check demonstrated in Step 1. It is appropriate for this single-SKU counter.

UPDATE inventory
SET stock = stock - ?
WHERE sku = ? AND stock >= ?;

Tradeoff and pitfall. For several products, use stable lock ordering and an engine-specific transaction design. A lockForUpdate read can be useful with InnoDB; SQLite does not provide the same row-lock semantics. Do not infer those guarantees from this browser.

Check before moving on. With safeguards, observe 1 order, stock 0 and a declined second decision. Examine rowCount and the transaction around the update.

Step 03

Give retries an identity

A retry key identifies one logical request, not one network attempt. reserve checks the prior request inside the transaction before checking current availability. It compares a hash of the normalized body, so the same key cannot silently become a different purchase. A unique database key backs up that identity.

if ($previous) {
    if (!hash_equals($previous["body_hash"], $hash)) {
        throw new DomainException("Different request");
    }
    return $originalDecision;
}

Tradeoff and pitfall. A new key is a new operation. Clients must retain the original key across an uncertain timeout. For MySQL, handle duplicate-key races and retryable transaction failures around the whole operation; the isolated browser exercise does not execute that engine-specific retry loop.

Check before moving on. Choose Retry. With safeguards, the second result is replayed and there is still 1 order and 1 event. The native test rejects the same key with a changed quantity.

Step 04

Commit a complete decision or restore all of it

Choose Failure before commit. The injected exception occurs after stock, order and outbox writes. The catch path rolls back the transaction before the exception leaves the service. The detail panel preserves the after-failure snapshot, then shows a retry. Without the transaction, stock can disappear without an order.

$db->beginTransaction();
try { /* inventory + order + outbox */ $db->commit(); }
catch (Throwable $e) {
    if ($db->inTransaction()) $db->rollBack();
    throw $e;
}

Tradeoff and pitfall. Do not send email, charge a card or call a remote fulfillment API inside a retried database transaction. Those effects cannot be undone by rolling back these tables.

Check before moving on. The guarded afterFailure snapshot has stock 1, orders 0 and events 0. Its retry then creates exactly one local order.

Step 05

Write the intent to deliver beside the order

An outbox row records a committed intention to notify another system. It shares the order transaction. A later worker can find undelivered rows. The browser demo does not contact a service; it exercises a local receiver whose inbox uses the event key as a uniqueness boundary. Lost acknowledgments can cause redelivery even after useful work succeeded.

INSERT INTO outbox (event_key, request_key) VALUES (?, ?);
-- The receiving fixture records a unique event_key in inbox.

Tradeoff and pitfall. The local inbox transaction proves deduplication of this database effect. It does not promise exactly-once email or payment. A real receiver must provide an idempotent operation or its own durable deduplication boundary.

Check before moving on. Choose Repeated delivery. The safeguarded version records 1 receipt after 2 delivery attempts. The unguarded example records 2 receipts.

Step 06

Inspect the plan and preserve the answer

The query exercise creates 5,000 invented history rows on every run. Without the matching index, inspect the scan and ordering work. With it, inspect the indexed lookup. The native regression compares result IDs before and after the index. Improving a plan while changing the answer is not an optimization of the same query.

CREATE INDEX history_customer_status_recent
ON quote_history(customer, status, created DESC);

Tradeoff and pitfall. An index costs space and write work. Predicate order, selectivity and ordering matter. This exercise reports actual SQLite plans, not fabricated timing claims or a prediction of MySQL throughput.

Check before moving on. Run both query modes. The returned IDs match, and the safeguarded plan names history_customer_status_recent.

Step 07

Use Laravel database components without hiding their behavior

The download includes a separate, runnable Illuminate Database exercise. Composer installs the pinned database and events components; this is not a complete Laravel application. Four orders each have one line. Accessing each relation lazily runs one initial query plus four relation queries. Eager loading asks for the orders and their lines in two queries.

foreach (DemoOrder::with("lines")->get() as $order) {
    $quantities[] = $order->lines->sum("quantity");
}

Tradeoff and pitfall. Eager loading prevents this N+1 pattern but can load too much data. Select the fields and relations the view needs. A Laravel policy, real authentication and HTTP deployment are outside this isolated database exercise.

Check before moving on. After composer install, run php eloquent.php. Expect lazyQueries 5, eagerQueries 2 and sameResult true.

Step 08

Separate browser proof from a MySQL test

The source provides a native test suite and a disposable MySQL exercise. Use the provided local container and only the named lab database. The exercise uses independent clients to compete for one inventory row, then checks the final order count. Keep engine version, isolation level and observed results with the test receipt.

php test.php
php eloquent.php
# Optional isolated MySQL workflow: see mysql/README.md

Tradeoff and pitfall. SQLite serializes writes differently from InnoDB. A locally passing transaction test does not establish MySQL lock behavior, scale, compliance or real ordering. Never point the exercise at an existing application database.

Check before moving on. First reproduce all five browser scenarios. Then explain what a genuine multi-connection MySQL run must add before making a concurrency claim.

Independent challenge

Make one new guarantee.

Add a second SKU, bind retries to a normalized cart, inject a failure after the second stock update, and prove that no partial order survives. Define a stable lock order for the separate MySQL implementation.

Write the expected behavior before changing the implementation. Keep the original failure as a regression case. Explain the tradeoff you accepted and the result that would make you reconsider it.

Primary references and further reading

References support the language and runtime concepts. The lesson, scenarios and explanations are original Zyel Lab material.

Keep exploring

Follow the question you have next.

Build the safeguards

Eight explained steps, tradeoffs, pitfalls and a checkpoint before moving on.

Read the tutorial

Compare the choices

Choose a pattern and an environment for the problem you actually have.

Compare approaches