What is the transactional outbox pattern, and how does it solve the dual-write problem between a database and a message broker?

#The dual-write problem

Say an order service needs to do two things when an order is placed: persist the order in its database, and publish an OrderCreated event so inventory, billing, and notifications can react. These are two independent systems with no shared transaction between them -- a relational database does not participate in the same ACID transaction as a Kafka broker or a RabbitMQ exchange. If you commit the database write first and then publish, a crash or a broker outage between the two steps leaves you with a persisted order that nothing downstream ever hears about. If you publish first and then commit, a failed commit leaves you with an event describing an order that does not exist. Wrapping both in a two-phase-commit transaction is possible in theory but rarely available or desirable in practice -- most brokers don't support XA, and 2PC's own failure modes (a coordinator that dies mid-commit) are their own can of worms.

#The fix: make only one write atomic, and relay the rest

The transactional outbox pattern sidesteps the problem instead of solving it head-on: rather than writing to two systems, you write to one. The business row and a record of the event to be published both go into the same local database transaction, in an outbox table. Because that is a single ACID transaction against a single database, it is trivially atomic -- either both rows exist or neither does, with no window in between. A separate process, the relay (or poller), reads unpublished rows from the outbox and publishes them to the broker on its own schedule, independent of the original request.

#The write side: business row plus outbox row, one transaction

import json
import uuid

def create_order(conn, order):
    cur = conn.cursor()
    cur.execute(
        'INSERT INTO orders (id, customer_id, total_cents, status) '
        'VALUES (%s, %s, %s, %s)',
        (order['id'], order['customer_id'], order['total_cents'], 'CREATED')
    )

    payload = json.dumps({
        'order_id': order['id'],
        'customer_id': order['customer_id'],
        'total_cents': order['total_cents'],
    })

    cur.execute(
        'INSERT INTO outbox (id, aggregate_type, aggregate_id, event_type, '
        'payload, created_at, published) '
        'VALUES (%s, %s, %s, %s, %s, now(), false)',
        (str(uuid.uuid4()), 'Order', order['id'], 'OrderCreated', payload)
    )

    conn.commit()  # one ACID transaction: both rows exist, or neither does
    cur.close()

#The relay: publish, then mark published

import time

def relay_loop(conn, producer, poll_interval_seconds=1):
    while True:
        cur = conn.cursor()
        cur.execute(
            'SELECT id, aggregate_id, event_type, payload FROM outbox '
            'WHERE published = false ORDER BY created_at ASC LIMIT 100'
        )
        rows = cur.fetchall()

        for row_id, aggregate_id, event_type, payload in rows:
            producer.produce(
                topic='order-events',
                key=aggregate_id,
                value=payload,
                headers={'event_type': event_type}
            )
            producer.flush()

            # Marked published only after the broker has the message.
            # A crash between produce() and this UPDATE causes a
            # duplicate publish, not a loss -- consumers must dedup.
            cur.execute(
                'UPDATE outbox SET published = true WHERE id = %s',
                (row_id,)
            )
            conn.commit()

        cur.close()
        time.sleep(poll_interval_seconds)

Notice the order of operations inside the loop: publish first, mark published second. Reversing that order would reopen the exact dual-write problem the pattern exists to close -- a crash after marking published but before the broker actually has the message would silently drop the event. Publishing first and marking second means the only failure mode left is a duplicate publish, which is the reason the outbox pattern and idempotent? consumers are meant to be used together -- see the dedup-table pattern for the consumer side of this same problem.

#Two ways to build the relay

🧠 Check your understanding
0/3 · 0/3 answered
1. What specific failure does the transactional outbox pattern prevent?
The message broker running out of disk spaceConsumers processing events out of orderSlow queries against the orders tableA crash between committing a database write and publishing the corresponding event, which would leave the two systems inconsistent
2. Why must the outbox row be written in the exact same local transaction as the business row?
Because that is the only place true atomicity is available -- a single database transaction is ACID, but there is no equivalent shared transaction across a database and a brokerBecause the broker requires itTo reduce the size of the outbox tableBecause two INSERT statements cannot otherwise run in the same request
3. What delivery guarantee does the relay give the message broker, and what does that imply for consumers?
At-most-once delivery, so some events may simply be lostAt-least-once delivery, so consumers still need to be idempotent, since a crash after publishing but before marking a row published causes a re-publishNo delivery guarantee at allExactly-once delivery, so consumers need no special handling