Transactional Outbox Pattern in PHP: Stop Losing Events

Every PHP team that adds a queue hits the same bug. The order saves, the process crashes, and the “order created” event never leaves. The transactional outbox pattern exists to kill exactly that bug. I hit it on a client checkout flow where about one order in a few thousand never reached the invoicing service. Nothing logged an error, because nothing failed. The process simply died between the commit and the publish. This guide shows the table design, the PHP code, the relay, and the trade-offs I would accept today.

What is the dual-write problem the transactional outbox pattern solves?

The dual-write problem is updating two systems, such as MySQL and a broker, with no shared transaction. One of the two writes can succeed while the other fails. The result is a silent data inconsistency.

Look at the naive code. It commits first and publishes second.

$pdo->beginTransaction();
$pdo->prepare('INSERT INTO orders (id, total) VALUES (?, ?)')->execute([$id, $total]);
$pdo->commit();

// Process can die right here. The order exists, the event never will.
$broker->publish('order.created', ['id' => $id]);

Flip the order and you get the opposite failure. You publish, the transaction rolls back, and downstream services act on an order that does not exist. Both variants are documented in the AWS Prescriptive Guidance guide on this pattern. Two-phase commit would fix it, but most brokers and managed databases do not support it.

The outbox removes the second write from the request path. Your request only ever writes to one system. That system already gives you atomicity for free.

How do you design the outbox table in MySQL?

Use a single append-only table with an auto-increment id, an aggregate id, an event type, a JSON payload, and a nullable published timestamp. The auto-increment id gives the relay a stable ordering key.

1. Create the table with an index on (published_at, id) so the relay scan stays cheap.
2. Store the payload as JSON so the relay never joins back to business tables.
3. Add a unique event UUID so consumers can deduplicate.
4. Keep rows short-lived and purge published rows on a schedule.

CREATE TABLE outbox (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_uuid CHAR(36) NOT NULL UNIQUE,
  aggregate_id VARCHAR(64) NOT NULL,
  event_type VARCHAR(100) NOT NULL,
  payload JSON NOT NULL,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  published_at TIMESTAMP(3) NULL,
  INDEX idx_unpublished (published_at, id)
) ENGINE=InnoDB;

This is the same discipline I use for idempotency keys: the database constraint does the work, not application memory. A unique event id costs 16 bytes of index per row in binary form and prevents a whole class of duplicate bugs downstream.

Why the aggregate id matters for ordering

Ordering only matters per aggregate. Order 42 must see “created” before “shipped”. Order 43 does not care. Store the aggregate id so you can use it as a partition key in Kafka or a message group id in SQS FIFO.

How do you write the outbox row in the same transaction in PHP?

Insert the event row using the same PDO connection and the same open transaction as the business write. If either insert fails, both roll back. That single rule is the whole pattern.

function placeOrder(PDO $pdo, string $id, int $total): void
{
    $pdo->beginTransaction();
    try {
        $pdo->prepare('INSERT INTO orders (id, total) VALUES (?, ?)')
            ->execute([$id, $total]);

        $pdo->prepare(
            'INSERT INTO outbox (event_uuid, aggregate_id, event_type, payload)
             VALUES (UUID(), ?, ?, ?)'
        )->execute([$id, 'order.created', json_encode(['id' => $id, 'total' => $total])]);

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Never call the broker inside this function. The extra insert adds one small indexed write to your transaction. On the checkout flow I mentioned, that cost was invisible next to the existing writes. Teams that care about API performance should still measure it on their own hardware.

In Laravel, wrap the same two inserts in DB::transaction(). In Symfony, use the Doctrine transaction. The rule does not change: one connection, one transaction.

How do you build a relay that does not double-publish with SKIP LOCKED?

Select unpublished rows with FOR UPDATE SKIP LOCKED inside a short transaction, publish them, and mark them published before commit. SKIP LOCKED lets several relay workers run without blocking each other.

The MySQL 8.4 reference manual says this clause removes locked rows from the result set. It also says the feature suits queue-like tables. MySQL added it in 8.0.1.

1. Start a transaction.
2. Select up to 100 rows where published_at IS NULL, ordered by id, with FOR UPDATE SKIP LOCKED.
3. Publish each row to the broker with the event UUID as the message id.
4. Update published_at for those ids.
5. Commit, sleep briefly if the batch was empty, and loop.

function relayBatch(PDO $pdo, Broker $broker, int $limit = 100): int
{
    $pdo->beginTransaction();
    $rows = $pdo->query(
        "SELECT id, event_uuid, aggregate_id, event_type, payload
         FROM outbox WHERE published_at IS NULL
         ORDER BY id LIMIT $limit FOR UPDATE SKIP LOCKED"
    )->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as $r) {
        $broker->publish($r['event_type'], $r['payload'], [
            'message_id' => $r['event_uuid'],
            'key' => $r['aggregate_id'],
        ]);
    }
    if ($rows) {
        $ids = implode(',', array_map('intval', array_column($rows, 'id')));
        $pdo->exec("UPDATE outbox SET published_at = NOW(3) WHERE id IN ($ids)");
    }
    $pdo->commit();
    return count($rows);
}

If the worker crashes after publishing but before commit, the rows stay unpublished. The next worker publishes them again. That is at-least-once delivery, and it is by design.

The gotcha: SKIP LOCKED can reorder events

SKIP LOCKED trades strict ordering for parallelism. Worker A may hold ids 1 to 100 while worker B publishes 101 to 200. Events for one aggregate can then cross batches out of order. The fix is to run a single relay worker per partition, or to hash aggregate ids across workers. I learned this the hard way when a “cancelled” event overtook “created” in staging.

What delivery guarantees does the transactional outbox pattern give you?

The pattern gives at-least-once delivery with per-aggregate ordering, never exactly-once. The microservices.io write-up states the relay may publish a message more than once. Consumers must therefore be idempotent.

Use the event UUID as the dedupe key on the consumer side. A processed-events table with a unique constraint is enough for most PHP services. For a deeper treatment of that consumer side, see my guide to idempotency keys.

Throughput is easy to estimate. A relay that polls every 500 ms and takes 100 rows per batch tops out at 200 events per second per worker. Cut the sleep to 100 ms and you reach about 1,000 per second, with five times the idle queries. Pick the interval from your latency budget, not from habit.

Should you poll the outbox table or use change data capture?

Poll if you run fewer than a few hundred events per second and want zero new infrastructure. Use log-based change data capture such as Debezium when you need low latency or strict commit ordering. Gunnar Morling reports CDC latency in the two-digit millisecond range.

Polling is simple and works on any MySQL host. Its costs are periodic load on the database and weaker ordering across concurrent transactions. CDC tails the binlog, so events leave in commit order with no polling queries. The price is operating Kafka Connect or a similar stack.

Outbox relay strategies compared

Option	Performance	Complexity	When to Use
Polling relay (SKIP LOCKED)	Up to 200 events/s per worker at 500 ms, 100 rows	Low: one PHP daemon	Small and mid services on MySQL 8+
Polling, single worker per partition	Same rate, strict per-aggregate order	Low to medium	Order-sensitive events at modest volume
Log-based CDC (Debezium)	Two-digit millisecond latency	High: Kafka Connect and binlog access	High volume or strict commit ordering
Publish after commit (no outbox)	Fastest, but loses events on crash	None	Only for events you can afford to lose

How do you keep the outbox table from growing forever?

Delete published rows on a schedule, in small batches, after a retention window. A seven-day window keeps enough history for replay and debugging.

Run a nightly job that deletes rows where published_at is older than seven days, 5,000 rows at a time. Large single deletes hold locks and bloat the undo log. Monitor one number: the age of the oldest unpublished row. If it exceeds your latency budget, page someone. That metric catches a dead relay faster than any error log. It also respects SOLID design principles by keeping the relay a single-purpose component.

• The transactional outbox pattern guarantees an event is published if and only if its database transaction commits.
• Delivery is at-least-once, so every consumer must deduplicate using a unique event id.
• MySQL 8.0.1 added SKIP LOCKED, which lets several relay workers read one outbox table without blocking.
• A polling relay at 500 ms intervals with 100-row batches handles roughly 200 events per second per worker.
• SKIP LOCKED with parallel workers can reorder events for one aggregate unless you partition by aggregate id.
• Log-based CDC delivers outbox events in commit order with latency in the two-digit millisecond range.

Frequently Asked Questions

What is the transactional outbox pattern?

It is a technique that writes outgoing events to an outbox table in the same database transaction as business data. A separate relay later publishes those rows to a message broker. This avoids the dual-write problem without needing two-phase commit.

Why use the outbox pattern instead of publishing directly to Kafka or RabbitMQ?

Direct publishing cannot be atomic with your database commit. If the process crashes between the two steps, you lose the event or publish a phantom one. The outbox ties the event to the commit, so both succeed or both fail together.

Does the outbox pattern guarantee exactly-once delivery?

No. It guarantees at-least-once delivery. A relay can crash after publishing but before marking the row, so it publishes again. Consumers should deduplicate with a unique event id to get effectively-once processing.

How often should the outbox relay poll the database?

Choose the interval from your latency budget. Every 500 ms suits most asynchronous notifications. Every 100 ms cuts latency but multiplies idle queries by five. If you need tens of milliseconds, use log-based change data capture instead of polling.

What is the difference between the outbox pattern and change data capture?

The outbox is a table your application writes. Change data capture is a way to read it. A polling relay queries the table, while CDC tails the database log. You can combine them: write an outbox table and let Debezium stream it.

The transactional outbox pattern is unglamorous, and that is why it works. It turns a distributed failure mode into a local transaction you already trust. The part people skip is monitoring. Alert on the age of the oldest unpublished row, and you will know within minutes when the relay stops. Build the table this week, move one critical event onto it, and watch the lost-event bug disappear. If you want the consumer half, read the idempotency guide next.