You Can't Commit to Two Systems at Once

A customer pays £80. The payment row commits, the API returns 201, the customer sees a green tick. The payment.captured event that tells the ledger to book it and the warehouse to ship it never gets published. Nothing errors. Nobody notices until reconciliation finds a payment with no ledger entry, or the customer emails asking where their order is.

Start with the version that looks fine

Here's a capture endpoint. It records the payment, then tells the rest of the system about it.

@itsnas — TypeScript

Hide line numbersCopy code

// POST /payments
async function capturePayment(req: Request) {
  const [payment] = await db`
    INSERT INTO payments (order_id, amount, status)
    VALUES (${req.body.orderId}, ${req.body.amount}, 'captured')
    RETURNING id, order_id, amount
  `
 
  await queue.publish('payment.captured', {
    paymentId: payment.id,
    orderId: payment.order_id,
    amount: payment.amount,
  })
 
  return json(201, payment)
}

Show all 16 lines

main

Nas (@itsnas)

Every test passes, because in a test nothing happens between those two awaits. In production, plenty does. A deploy kills the pod. The process runs out of memory. The broker is mid-failover and the publish times out. Any of those after the INSERT and before the publish lands, and the result is the same:

API

Postgres

Broker

Ledger

INSERT payment

committed

publish: never sent, pod killed

API

Postgres

Broker

Ledger

1. API to Postgres: INSERT payment
2. Postgres to API: committed
3. API to Broker: publish: never sent, pod killed

This is the dual write problem. #postgres has a transaction; the broker has its own idea of durability; nothing spans both. Each write can succeed or fail on its own, so sooner or later one does and the other doesn't.

Flip the order and it lies the other way

The instinct is to publish first, so the event is at least out there:

Now the INSERT is the one that can fail after the other side has already happened: a constraint violation, a dropped connection, a deadlock that Postgres resolves by killing this transaction. The ledger books a payment that doesn't exist and the warehouse ships an order nobody paid for. A missing event was a reconciliation problem. A phantom event is a refund problem.

The fixes that don't close the gap

Each of these gets suggested, and each narrows the window without closing it.

Publish inside the transaction, before COMMIT. The publish still happens before the commit, so a failed commit still leaves a phantom event. And the transaction now stays open for as long as the broker takes to answer, holding every lock it took.

Retry the publish. Retries with backoff handle a broker blip. They don't handle a crash: the retry loop lives in the memory of the process that just died, and the fact that a publish was owed died with it.

Catch the publish error and roll back. The INSERT already committed. There's nothing to roll back; the only option is a compensating write, which is a second write to a system that just failed, with the same problem.

A distributed transaction. Two-phase commit across the database and the broker would work in theory, but most brokers don't take part in one, and the ones that have "transactions" only make writes atomic within the broker itself.

The shape of all four is the same: two systems, and a hope that both say yes. You can't make two systems commit atomically. You can make one system commit two things atomically.

Write the event where the data is

Give the database a table for events that are owed:

@itsnas — SQL

Hide line numbersCopy code

CREATE TABLE outbox (
  id           uuid PRIMARY KEY,
  topic        text NOT NULL,
  payload      jsonb NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);
 
CREATE INDEX outbox_unpublished ON outbox (created_at)
  WHERE published_at IS NULL;

main

Nas (@itsnas)

Then write the event into it in the same transaction as the payment:

@itsnas — TypeScript

Hide line numbersCopy code

async function capturePayment(req: Request) {
  return tx(async db => {
    const [payment] = await db`
      INSERT INTO payments (order_id, amount, status)
      VALUES (${req.body.orderId}, ${req.body.amount}, 'captured')
      RETURNING id, order_id, amount
    `
 
    await db`
      INSERT INTO outbox (id, topic, payload)
      VALUES (${randomUUID()}, 'payment.captured', ${{
        paymentId: payment.id,
        orderId: payment.order_id,
        amount: payment.amount,
      }})
    `
 
    return json(201, payment)
  })
}

Show all 20 lines

main

Nas (@itsnas)

The handler doesn't talk to the broker at all any more. Kill the pod anywhere in that function and either both rows exist or neither does. If the payment is there, the obligation to announce it is there too, written down in the same place, by the same commit.

Publishing becomes someone else's job:

API

payments + outbox

Relay

Broker

Ledger

1. API to payments + outbox: one transaction
2. Relay to payments + outbox: claim unpublished
3. Relay to Broker: publish
4. Broker to Ledger: deliver

The relay

The relay is a loop: claim a batch of unpublished rows, publish them, mark them sent.

@itsnas — TypeScript

Hide line numbersCopy code

async function relayBatch() {
  return tx(async db => {
    const rows = await db`
      SELECT id, topic, payload FROM outbox
      WHERE published_at IS NULL
      ORDER BY created_at
      LIMIT 100
      FOR UPDATE SKIP LOCKED
    `
 
    for (const row of rows) {
      await queue.publish(row.topic, { id: row.id, ...row.payload })
    }
 
    await db`
      UPDATE outbox SET published_at = now()
      WHERE id = ANY(${rows.map(row => row.id)})
    `
    return rows.length
  })
}

Show all 21 lines

main

Nas (@itsnas)

Run it on a short interval, and go straight back for another batch whenever it comes back full.

FOR UPDATE SKIP LOCKED is what lets you run more than one relay. FOR UPDATE locks the claimed rows; SKIP LOCKED makes a second relay step over them instead of waiting, so it claims the next hundred. Two relays never hold the same row at once.

That transaction does wrap a network call, which the last post warned against. It's fine here because of what the lock covers: only outbox rows, which only relays touch, and relays skip each other rather than queue. The API's INSERTs are new rows and never wait on it. Keep the batch bounded and give the publish a timeout, so a slow broker can't hold a batch forever.

Now trace the crash again, this time in the relay. It publishes three events, then dies before the UPDATE. The transaction rolls back, the locks release, and the three rows are still unpublished. The next relay picks them up and publishes them again.

Nothing was lost. Three things were sent twice.

At least once means consumers dedupe

That's the trade the outbox makes: "maybe never" becomes "maybe twice". You can't do better than that without the broker and the database sharing a transaction, which is the thing you don't have. So the consumer has to make the second delivery harmless.

It's the same idea as an idempotency key, with the outbox row's id as the key. The relay stamps it on every message it sends; the consumer records every id it has handled:

@itsnas — TypeScript

Hide line numbersCopy code

async function onPaymentCaptured(event: PaymentCaptured) {
  await tx(async db => {
    const [fresh] = await db`
      INSERT INTO processed_events (event_id) VALUES (${event.id})
      ON CONFLICT DO NOTHING
      RETURNING event_id
    `
    if (!fresh) return
 
    await db`
      INSERT INTO ledger_entries (payment_id, amount)
      VALUES (${event.paymentId}, ${event.amount})
    `
  })
}

main

Nas (@itsnas)

The dedupe row and the ledger entry go in one transaction for the same reason the payment and the outbox row do. Record the id in one step and do the work in another, and a crash between them is the dual write problem again, moved to the consumer.

Polling or change data capture

The relay above polls. The alternative is change data capture: a tool like Debezium reads Postgres's write-ahead log through logical replication and publishes each new outbox row as it commits. No polling queries, and latency drops from the poll interval to near zero. It doesn't change the delivery guarantee, though: after a restart the reader resumes from its last saved position and re-sends whatever came after it, so consumers still dedupe.

The cost is a new piece of infrastructure to run, and a replication slot that makes Postgres keep WAL until the reader catches up. If the reader stalls, that WAL piles up on the database's disk. Polling is a query you already know how to run and monitor. Start there and switch when the poll load or the latency actually hurts.

Where it hurts

1. 1

   Assuming events arrive in order

   Several relays with SKIP LOCKED publish batches in parallel, and a transaction that started first can commit last. If a consumer cares that payment.refunded follows payment.captured for the same payment, a broker partition key alone won't save it: the relays race before the broker ever sees the events. Either give each payment's events one relay (split the outbox by a hash of the payment id) and publish with the payment id as the partition key, or put a per-payment sequence number in the payload and have the consumer hold an event back until the one before it has landed.

2. 2

   Not watching the lag

   A dead relay fails silently: the API keeps returning 201 and the outbox just grows. Alert on the age of the oldest unpublished row. It's the one number that tells you whether events are flowing.

3. 3

   Letting the table grow forever

   The partial index keeps the relay's query fast, but published rows still take space. Delete them after a retention window, or partition the table by day and drop old partitions.

4. 4

   One bad row blocking the batch

   A payload the broker always rejects fails the whole transaction, and the next poll claims the same row first again. Add an attempts column, bump it on failure in a separate write, and move a row aside after a few tries so the rest keep flowing.

5. 5

   Publishing an id instead of the facts

   If the payload is only paymentId, every consumer reads the payment back and gets its state now, not its state when the event happened. Put what the consumer needs in the payload: it's a snapshot, written in the same transaction, so it's already consistent.

Back to the £80. With the outbox, the payment and the promise to announce it commit as one. The pod can die, the broker can fail over, the relay can crash mid-batch, and the ledger still books the payment, possibly a few seconds late, possibly delivered twice and dropped the second time. The handler stopped asking two systems to agree and wrote everything to the one that could guarantee it.

---