Outbox Pattern: State Machines, SKIP LOCKED, and Kafka Keys

Mike

4 min read

·

Aug 19, 2026

--

Listen

Share

In a world of events, how do we guarantee delivery of messages?

One everyday, battle-tested pattern is the outbox.

Why do we need it?

Because a DB transaction does not cover producing the message to the bus.

How to do it? Decompose the insert from the produce.

What does that mean?

First of all we need to store our outbox messages in a separate event table. I won’t write a full

table DDL here, but in my practice the important fields are

- obj_id — ref to the original object (convenient for searching if you want to look things up or have an admin panel/backoffice)
- topic — where the event needs to be sent
- payload — bytes/text, it depends on your taste
- status — here we want to see pending, processing, published, or error

Of course in real life there are a lot of extra fields for metadata: headers, correlation_id, retry_count, error_text, and so on.

Here I see one mistake: do not make obj_id UNIQUE in the outbox. Want only one order? UNIQUE on orders, not on outbox — they share the same transaction anyway. And one object often means many events, so UNIQUE on obj_id is the wrong constraint even when the main row is unique.

Another very important part: do the business work in the same transaction as storing the outbox event:

// good
begin_tx
store_order
store_outbox_event
commit_tx

// bad
store_order
store_outbox_event

In the `bad` part we lose the A — atomicity — from ACID, because the two writes are not one transaction. Otherwise you know — that does not work at all.

Then the main and complicated part: how to read new rows for parallel processing, without a long-running transaction.

So the base code is something like this:

begin_tx
rows = `select * from outbox_events where status = 'pending' order by created_at ASC limit :limit`

for row in rows {
    err = send_to_bus(row)
    if err != nil {
        mark_as_error(row)
    } else {
        mark_as_published(row)
    }
}

commit_tx

See the troubles here?

I count them:

- one long-running transaction with bus-involved code (Kafka, Rabbit, …); in case of a network glitch you need to wait 2–5 seconds to complete all events
- this SELECT has no lock: two workers fetch the same pending rows and both publish — that is duplicate delivery, not parallelism

How to solve it?

I prefer to have state-machine processing in three steps.

Step 1 — claim. One short transaction: lock a batch, mark processing, commit. No bus I/O inside.

begin_tx

rows = update outbox_events SET status = 'processing', updated_at = NOW()
where id in (
    select id from outbox_events
        where status = 'pending'
        order by created_at ASC limit :limit
        FOR UPDATE SKIP LOCKED -- <----------- see here
    )
returning *

commit_tx

`FOR UPDATE SKIP LOCKED` prevents two workers from fetching the same IDs at the same time.

Step 2 — send. Outside any DB transaction.

Step 3 — final status. A separate micro-transaction per row. The DB still has to open a short TX for that UPDATE; it is just not wrapped around the bus send.

// after commit_tx of step 1
for row in rows {
    // step 2 - no DB transaction
    err = send_to_bus(row)  // Kafka key = obj_id

    // step 3 - micro-transaction, status only
    if err != nil {
        db.execute("UPDATE outbox_events SET status = 'error' WHERE id = :id")
    } else {
        db.execute("UPDATE outbox_events SET status = 'published' WHERE id = :id")
 }

One more nuance from “do not make obj_id UNIQUE”: one order can emit CREATED, then a second later PAID. Worker 1 SKIP LOCKED a batch that has CREATED; worker 2 in the same millisecond takes the next batch with PAID for the same order. They send in parallel, and because of the network PAID can land in Kafka before CREATED.

Fix: when producing, use obj_id (order_id) as the Kafka message key. Kafka then puts both messages on the same partition and keeps order inside that partition even if they arrived with a microsecond skew.

If a worker dies, a scheduler resets status back to pending. One catch: pick the interval carefully — if the main sender runs every 1 second, run the scheduler on something like 30 seconds, and only reset rows whose updated_at is old enough that you do not overlap a live worker.

The same for error: increment retry_count and retry after some interval.

Here I highlight the issue I want to solve with this code: the long-running transaction, nothing more.

SKIP LOCKED also stops two workers from grabbing the same row at the same time — that duplicate we do want to kill (network load, event races).

It does not make delivery exactly-once. If the worker dies after send_to_bus and before published, the scheduler retries and the bus sees the message twice. Inbox is still needed. Unique/integrity stays on the main table, not on this read path.

Using the outbox is not a hard pattern, but it is useful in a world of events.