How to Implement the Transactional Outbox Pattern in Python to Avoid Dual Writes

When a service needs to update its own database and tell another system about that change, in that order, at the same time, it runs into one of the oldest reliability problems in distributed systems: the dual write. This tutorial teaches you how to solve it from scratch in Python using the transactional outbox pattern, a technique that turns two separate, failure-prone operations into a single atomic one. You will build a naive version that loses data, watch it fail, then fix it with a real outbox table, a crash-safe relay process, an idempotent consumer, and safe handling for multiple relay workers running at once.

What you will accomplish and why it matters

Picture a simple order-processing service. When a customer places an order, the service needs to do two things: save the order to its database, and notify a downstream system (an email service, a shipping service, an analytics pipeline) that the order happened. Those look like one logical operation, but they are actually two separate writes to two separate systems, and that gap between them is where things go wrong.

This is called the dual write problem. As the pattern’s canonical description from Chris Richardson’s microservices.io puts it, a service command “typically needs to create/update/delete aggregates in the database and send messages/events to a message broker,” and it “must atomically update the database and send messages in order to avoid data inconsistencies and bugs.” The trouble is that a traditional distributed transaction spanning both the database and the message broker (something like two-phase commit) is usually not available, and even when it is, tightly coupling your service to both systems that way is rarely worth the cost. AWS’s own guidance on the pattern frames the failure mode plainly: “if the database update is successful but the event notification fails, the downstream service will not be aware of the change, and the system can enter an inconsistent state.”

The transactional outbox pattern fixes this by never trying to write to two systems in the first place. Instead of writing to your database AND sending a message, you write to your database TWICE, in the same transaction: once for your actual business data, and once for a row describing the event you want to send. A separate background process, usually called a message relay, then reads those event rows and delivers them to wherever they need to go, marking each one as delivered once it succeeds. Because both writes happen in one transaction, they succeed or fail together. There is no gap left for things to go wrong in.

Along the way you will personally reproduce three things that trip people up in production: the dual-write bug itself, the fact that outbox delivery is at-least-once (not exactly-once, meaning a consumer has to expect and safely handle duplicates), and a real race condition that emerges the moment you run more than one relay worker for scale or high availability.

Prerequisites

• Python 3.10 or later (this tutorial was built and tested on Python 3.13). No prior distributed-systems experience assumed; every term is defined as it comes up.
• Comfort running Python scripts from a terminal and reading basic SQL (SELECT, INSERT, UPDATE).
• pip, to install two packages: fastapi and uvicorn. Everything else (sqlite3, threading, json) is in Python’s standard library, so nothing needs Docker, Node.js, or any external database server.
• Basic familiarity with HTTP requests (you will use the requests library, which is commonly pre-installed alongside Python).

Step 1: Reproduce the dual-write bug

Before building the fix, it helps to feel the actual bug. Here is the naive version of place_order() that most people write first: insert the order, commit it, and then call out to a notification function separately.

def place_order(customer_email, amount_cents):
    conn = sqlite3.connect(DB_PATH)
    cur = conn.execute(
        "INSERT INTO orders (customer_email, amount_cents, created_at) VALUES (?, ?, ?)",
        (customer_email, amount_cents, time.strftime("%Y-%m-%d %H:%M:%S")),
    )
    order_id = cur.lastrowid
    conn.commit()  # write #1: the order now exists, permanently
    conn.close()

    # write #2: a completely separate operation, with its own failure modes
    send_email_notification(order_id, customer_email, amount_cents)
    return order_id

send_email_notification() here stands in for any downstream call: an email provider’s API, a message broker client, a webhook. In the demo it deliberately raises a ConnectionError for one specific order, simulating a timeout. Run it and place three orders:

Placing 3 orders...
  [notification service] sent confirmation for order 1 to [email protected]
order 1 placed and notified OK
order for [email protected] placed, but notification FAILED: email provider timed out
  [notification service] sent confirmation for order 3 to [email protected]
order 3 placed and notified OK

Orders actually in the database:
  (1, '[email protected]', 4200)
  (2, '[email protected]', 1999)
  (3, '[email protected]', 7500)

Notice: order 2 ([email protected]) exists permanently in the database, but its confirmation email was silently lost. Nothing will ever retry it.

That is the whole bug in one run. Order 2 is sitting in the database right now, fully paid for as far as the system is concerned, and the customer has no idea it happened. There is no queue holding the failed notification, no flag marking the order as “needs a retry.” The information needed to fix this (that a notification was owed for order 2) was never durably recorded anywhere. It just evaporated the moment the exception was raised.

Step 2 and 3: Design the outbox table and write to it atomically

The fix starts with a second table living in the exact same database as your business data:

CREATE TABLE outbox (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type TEXT NOT NULL,
    payload TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    claimed_at TEXT,
    claimed_by TEXT,
    processed_at TEXT,
    created_at TEXT NOT NULL
);

Each row is one event waiting to be delivered. status starts at 'pending' and moves to 'sent' once delivery succeeds (you will add a 'processing' state in Step 6, for claiming). payload holds everything the eventual consumer needs to know, serialized as JSON.

The part that actually fixes the bug is this: the order row and the outbox row are inserted inside the same transaction, with a single commit at the end.

def place_order(customer_email, amount_cents, fail_after_order_insert=False):
    conn = sqlite3.connect(DB_PATH)
    try:
        conn.execute("BEGIN")
        cur = conn.execute(
            "INSERT INTO orders (customer_email, amount_cents, created_at) VALUES (?, ?, ?)",
            (customer_email, amount_cents, time.strftime("%Y-%m-%d %H:%M:%S")),
        )
        order_id = cur.lastrowid

        if fail_after_order_insert:
            raise RuntimeError("simulated failure between the two inserts")

        payload = json.dumps(
            {"order_id": order_id, "customer_email": customer_email, "amount_cents": amount_cents}
        )
        conn.execute(
            "INSERT INTO outbox (event_type, payload, created_at) VALUES (?, ?, ?)",
            ("order.confirmed", payload, time.strftime("%Y-%m-%d %H:%M:%S")),
        )
        conn.commit()  # single commit: order row + outbox row succeed or fail together
        return order_id
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

Notice there is no call to any external system in this function at all anymore. Placing an order now only ever touches this one SQLite database, which means it can only ever succeed or fail as a whole. To prove that, the demo forces a RuntimeError between the two inserts and checks both tables afterward:

Attempt 1: force a failure between the two inserts
  place_order raised: simulated failure between the two inserts

-- after the forced failure (should be EMPTY, not a half-written order) --
orders: []
outbox: []

Attempt 2: normal success path
  order 1 placed

-- after a normal successful order (order row AND outbox row both present) --
orders: [(1, '[email protected]')]
outbox: [(1, 'order.confirmed', 'pending')]

After the forced failure, both tables are completely empty, not “order exists but no outbox row.” The conn.rollback() in the except block undoes the order insert too, because it was part of the same uncommitted transaction. This is the entire trick behind the pattern: there is no dual write left to get half-done, because there is only ever one write, to one database, wrapped in one transaction.

Step 4: Build the relay and deliver to a real downstream service

An outbox row sitting in 'pending' status is not useful on its own. Something has to notice it and actually deliver it. That something is called the message relay (or “polling publisher,” in the pattern’s own terminology): a background process that periodically polls the outbox table for pending rows and pushes them out.

To make this real rather than simulated, spin up a small FastAPI service that plays the role of the downstream system (an email/notification service). It exposes a single POST /notify endpoint:

@app.post("/notify")
def notify(payload: NotifyPayload):
    conn = get_conn()
    cur = conn.execute(
        "INSERT OR IGNORE INTO sent_notifications (event_id, order_id, customer_email, amount_cents) "
        "VALUES (?, ?, ?, ?)",
        (payload.event_id, payload.order_id, payload.customer_email, payload.amount_cents),
    )
    conn.commit()
    was_new = cur.rowcount == 1
    conn.close()
    return {"status": "sent" if was_new else "duplicate_ignored", "was_new": was_new}

Run it in one terminal:

uvicorn notification_service:app --host 127.0.0.1 --port 8811

Then the relay itself is a straightforward poll-deliver-update loop, run here as a single pass for clarity:

def run_relay_once(endpoint=NOTIFY_URL, limit=50):
    conn = sqlite3.connect(DB_PATH)
    rows = conn.execute(
        "SELECT id, event_type, payload FROM outbox WHERE status = 'pending' ORDER BY id ASC LIMIT ?",
        (limit,),
    ).fetchall()

    delivered = 0
    for outbox_id, event_type, payload_json in rows:
        payload = json.loads(payload_json)
        body = {
            "event_id": outbox_id,
            "order_id": payload["order_id"],
            "customer_email": payload["customer_email"],
            "amount_cents": payload["amount_cents"],
        }
        resp = requests.post(endpoint, json=body, timeout=5)
        resp.raise_for_status()
        conn.execute(
            "UPDATE outbox SET status = 'sent', processed_at = datetime('now') WHERE id = ?",
            (outbox_id,),
        )
        conn.commit()
        delivered += 1
    conn.close()
    return delivered

Running the full flow (place 3 orders, then run one relay poll cycle against the live service) produces this real output:

Placing 3 orders (each writes an order row + a pending outbox row)...
  order 1 placed for [email protected]
  order 2 placed for [email protected]
  order 3 placed for [email protected]

outbox has 3 pending rows before the relay runs

Running one relay poll cycle...
  relay delivered outbox row 1 (order.confirmed) -> {'status': 'sent', 'was_new': True}
  relay delivered outbox row 2 (order.confirmed) -> {'status': 'sent', 'was_new': True}
  relay delivered outbox row 3 (order.confirmed) -> {'status': 'sent', 'was_new': True}
delivered 3 events

outbox row statuses after the relay ran: [(1, 'sent'), (2, 'sent'), (3, 'sent')]
what the notification service actually received: [{'event_id': 1, 'order_id': 1, 'customer_email': '[email protected]', 'amount_cents': 4200}, {'event_id': 2, 'order_id': 2, 'customer_email': '[email protected]', 'amount_cents': 1999}, {'event_id': 3, 'order_id': 3, 'customer_email': '[email protected]', 'amount_cents': 7500}]

Every order that was placed now has a matching, verified delivery on the notification service’s side, and unlike Step 1, this held true even though the relay is a completely separate process that could crash, restart, or fall behind without losing a single event: the record of “this needs to be delivered” survives in the database until it is confirmed sent.

Step 5: At-least-once delivery is not the same as exactly-once

Here is the part almost every outbox tutorial glosses over. What happens if the relay successfully delivers an event, but crashes (gets killed, runs out of memory, gets redeployed) before it can run the UPDATE ... SET status = 'sent' line? From the outbox table’s point of view, that row is still 'pending'. The next poll cycle will pick it up and deliver it again.

This is called at-least-once delivery, and it is a deliberate, unavoidable property of the pattern, not a bug to eliminate. AWS’s guidance is explicit about this trade-off: “the events processing service might send out duplicate messages or events, so we recommend that you make the consuming service idempotent by tracking the processed messages.” The relay’s job is to guarantee the event gets delivered at least once. Guaranteeing it gets delivered exactly once is the consumer’s job.

To see why this matters, the demo simulates a worker that delivers successfully and then “dies” before marking the row sent, twice in a row, once against the naive /notify-naive endpoint (no deduplication at all, just an append-only list) and once against the idempotent /notify endpoint from Step 4 (dedupes on event_id using INSERT OR IGNORE):

======================================================================
WITHOUT an idempotent consumer (/notify-naive, no dedup):
======================================================================
order 1 placed, one outbox row created

Poll cycle 1 (worker crashes right after the HTTP call succeeds):
  delivered outbox row 1 to /notify-naive -> {'status': 'sent', 'total_emails_sent_so_far': 1}  (then the worker 'dies')

Poll cycle 2 (row still pending, gets redelivered, this time completes normally):
  delivered outbox row 1 to /notify-naive -> {'status': 'sent', 'total_emails_sent_so_far': 2}  (then the worker 'dies')

what the notification service actually has on file: [{'event_id': 1, 'order_id': 1, 'customer_email': '[email protected]', 'amount_cents': 2500}, {'event_id': 1, 'order_id': 1, 'customer_email': '[email protected]', 'amount_cents': 2500}]

>>> customer [email protected] was sent 2 confirmation emails for ONE order <<<

======================================================================
WITH an idempotent consumer (/notify, dedup by event_id):
======================================================================
order 1 placed, one outbox row created

Poll cycle 1 (worker crashes right after the HTTP call succeeds):
  delivered outbox row 1 to /notify -> {'status': 'sent', 'was_new': True}  (then the worker 'dies')

Poll cycle 2 (row still pending, gets redelivered, this time completes normally):
  delivered outbox row 1 to /notify -> {'status': 'duplicate_ignored', 'was_new': False}  (then the worker 'dies')

what the notification service actually has on file: [{'event_id': 1, 'order_id': 1, 'customer_email': '[email protected]', 'amount_cents': 2500}]

>>> customer [email protected] was sent 1 confirmation email for the same redelivery <<<

Same exact redelivery scenario, two completely different customer-facing outcomes. The fix costs almost nothing: a unique key on the event’s identifier and an INSERT OR IGNORE instead of a plain INSERT. If your downstream consumer already deduplicates by an idempotency key, this pairs directly with a synchronizer-token style approach; see How to Prevent Duplicate Charges in an API With Idempotency Keys in Python for the same core technique applied to inbound API requests instead of outbound events.

Step 6: Running more than one relay worker safely

A single relay process is a single point of failure and, eventually, a throughput bottleneck. The obvious fix is to run two or more relay workers in parallel. The obvious bug that follows is two workers grabbing the same pending row and delivering it twice, at the same moment, for no reason related to a crash at all.

The fix is an atomic “claim” step: a worker marks a row as 'processing' and records that it did so, in one statement, and only proceeds if that statement actually changed a row.

def try_claim_one_row(worker_id):
    conn = get_conn()  # sqlite3.connect(DB_PATH, timeout=30) + PRAGMA busy_timeout = 30000
    cur = conn.execute(
        """
        UPDATE outbox
        SET status = 'processing', claimed_at = datetime('now'), claimed_by = ?
        WHERE id = (
            SELECT id FROM outbox WHERE status = 'pending' ORDER BY id LIMIT 1
        )
        AND status = 'pending'
        """,
        (worker_id,),
    )
    conn.commit()
    if cur.rowcount == 0:
        conn.close()
        return None
    row_id = conn.execute(
        "SELECT id FROM outbox WHERE claimed_by = ? AND status = 'processing' ORDER BY claimed_at DESC LIMIT 1",
        (worker_id,),
    ).fetchone()[0]
    conn.close()
    return row_id

This works because SQLite only ever lets one connection hold a write lock at a time. According to SQLite’s own locking documentation, a process must acquire a RESERVED lock before writing, and “only one process at a time can hold a RESERVED lock;” if a second writer tries while that lock is held, its attempt “fails and returns SQLITE_BUSY.” Setting PRAGMA busy_timeout = 30000 (the exact syntax documented on SQLite’s own pragma reference) tells a blocked writer to wait up to 30 seconds for the lock instead of failing immediately, so two workers racing for the row simply queue up rather than error out. Whichever one runs second re-evaluates the inner SELECT fresh, after the first worker’s claim is already committed and visible, so it picks a different row instead.

Racing 4 worker threads against 12 pending rows confirms this holds under real concurrency, not just in theory:

12 orders placed -> 12 pending outbox rows. Racing 4 workers against them:
  worker worker-1 processed 4 rows: [1, 5, 7, 11]
  worker worker-0 processed 4 rows: [2, 6, 10, 12]
  worker worker-2 processed 2 rows: [4, 8]
  worker worker-3 processed 2 rows: [3, 9]

total rows claimed across all workers: 12
unique rows claimed: 12
no row was ever claimed by two workers, and all 12 were processed exactly once

The uneven split (4, 4, 2, 2) is real and expected: threads do not get scheduled perfectly evenly, and whichever worker happens to run next grabs the next available row. What matters is the total: 12 rows claimed, 12 unique row IDs, zero double-claims.

Gotcha: a claimed row can get stuck forever

There is a second failure mode this claim mechanism introduces on its own: what if a worker claims a row (moves it to 'processing') and then crashes before finishing? That row is not 'pending' anymore, so no other worker will ever pick it up. It is silently stuck.

The fix is a periodic sweep that resets claims older than some lease window back to 'pending':

UPDATE outbox SET status = 'pending', claimed_at = NULL, claimed_by = NULL
WHERE status = 'processing' AND claimed_at < datetime('now', '-5 minutes')

Simulating a claim from 10 minutes ago and running that sweep:

simulated a worker that claimed row 1 ten minutes ago, then crashed before finishing
row 1 right now: status='processing', claimed_by='worker-that-crashed' (stuck, will NEVER be retried as-is)
lease-expiry sweep (claimed_at older than 5 minutes) reset 1 row(s) back to pending
row 1 status after the sweep: 'pending' -- a future poll cycle will pick it up again

Note that combining the claim pattern with an idempotent consumer (Step 5) is what makes this safe: if the “crashed” worker actually delivered the event right before dying, the lease sweep will cause it to be redelivered, and the idempotent consumer will correctly ignore the duplicate instead of double-processing it.

Step 7: Preserving event order for the same aggregate

AWS’s guidance calls out one more consideration worth taking seriously: “send messages or events in the same order in which the service updates the database… If the order is incorrect, it might compromise the quality of the data.” A worker in this tutorial always claims the lowest-numbered pending row first (ORDER BY id LIMIT 1), so for a single aggregate like one order, its events are always claimed in the order they were created. But claiming first is not the same as finishing first, once two events for the same aggregate land on two different workers with independent delivery latency.

To test this honestly rather than just asserting it, the demo creates two events for one order (order.created, then order.shipped), gives each simulated delivery a random 10 to 80 millisecond delay, and races 3 workers against them across 10 trials:

Two events for the same order (order.created, then order.shipped) with 3 workers
racing on the whole table, no per-aggregate partitioning, random 10-80ms delivery jitter:
  trial 1: finished as ['order.created', 'order.shipped']  (in order: True)
  trial 2: finished as ['order.created', 'order.shipped']  (in order: True)
  trial 3: finished as ['order.created', 'order.shipped']  (in order: True)
  trial 4: finished as ['order.shipped', 'order.created']  (in order: False)
  trial 5: finished as ['order.created', 'order.shipped']  (in order: True)
  trial 6: finished as ['order.shipped', 'order.created']  (in order: False)
  trial 7: finished as ['order.shipped', 'order.created']  (in order: False)
  trial 8: finished as ['order.created', 'order.shipped']  (in order: True)
  trial 9: finished as ['order.shipped', 'order.created']  (in order: False)
  trial 10: finished as ['order.created', 'order.shipped']  (in order: True)

4 of 10 trials finished OUT of order.

4 out of 10 real trials delivered “shipped” to the downstream service before “created,” despite both events being claimed in the correct order. If your consumer cares about event order for a given aggregate (and most do; an out-of-order “shipped” notification before “created” is confusing at best), the fix is to route every event for the same aggregate_id to the same worker, commonly done by hashing the aggregate ID to pick a worker, rather than handing out whichever row is next to whichever worker happens to be free.

Step 8: Housekeeping: archiving processed rows

Left alone, the outbox table grows forever, since every event that has ever been sent stays in it. Once you are confident nothing needs to re-read old delivered events, clean them up on a schedule, being careful to only ever touch rows that are actually 'sent':

DELETE FROM outbox WHERE status = 'sent' AND processed_at < datetime('now', '-7 days');

outbox rows before cleanup: 6 (5 old+sent, 1 pending)
deleted 5 rows older than 7 days that were already sent
outbox rows after cleanup: 1 (the still-pending row was correctly left alone)

Common mistakes to watch for

• Writing to the database and calling the downstream system in two separate operations. This is Step 1’s bug, and it is the default way almost everyone writes this code the first time. If your code has a network call, a message broker publish, or an email send sitting between two unrelated database writes with no durable record of “this still needs to happen,” you have this bug.
• Assuming outbox delivery is exactly-once. It is at-least-once by design. If your consumer is not idempotent, a routine crash-and-retry in the relay silently becomes duplicate emails, duplicate charges, or duplicate anything else the consumer does.
• Running multiple relay workers without a claim mechanism. Without the atomic UPDATE ... WHERE status = 'pending' pattern from Step 6, two workers will occasionally grab and deliver the same row.
• A claim mechanism with no lease expiry. A worker that claims a row and then crashes will leave that row stuck in 'processing' forever unless something resets stale claims back to 'pending'.
• Assuming per-aggregate order is automatic just because rows are claimed in ID order. Claim order and delivery-finish order are not the same thing once you have more than one worker, as Step 7 demonstrates with real, reproducible out-of-order deliveries.
• Never archiving processed rows. An outbox table with years of 'sent' rows in it slows down every query that has to scan past them to find the handful of 'pending' ones.

Verifying it all works end-to-end

All of the behavior above is captured in a small pytest suite that exercises the atomic write, the relay, the idempotent consumer, and concurrent claiming against the live notification service:

collected 5 items

test_outbox.py::test_failed_write_leaves_no_partial_state PASSED         [ 20%]
test_outbox.py::test_successful_write_creates_both_rows PASSED           [ 40%]
test_outbox.py::test_relay_delivers_and_marks_sent PASSED                [ 60%]
test_outbox.py::test_idempotent_consumer_dedupes_redelivery PASSED       [ 80%]
test_outbox.py::test_concurrent_workers_never_claim_the_same_row PASSED  [100%]

============================== 5 passed in 0.65s ==============================

To confirm the pattern is working in your own setup, walk through the same checks by hand: place an order and confirm the order row and outbox row appear together (Step 3); run the relay and confirm the notification service actually received it (Step 4); force a redelivery and confirm your consumer does not double-process it (Step 5); and, if you run more than one relay worker, confirm two workers never report claiming the same row ID.

Next steps

The relay in this tutorial polls the outbox table on a timer, which the pattern calls the “polling publisher” approach. It is simple and requires nothing beyond your existing database, but it adds polling latency and database load. The alternative, which both microservices.io and AWS document, is “transaction log tailing” (also called change data capture, or CDC): instead of a separate process polling the outbox table with SELECT, a tool reads the database’s own write-ahead log or replication stream directly, reacting to new outbox rows as they are committed rather than waiting for the next poll interval. Debezium is the most common open-source implementation of this approach for relational databases, and AWS’s guidance covers a DynamoDB Streams equivalent for that database, where the events processing service polls the stream itself instead of the outbox table.

From here, a few natural extensions worth exploring on your own:

• Swap the direct HTTP call in the relay for a real message broker client (SQS, RabbitMQ, Kafka) so the outbox feeds a proper queue instead of calling one downstream service directly.
• Add retry with backoff inside the relay itself for delivery failures, rather than only relying on the next poll cycle; see How to Retry Failed API Calls in Python With Exponential Backoff and Jitter for a from-scratch implementation of exactly that.
• If your downstream delivery is itself an HTTP webhook, pair the outbox with signed payloads so the receiving side can verify authenticity; see How to Verify Webhook Signatures With HMAC and Stop Replay Attacks in Python.
• Wrap the relay’s downstream call in a circuit breaker so a persistently down consumer does not leave every relay worker stuck retrying the same failing call; see How to Build a Circuit Breaker in Python to Stop Cascading Failures.