By Chason ArthurRevision 118.063Published Updated

Comparison / Choose a boundary

Choose the mechanism for the failure.

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.

This comparison separates the implementation pattern from the environment used to explore it. A general playground is useful for editing freely; this lab adds a fixed failure, a readable execution trace and a direct path from the result to its source.

Where each choice fits

ApproachUseful whenTradeoffConnection to this lab
Guarded UPDATEA single counter or availability rule can be expressed in one statement.Check affected rows and keep dependent writes in a transaction.The core inventory choice in this lab.
Pessimistic row lockingSeveral reads and writes require a consistent locked decision.Lock ordering, wait times and deadlocks require real engine tests.The MySQL companion introduces it; SQLite is not evidence for it.
Optimistic version checksConflicts are uncommon and callers can retry a versioned decision.A conflict is a normal result that the application must handle.A useful extension for quote editing, separate from the stock counter.
Outbox plus receiver inboxA committed change must trigger work across a delivery boundary.The receiver must define an idempotent effect; delivery may repeat.Shown as local database behavior with no external sends.
WordPress PlaygroundExplore WordPress or use its open-source PHP.wasm building blocks.A full WordPress installation serves a broader purpose than these SQL exercises.This lab reuses the PHP runtime with a small, purpose-built application.
Disposable MySQL environmentVerify actual InnoDB concurrency and plans.Requires a local runtime, more resources and explicit database isolation.Use the download when you are ready to test beyond the browser.

Compare the same input

  1. Choose one scenario and predict the outcome.
  2. Run it without safeguards and retain the visible result.
  3. Change only the implementation, then run again.
  4. Inspect the source responsible for the difference.
  5. Name the guarantee that changed and the guarantee that remains outside the experiment.

The browser executes actual PHP and SQLite through the open-source PHP.wasm runtime from WordPress Playground. Each run creates a new memory database. The last-item example replays a chosen stale-read order; it does not measure MySQL concurrency or InnoDB row locks.

Run the comparison

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

Lab discussion / PHP Transaction Lab

What would you try next?

Ask about the code, compare an approach, or describe an edge case. Reader comments are reviewed before publication.

All lab discussions

Reader comments

Open the discussion to read and add reader comments.

Read or write a comment →

Code walkthrough

Follow the implementation in code.

These short excerpts connect the lab’s behavior to its implementation. Each example links to the full tutorial step for its context, tradeoffs and checkpoint. Names and dependencies belong to that source; the excerpts are not standalone programs.

Example 01 · Tutorial excerpt

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 >= ?;

Read the full tutorial step

Example 02 · Tutorial excerpt

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;
}

Read the full tutorial step

Example 03 · Tutorial excerpt

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;
}

Read the full tutorial step

Example 04 · Tutorial excerpt

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.

Read the full tutorial step

Example 05 · Tutorial excerpt

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);

Read the full tutorial step