Never call the outside world before you commit

Sooner or later, most backends need to do two things at once. They change their own database, and they tell some other system about it. Charge a card and record the order. Delete a record and notify a partner. Send an email and mark it sent.

These look like one operation, but they're really two writes to two systems. The two systems don't share a transaction. That gap is where data quietly stops agreeing.

People call this the dual-write problem. You can't make it go away; you can only manage it. So the useful questions are: which order do you do things in, what can fail in each order, and which failures can you recover from? I'll walk through those, then show two real fixes where the order was the whole bug.

Two writes, no shared transaction

A database transaction is atomic: either every change commits, or none does. But that promise stops at the edge of the database. A call to a payment provider isn't part of your transaction. Neither is a message to a partner's API or an email sent through a mail service. You can't roll any of them back.

So anything that touches both has to pick an order. Each order fails in its own way. Here's a simple example: you cancel an order and tell the warehouse to stop shipping it.

The first row is the dangerous one. Once the outside world has acted on your message, rolling back on your side doesn't undo it. Now the two systems disagree, and neither one knows it.

The second row is also a disagreement, but in the safe direction. Your database holds the truth, and the other system is just behind. Anything that's behind can catch up with a retry.

Why calling first feels right

Calling the external system first often feels safer. That's why this bug is so common. The thinking goes: if the partner rejects the request, we haven't changed anything yet, so we just return an error.

But that only covers one failure: the external call failing. It ignores the other one, where the call succeeds and then your own transaction fails. Transactions fail for boring reasons all the time:

• a unique-constraint violation
• a serialization conflict
• a deadlock
• a timeout
• a deploy restarting the process

If any of those happens after a successful external call, you land in the unrecoverable row of the table.

What if the external system needs to be able to say no? There's still a safe way. Make the call a separate step you can reverse: reserve, then commit locally, then confirm. Payment providers offer exactly this split. They call it authorize and capture.

Closing the last gap: the transactional outbox

"Commit, then call" still has one crack. If the process dies between the commit and the call, the call never happens. And nothing remembers that it should have.

A transactional outbox closes that gap. You don't call the external system directly. Instead, you write a row describing the message into an outbox table, in the same transaction as the business change. A separate worker picks up unsent rows, delivers them and marks them sent.

BEGIN;
UPDATE orders SET status = 'cancelled' WHERE id = $1;
INSERT INTO outbox (topic, payload) VALUES ('warehouse.cancel', '{"order_id": "..."}');
COMMIT;

Now the change and the plan to notify are atomic. Both exist, or neither does. The worker keeps retrying until delivery works, so each message arrives at least once. That means the receiver has to handle duplicates. This is why outboxes and idempotent receivers (ones where handling the same message twice is harmless) usually go together.

An outbox isn't always worth it. It adds a table, a worker, monitoring and some delivery delay. Here's a rough rule of thumb:

• If a missed message is a correctness problem, like money moving or data that must be erased elsewhere, use an outbox.
• If a missed message is cosmetic and you already have a retry path, like a pending row that a sweep picks up, "commit then call" plus that path is often enough.
• Either way, never "call then commit".

Retries need idempotent writes

Operations will get retried. Users click again, clients retry, and your own outbox worker retries. So every write on that path has to survive running twice.

A common trap is a constraint that's correct on its own but turns retries into permanent failures. Say you track pending deletion requests, and you allow at most one pending request per record:

CREATE UNIQUE INDEX one_pending_deletion
    ON deletion_log (record_id) WHERE status = 'PENDING';

Now suppose the delete path writes that row with a plain INSERT. The first failed attempt leaves a pending row behind. Every retry then breaks the index, and the transaction rolls back. Forever. The constraint did its job. The write just wasn't built to run twice.

In PostgreSQL, ON CONFLICT can target a partial unique index (one that only covers rows matching a WHERE). You name the column and repeat the index's WHERE clause, so the planner knows which index you mean:

INSERT INTO deletion_log (record_id, initiated_by)
VALUES ($1, $2)
ON CONFLICT (record_id) WHERE status = 'PENDING'
DO UPDATE SET
    initiated_by = COALESCE(EXCLUDED.initiated_by, deletion_log.initiated_by),
    updated_at   = NOW()
RETURNING *;

Now a retry finds the earlier pending row and refreshes it instead of failing.

Keep other people's latency off your read paths

Order isn't only about correctness. It also decides whose slowness your users wait for.

Sometimes a read triggers a write to an external system. For example: "mark this record as viewed and tell the partner." It's tempting to do that push inline. But then the read takes as long as the partner's API. If the partner is slow, your page is slow, and your users have no idea why.

The fix is to detach the external work from the request. In Go, you need to get two things right.

First, the request's context is cancelled as soon as the handler returns. A goroutine that keeps using it will fail with "context canceled" almost right away. context.WithoutCancel (Go 1.21+) gives you a context that keeps the parent's values, like request IDs for logging, but never gets cancelled with the parent.

Second, detached work needs its own deadline, sized for the work it does:

func (s *Service) notifyAsync(ctx context.Context, id uuid.UUID) {
    parent := context.WithoutCancel(ctx) // keep values, drop cancellation
    go func() {
        bg, cancel := context.WithTimeout(parent, 45*time.Second)
        defer cancel()
        s.notifyPartner(bg, id)
    }()
}

Say the background work makes two calls, each with a 15-second timeout. Then its budget has to be more than 30 seconds. If it isn't, the outer timeout can cut it off between the two calls. That's the worst outcome: the first call went through and the second was abandoned.

Where I ran into this

Both halves of this post come from one system at work: a multi-tenant lead-routing platform. Partners send in leads. Each lead goes to a nearby location. Status then syncs back to the partner and to the location's own CRM. So deleting a lead has to reach all three systems.

A delete that told the partner too early

The bug report said: "delete returns 500, the retry returns 404, and the partner shows the deletion was sent anyway." On our development database, it had left 23 pending deletion logs behind. Three of them were on live leads that could no longer be deleted at all.

Both mistakes from this post were in the same function. The partner was told before the transaction even began. And the deletion log was written with a plain INSERT against a partial unique index:

// before: the partner hears first
if lead.PartnerID != nil && lead.SourceID != nil && *lead.SourceID != "" {
    if err := s.DeleteLeadFromPartner(ctx, *lead.PartnerID, *lead.SourceID, id); err != nil {
        // Continue with deletion regardless of external API failure
    } else {
        partnerNotified = true
    }
}
tx, err := s.db.Begin(ctx)
...
deletionLog, err := qtx.CreateLeadDeletionLog(ctx, ...)

So the partner erased its copy. Then our INSERT hit the leftover pending row, and our transaction rolled back. After that, every retry failed the same way.

I fixed both halves. The notification moved after the commit. The log write became an upsert on the partial index:

notifyPartner := func() bool { ... DeleteLeadFromPartner(...) ... }

tx, err := s.db.Begin(ctx)
...
deletionLog, err := qtx.UpsertPendingLeadDeletionLog(ctx, ...)
...  // commit

// Committed. Only now is it safe to tell anyone outside this system
// that the record is gone.
partnerNotified := notifyPartner()

I added the upsert as a new query instead of changing the existing insert. Another loop calls that insert once per secondary CRM, and it needs one row for each. Turning that query into an upsert would have overwritten the primary CRM's row.

There are no timings for this fix, because it was a correctness bug. I wrote integration tests and checked that they failed without the change. One test reads the lead's deleted flag at the moment the outbound call leaves. So it checks the order itself: by the time the partner hears about it, our deletion is already committed.

A read that waited for the partner

When a location user opened a lead, we recorded VIEWED and pushed that status to the partner. The push was two calls: one to authenticate, one to update. Each was capped at 15 seconds. And it all ran inside the request that served the detail view.

I moved it into a detached goroutine with a 45-second budget. That's more than the 2 × 15 seconds the two calls can take. The push only moves status forward, so a late VIEWED can't overwrite a newer status. It's also limited to the exact locations that authorised the read.

Here's what it looked like against a partner that deliberately stalled 6 seconds per call:

A checklist for anything that crosses a system boundary

1. 1.List every external side effect the operation causes: emails, webhooks, calls to partners.
2. 2.For each one, decide whether it needs to be able to say no. If it does, split it into reserve and confirm steps.
3. 3.Otherwise, do it after your commit, never before.
4. 4.Decide what happens if it fails after the commit: an outbox, a pending row that a sweep retries, or a gap you accept.
5. 5.Make every write on the retry path safe to repeat: upserts, ON CONFLICT, or conditional updates.
6. 6.Write a test that checks the order, not just the end state.
7. 7.If a read triggers the side effect, move it off the request path, with a detached context and a timeout sized for the work.

Common mistakes to watch for:

• "Continue regardless of the external API failure" placed before the transaction. That locks in the order you can't recover from.
• Goroutines that reuse the request context and die as soon as the response is sent.
• Background timeouts shorter than the calls they wrap, added together.
• Turning a shared insert into an upsert without checking every caller.

Takeaways

• Your database and an external system can't share a transaction. Pick the order on purpose.
• Commit first, then call. A late call can be retried. A call followed by a rollback can't be undone.
• Use a transactional outbox when a missed message is a correctness problem, and make receivers idempotent.
• Without idempotent writes, retries turn constraint violations into permanent failures. ON CONFLICT can target a partial unique index if you repeat its WHERE.
• Test the order itself, not just the final state.
• Keep third-party slowness off read paths with context.WithoutCancel and a timeout bigger than the work inside it.