Transactional outbox: a crash and retry example

Committing an order and publishing its notification are two different operations. Put the database commit first and a crash can leave an order with no notification. Put the publish first and another service may act on an order that never commits. Changing the order of those two calls does not remove the gap.

Close the gap between two writes#

In this illustrative scenario, order O7 should produce event E42. Save O7 and an outbox record for E42 in one local transaction. Either both commit or neither does. Only after commit does a separate worker, the relay, read pending records and publish them. The broker call is outside the producer’s database transaction.

First choose a crash after commit. Direct publishing leaves no pending record for the restarted worker; the outbox retains E42. Then move the crash to after publishing and turn consumer deduplication off. The relay finds E42 pending and sends it again, producing two effects in the model. Turn the processed-id check on and repeat: there are still two published copies, but only one committed consumer effect.

This is the boundary described by the Transactional Outbox pattern: the local state change and pending message are atomic; delivery to a different system is separate. The broader transaction-boundaries guide explains how to choose what belongs in that local commit.

Run the pending-message example in SQLite#

Save this as outbox.py and run python3 outbox.py. It uses Python’s standard library. The producer has a temporary SQLite file; the consumer has a separate in-memory database. Calling consume stands in for delivery, so this tests local transactions and repeated consumption, not a broker or concurrent relay workers.

# Complete illustrative Python 3 / SQLite example.
# Reopening a file models restart; it is not an OS crash or real-broker test.
import sqlite3
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory


def consume(db, event_id):
    with db:
        db.execute("BEGIN")
        inserted = db.execute(
            "INSERT INTO processed VALUES (?) ON CONFLICT(id) DO NOTHING",
            (event_id,),
        ).rowcount
        if inserted:
            db.execute("INSERT INTO effects VALUES (?)", (event_id,))


with TemporaryDirectory() as directory:
    path = Path(directory) / "producer.db"
    with closing(sqlite3.connect(":memory:", isolation_level=None)) as consumer:
        consumer.executescript("""
            CREATE TABLE processed (id TEXT PRIMARY KEY);
            CREATE TABLE effects (event_id TEXT NOT NULL);
        """)
        with closing(sqlite3.connect(path, isolation_level=None)) as producer:
            producer.executescript("""
                CREATE TABLE orders (id TEXT PRIMARY KEY);
                CREATE TABLE outbox (
                    id TEXT PRIMARY KEY,
                    order_id TEXT NOT NULL,
                    sent INTEGER NOT NULL DEFAULT 0 CHECK (sent IN (0, 1))
                );
            """)
            # An aborted transaction leaves neither write behind.
            producer.execute("BEGIN")
            producer.execute("INSERT INTO orders VALUES ('aborted')")
            producer.execute("INSERT INTO outbox VALUES ('aborted-event', 'aborted', 0)")
            producer.execute("ROLLBACK")
            assert producer.execute("SELECT COUNT(*) FROM orders").fetchone()[0] == 0
            assert producer.execute("SELECT COUNT(*) FROM outbox").fetchone()[0] == 0
            with producer:
                producer.execute("BEGIN")
                producer.execute("INSERT INTO orders VALUES ('O7')")
                producer.execute("INSERT INTO outbox VALUES ('E42', 'O7', 0)")
            first = producer.execute("SELECT id FROM outbox WHERE sent = 0").fetchone()[0]
            consume(consumer, first)
            # Publish/consume succeeded; deliberately omit the sent marker.

        with closing(sqlite3.connect(path, isolation_level=None)) as restarted:
            again = restarted.execute("SELECT id FROM outbox WHERE sent = 0").fetchone()[0]
            consume(consumer, again)
            restarted.execute("UPDATE outbox SET sent = 1 WHERE id = ?", (again,))
            assert first == again == "E42"
            assert restarted.execute("SELECT COUNT(*) FROM orders").fetchone()[0] == 1
            assert restarted.execute("SELECT COUNT(*) FROM outbox WHERE sent = 0").fetchone()[0] == 0
        assert consumer.execute("SELECT COUNT(*) FROM effects").fetchone()[0] == 1
        print("two deliveries of E42; one committed consumer effect")

The first transaction rolls back, and both tables remain empty. The successful transaction saves the order and E42. Closing and reopening the producer database finds E42 still pending because its sent marker was deliberately omitted. Both deliveries use the same id, and the consumer’s unique processed-id row prevents a second effect. The output is two deliveries of E42; one committed consumer effect.

SQLite’s transaction documentation defines the local commit and rollback behaviour used here. Reopening a file is a controlled restart boundary, not proof of power-loss durability under every filesystem or database setting.

A sent marker does not prevent every duplicate#

Marking sent before publishing trades duplicates for loss: a crash between those actions leaves work marked complete that never reached the broker. Marking it afterwards preserves retryable work but admits the duplicate shown above. A publish timeout can also mean that the broker accepted the message but the reply was lost.

The Idempotent Consumer pattern puts a processed-message identifier and the business update in the consumer’s own transaction. Use a stable id across retries and scope it to the subscriber when several subscribers share the table. Keep the identifier for the supported replay window. Recording it before an unrelated commit can lose work; recording it after that commit can repeat the effect. Safe retries follows the same question across request boundaries.

An email or payment API is not part of that local transaction. Use its supported idempotency contract or another explicitly coordinated workflow. Do not label all downstream work exactly-once because the example’s single database effect happens once.

Ordering also needs a design. AWS’s outbox guidance calls out duplicate processing and message order. A production design may carry an entity id and sequence number and coordinate workers accordingly. A timestamp or an ORDER BY in one worker does not prevent a second worker from publishing or finishing first. The existing event-delivery article separates log order from processing order.

What a real relay still needs#

The SQLite example deliberately has one message and one worker. A real implementation must define how workers claim pending rows, recover abandoned claims, back off after errors and surface messages they cannot publish. Observe the age of the oldest pending work as well as the count: a busy relay can still leave one important message stuck.

Delivery depends on the relay eventually running, recoverable failures, retained work and the broker’s acceptance contract. The outbox alone cannot overcome permanent unavailability or an invalid payload. Plan cleanup and reconciliation against those conditions, rather than treating an empty table as proof that every downstream action completed.

Ordinary current-state tables can use this pattern; event sourcing is a separate choice about reconstructing state from history. Use an outbox when the local change must leave durable publication work. It is unnecessary for a change that has no cross-system delivery requirement.