System Design Drill Day 11: The Transactional Outbox Pattern

Almost every Laravel checkout controller has the same two lines: save the order, then dispatch a job. It works nearly every time. If Redis hiccups between those two lines, the order is paid and the email, the stock update and the merchant webhook never happen.

This drill uses a transactional outbox so an online marketplace never loses an OrderPlaced event or fires one for an order that doesn't exist.

Core concepts

Checkout writes the order to MySQL and then, as a separate step, pushes OrderPlaced to a Redis queue. That's a dual write with no shared commit, and either half can succeed while the other fails, which shows up as lost emails, missed inventory jobs, or events for orders that were rolled back.

The transactional outbox removes the second write from the request. In the same DB transaction as the order, you also insert a row into an outbox table. A separate publisher later reads committed outbox rows and pushes them to the queue. The publisher can be a poller or a binlog reader (change data capture, or CDC). Delivery is still at-least-once, so consumers stay idempotent as in Day 9.

Figure 1: Left: two separate writes that can disagree. Right: one commit, then a relay.

In an interview I'd present it in this order: name the dual-write failure modes, introduce the outbox, then explain why moving the queue push inside the transaction still doesn't make it atomic with Redis.

Pause and think: You move dispatch() inside DB::transaction. Which failure did you fix and which one did you just create?

Answer: A Redis error now rolls back the order instead of losing the event, but you've created two new problems: a worker can run the job before the commit lands and find no paid order, and a rollback after the push sends an event for an order that doesn't exist. Only writing the event as a row in the same commit solves both.

The design problem

The marketplace's checkout runs on 3 Laravel servers with a MySQL primary and a Redis queue. On successful payment it must mark the order paid, email the customer, decrement stock and notify the warehouse, and fire a merchant webhook.

Today the controller runs DB::transaction for the order update and then calls OrderPlaced::dispatch(...). About once a week Redis blips right after the commit, so the order is paid but the jobs are never queued. Client retries also sometimes dispatch twice even when Redis was fine.

Design the outbox table and its columns (id, type, payload, created_at, published_at). Show how the publisher claims rows and marks them published without two workers publishing the same row, and what happens if it pushes to Redis and crashes before marking the row. Then decide whether the Day 8 inventory lock and the Day 9 payment idempotency key are still needed.

Try the problem before reading the solution.

Questions to think about

1. Why can't you put dispatch() inside the DB transaction and call it atomic?
2. Poller or CDC: which would you pick for the publisher, and why?
3. The publisher re-publishes an event after a crash. What's the safe outcome for the customer email, and how do you guarantee it?

Model solution

Write the event with the order

Inside the same DB::transaction that marks the order paid, insert an outbox row for OrderPlaced carrying the payload the consumers need. The request path no longer calls dispatch() at all.

The publisher

A scheduled command or long-running worker opens a transaction and runs SELECT ... FROM outbox WHERE published_at IS NULL ORDER BY id LIMIT 100 FOR UPDATE SKIP LOCKED (MySQL 8+), so two workers never grab the same rows. It pushes each event to the queue, sets published_at, and commits. The transaction matters: with autocommit on, the row locks are released as soon as the SELECT returns, and two publishers can grab the same rows anyway. In Laravel that's DB::transaction(...) around a query using ->lock('FOR UPDATE SKIP LOCKED'); lockForUpdate() has no skip-locked option.

A crash after pushing but before marking means the row is published again, so consumers have to be idempotent. Dedupe on the outbox row id, or on order_id plus the event type.

Figure 2: The publisher loop. A crash between push and mark means a duplicate, which the consumer ignores.

What the outbox doesn't replace

The outbox doesn't replace the payment idempotency key or the inventory lock. Its only promise is that if the DB commit happened, the event will eventually be published. Email and webhook handlers still dedupe, and stock still uses the Day 8 lock and fencing.

Failure modes to name in an interview

The usual four are a lost event from the dual write, a duplicate event because the publisher is at-least-once, a poison payload such as bad JSON (dead-letter it and alert), and publisher lag, which you catch by monitoring the age of the oldest unpublished row.

Pause and think: Your outbox has rows that are 20 minutes old and still unpublished, but nothing is erroring. What would you check first?

Answer: Whether the publisher is actually running (scheduler, supervisor, or the worker process itself), and then whether one poison row at the front of the batch keeps failing and blocking the rest. That's why "age of the oldest unpublished row" is the metric to alert on.

Answers to the questions

1. Redis isn't part of the MySQL transaction. Push inside it and then roll back, and the event goes out for an order that doesn't exist. Push after the commit, and a Redis blip loses the event. Laravel's afterCommit only fixes the first case. The outbox fixes both, because the event is just another row in the same commit.
2. A poller is simple, needs no extra infrastructure, and fits a small Laravel shop, but it adds a little latency and polling load. CDC (Debezium reading the binlog, for example) is near-real-time with no polling, but it's another system to run and monitor. It pays off at larger scale, or when many services consume the events.
3. The customer should get exactly one email. Store a dedupe key, like orders.confirmation_email_sent_at or a unique row keyed by event id, and check it before sending.

Interview one-liner

"Write the event to an outbox table in the same transaction as the order, have a separate publisher relay it with SKIP LOCKED batching, and make every consumer idempotent because delivery is at-least-once."

Conclusion

The outbox guarantees that a committed order eventually produces its event, and nothing more. Duplicates are still possible, so every consumer needs its own dedupe, and the age of the oldest unpublished row is the alert that tells you the relay has stopped.

Try it yourself

Create an outbox table in a Laravel app and change one controller so it inserts an outbox row inside the same DB::transaction as the main write. Write an artisan command that publishes unpublished rows inside a DB::transaction using ->lock('FOR UPDATE SKIP LOCKED'), dispatches a job for each and sets published_at.

Run two copies of the command at once and confirm no row is published twice. Then kill one mid-batch and see what the consumer receives.

---

This is part of my daily System Design Drills series.