Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

Transactional Outbox

Write the business change and the message about it in one local transaction, then let a relay publish the message: polling the table or reading the database's log, at-least-once delivery, and why consumers must deduplicate.

Cancelled, and charged anyway

A customer cancels a subscription, sees the confirmation page, and is charged again the next month. The subscription service's database agrees with the customer: the row says cancelled. The billing service never found out. Nothing in either service logged an error.

The cancel endpoint did its work in two steps. It ran UPDATE subscriptions SET status = 'cancelled' WHERE id = :id and committed, and then it published SubscriptionCancelled to the message broker, the message billing listens for to stop future charges. That afternoon a deploy restarted the service's instances, and one of them was stopped after the commit and before the publish. The change was saved; the message about it was never sent.

Swapping the two steps does not help. Publish first and commit second, and a commit that fails on a deadlock, a constraint or a crash leaves billing acting on a cancellation that never happened: a customer who is still subscribed stops being charged. Either order loses in one direction. This is the dual write problem: one operation has to change two systems, the database and the broker, and no transaction covers both. Most brokers cannot join a transaction with your database (a broker's own transaction, the subject of Transactional Client, covers only its own messages), and Two-phase commit explains why services usually avoid that kind of distributed commit even where it is available.

Both rows, one commit

The Transactional Outbox stops writing to two systems at once. The endpoint writes everything to the one system it can change atomically, its own database: the subscription row, and a row in an outbox table that describes the message to send. Inside a single transaction it runs the same UPDATE, then INSERT INTO outbox (id, topic, payload) with a fresh message id, the topic SubscriptionCancelled and the subscription id as payload, and then commits.

Now the two writes commit together or not at all. A crash before the commit loses both, and the customer sees an error or a timeout and can try again. A crash after it keeps both, and the message waits in the outbox. Nothing has been sent yet, and that is the point: sending has become a separate job that can be repeated until it succeeds.

Write state + message in one transaction, relay later

The endpoint never talks to the broker. Only the relay does, and only for rows that committed.

Who empties the outbox

A relay reads new rows from the outbox, publishes each one to the broker and records that it is done. It can find the new rows in two ways:

  • Polling the table. Every half second or so, the relay selects the oldest rows WHERE published_at IS NULL, publishes them and, once the broker confirms each one, sets published_at. It needs nothing beyond SQL. It costs a polling delay, a steady query load on the primary, and a table that grows until published rows are deleted.
  • Reading the database's log. The database already records every committed change in its own log (the write-ahead log in PostgreSQL, the binlog in MySQL), and a change-data-capture tool such as Debezium can stream new outbox rows from that log as they commit, with no polling queries. It adds a component to run and needs access to the log; Change data capture covers CDC itself.

One polling shortcut fails quietly: remembering the highest id published so far and asking for id > last_id. An id is assigned when the row is inserted, not when its transaction commits, so a transaction that took id 1041 and committed after 1042 was already published is skipped for good. Marking each row as published, or reading the commit log, does not leave that hole.

The message that arrives twice

The relay has its own gap. It publishes the row for the cancellation, the broker stores the message, and the relay crashes before it sets published_at (a log-reading relay has the same gap before it saves its position in the log). The instance that replaces it finds the row still unpublished and publishes it again.

The relay cannot remove this by being careful. Publishing and marking are, once again, writes to two systems, so it can only choose which way to fail, and losing messages is the failure the pattern exists to remove. What the outbox gives is at-least-once delivery: as long as the relay keeps retrying and the broker eventually accepts each message, every committed message gets published, possibly more than once, and no message is published for a change that rolled back.

So duplicates must be harmless at the other end. The outbox row's id travels with the message, and billing records the ids it has handled in the same transaction as their effect, skipping any it has already seen; a call outside that database, such as a refund through the payment provider, needs that provider's own idempotency key. Idempotent Consumer shows how; it is the messaging form of the Idempotency Key a client sends with a retried request. Stopping charges twice is harmless by luck; a refund issued twice is not.

Order, backlog and the shortcut that returns

Order needs care. If the customer cancels and then reactivates a second later, billing must see the two messages in that order. Reading the log keeps commit order; a polling relay that sorts by creation time only approximates it, for the same reason ids did; and several relays publishing in parallel lose it unless the messages for one subscription always go through the same one, keyed by the subscription id. Even then, billing gets the order that relay read only if it publishes them one at a time to a broker that keeps per-key order.

The outbox is also a queue inside your database: delete published rows on a schedule and alert on a growing backlog, because a stalled relay raises no error. And the shortcut that keeps coming back is the opening one, an event published directly from application code after the commit instead of through an outbox row, which reopens the dual-write race for that event. A service that stores its state as events commits a single write, the event, and other services read that same log, as Event Sourcing describes.

Crash it anywhere

The Transactional Outbox & Inbox challenge asks for both ends of this: the subscription change and its outbox row in one local transaction, a relay publishing to the Message Broker, and on the receiving side an inbox written in the same local transaction as the effect, so a redelivered message is skipped. A good way to check the result is to pick any arrow and imagine the process dying exactly there. The design holds if, at every such point, the state change never commits without its outbox row or the row without the change, and the receiving side still acts only once.

Practice this