Why is the outbox row written in the same transaction as the order, but published separately?

The database transaction is the only place true atomicity is available — either both the order and outbox rows commit, or neither does, guaranteed by the database itself. Publishing has to happen afterward because there's no way to make an external broker call part of that same atomic transaction, which is exactly the dual-write problem this pattern works around instead of solving directly.

What does 'at least once' actually mean here?

If the relay crashes after calling the broker but before marking its outbox row published, the next relay run sees an unpublished row and republishes it — the consumer receives the same event twice. The outbox pattern trades 'might lose an event' for 'might deliver an event more than once,' which is why it's always paired with idempotent consumers (the next lesson).

Does the relay need to run constantly?

It needs to run often enough that consumers see events promptly, but running it against an empty outbox is a safe no-op — there's no harm in polling on a fixed schedule (every few seconds, say) rather than needing to be triggered exactly when a new row appears.

Could the outbox table just BE the event log from the event-sourcing lessons?

In a system that's both event-sourced and outbox-based, yes — the same append-only table can serve both purposes. This lesson keeps them conceptually separate because a system doesn't have to be event-sourced at all to still have the dual-write problem; the outbox pattern applies to any database write that needs a reliable side-effect published alongside it.

Transactional Outbox: Committing the Event With the Data

Committing an order and its outbox row happens inside one database transaction — the same all-or-nothing guarantee that protects a single row now protects the event alongside it.

The event survives even when the broker is down

import sqlite3

class BrokerDown(Exception):
    pass

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER)")
conn.execute("CREATE TABLE outbox (id INTEGER PRIMARY KEY, payload TEXT, published INTEGER DEFAULT 0)")
conn.commit()

def place_order(order_id, total):
    conn.execute("INSERT INTO orders VALUES (?, ?)", (order_id, total))
    conn.execute("INSERT INTO outbox (payload, published) VALUES (?, 0)",
                 (f"OrderPlaced:{order_id}:{total}",))
    conn.commit()
    print(f"  order {order_id} and its outbox row committed together, in one transaction")

place_order(1, 75)

def relay(publish_fn):
    rows = conn.execute("SELECT id, payload FROM outbox WHERE published = 0").fetchall()
    for outbox_id, payload in rows:
        try:
            publish_fn(payload)
            conn.execute("UPDATE outbox SET published = 1 WHERE id = ?", (outbox_id,))
            conn.commit()
            print(f"  relayed and marked published: {payload}")
        except BrokerDown as e:
            print(f"  relay failed for {payload}, will retry next run: {e}")

def broker_down(payload):
    raise BrokerDown("message broker unreachable")

def broker_up(payload):
    pass

print("First relay attempt, broker down:")
relay(broker_down)
unpublished = conn.execute("SELECT COUNT(*) FROM outbox WHERE published = 0").fetchone()[0]
print(f"Still unpublished: {unpublished}")

print("Second relay attempt, broker back up:")
relay(broker_up)
unpublished = conn.execute("SELECT COUNT(*) FROM outbox WHERE published = 0").fetchone()[0]
print(f"Still unpublished: {unpublished}")

$ python transactional_outbox.py
  order 1 and its outbox row committed together, in one transaction
First relay attempt, broker down:
  relay failed for OrderPlaced:1:75, will retry next run: message broker unreachable
Still unpublished: 1
Second relay attempt, broker back up:
  relayed and marked published: OrderPlaced:1:75
Still unpublished: 0

Unlike the previous lesson’s naive version, a broker outage here loses nothing — the outbox row stays at published = 0 for as long as the broker is down, and the relay simply tries again whenever it next runs. The event was never at risk, because it was already durable in the same transaction as the order itself.

Practice

1. Crash before the transaction commits, and confirm neither the order nor the outbox row survives.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER)")
conn.execute("CREATE TABLE outbox (id INTEGER PRIMARY KEY, payload TEXT, published INTEGER DEFAULT 0)")
conn.commit()

def place_order_crashes_before_commit(order_id, total):
    conn.execute("INSERT INTO orders VALUES (?, ?)", (order_id, total))
    conn.execute("INSERT INTO outbox (payload, published) VALUES (?, 0)",
                 (f"OrderPlaced:{order_id}:{total}",))
    raise RuntimeError("process killed before commit()")

try:
    place_order_crashes_before_commit(1, 50)
except RuntimeError as e:
    print(f"Crashed: {e}")

conn.rollback()

orders = conn.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
outbox = conn.execute("SELECT COUNT(*) FROM outbox").fetchone()[0]
print(f"Orders after rollback: {orders}")
print(f"Outbox rows after rollback: {outbox}")

$ python crash_before_commit.py
Crashed: process killed before commit()
Orders after rollback: 0
Outbox rows after rollback: 0

This is the actual guarantee the pattern relies on: a crash before commit() leaves both tables exactly as they were, so there’s never a state where the order exists without its outbox row (or the reverse) — the two inserts share one atomic unit.

2. Crash the relay between publishing and marking the row published, and observe the duplicate delivery.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER)")
conn.execute("CREATE TABLE outbox (id INTEGER PRIMARY KEY, payload TEXT, published INTEGER DEFAULT 0)")
conn.commit()

conn.execute("INSERT INTO orders VALUES (1, 75)")
conn.execute("INSERT INTO outbox (payload, published) VALUES ('OrderPlaced:1:75', 0)")
conn.commit()

published_to_broker = []

def relay_crashes_after_publish():
    rows = conn.execute("SELECT id, payload FROM outbox WHERE published = 0").fetchall()
    for outbox_id, payload in rows:
        published_to_broker.append(payload)
        print(f"  published: {payload}")
        raise RuntimeError("relay process killed right here, before marking published")

try:
    relay_crashes_after_publish()
except RuntimeError as e:
    print(f"Relay crashed: {e}")

def relay(publish_fn):
    rows = conn.execute("SELECT id, payload FROM outbox WHERE published = 0").fetchall()
    for outbox_id, payload in rows:
        publish_fn(payload)
        conn.execute("UPDATE outbox SET published = 1 WHERE id = ?", (outbox_id,))
        conn.commit()

def publish_ok(payload):
    published_to_broker.append(payload)
    print(f"  published: {payload}")

print("Relay runs again after restart:")
relay(publish_ok)

print(f"Broker received this payload {published_to_broker.count('OrderPlaced:1:75')} time(s): {published_to_broker}")

$ python duplicate_delivery.py
  published: OrderPlaced:1:75
Relay crashed: relay process killed right here, before marking published
Relay runs again after restart:
  published: OrderPlaced:1:75
Broker received this payload 2 time(s): ['OrderPlaced:1:75', 'OrderPlaced:1:75']

The outbox row was still published = 0 when the relay restarted, because the crash happened before the UPDATE. This is the “at least once” half of the tradeoff — the event is never lost, but it can genuinely arrive twice, which the next lesson’s idempotent consumer is what actually protects against.

3. Run the relay repeatedly against an empty outbox and confirm it's a safe no-op.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE outbox (id INTEGER PRIMARY KEY, payload TEXT, published INTEGER DEFAULT 0)")
conn.commit()

def relay(publish_fn):
    rows = conn.execute("SELECT id, payload FROM outbox WHERE published = 0").fetchall()
    for outbox_id, payload in rows:
        publish_fn(payload)
        conn.execute("UPDATE outbox SET published = 1 WHERE id = ?", (outbox_id,))
        conn.commit()
    return len(rows)

def publish_ok(payload):
    print(f"  published: {payload}")

for run in range(1, 4):
    processed = relay(publish_ok)
    print(f"Relay run {run}: processed {processed} row(s)")

$ python empty_outbox_noop.py
Relay run 1: processed 0 row(s)
Relay run 2: processed 0 row(s)
Relay run 3: processed 0 row(s)

Nothing about relay assumes there’s anything to process — an empty result from the SELECT just means the for loop body never runs, which is why polling it on a fixed schedule is safe even when there’s nothing new to relay most of the time.

4. Place three orders before running the relay once, and confirm all three get processed in a single pass.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER)")
conn.execute("CREATE TABLE outbox (id INTEGER PRIMARY KEY, payload TEXT, published INTEGER DEFAULT 0)")
conn.commit()

def place_order(order_id, total):
    conn.execute("INSERT INTO orders VALUES (?, ?)", (order_id, total))
    conn.execute("INSERT INTO outbox (payload, published) VALUES (?, 0)",
                 (f"OrderPlaced:{order_id}:{total}",))
    conn.commit()

place_order(1, 75)
place_order(2, 20)
place_order(3, 40)

def relay(publish_fn):
    rows = conn.execute("SELECT id, payload FROM outbox WHERE published = 0").fetchall()
    for outbox_id, payload in rows:
        publish_fn(payload)
        conn.execute("UPDATE outbox SET published = 1 WHERE id = ?", (outbox_id,))
        conn.commit()
    return len(rows)

def publish_ok(payload):
    print(f"  published: {payload}")

processed = relay(publish_ok)
print(f"Processed {processed} pending row(s) in one pass")

$ python batch_relay.py
  published: OrderPlaced:1:75
  published: OrderPlaced:2:20
  published: OrderPlaced:3:40
Processed 3 pending row(s) in one pass

The relay doesn’t care how many orders piled up since its last run — it just processes whatever’s currently unpublished, which is what makes it safe to run on a fixed interval instead of needing to be woken up for every single write.

The event survives even when the broker is down

Practice

Frequently Asked Questions