DEV Community

mohamed Tayel
mohamed Tayel

Posted on

Day 14 — Transactional Outbox: Saving the Payment and the Promise Atomically

#dotnet #distributedsystems #sqlite #tutorial

Day 13 made the follow-up work survive a restart — but it saved the payment and the promise as one JSON file, deliberately labelled outbox-shaped. Day 14 moves to a real SQLite database and asks the sharper question: can the payment be saved while its work item goes missing? The answer is a single database transaction.

Transactional outbox — one atomic commit

🙏 Credit where it's due — This series is my attempt to internalize and share what I learned from Mahmoud Youssef's excellent course, Fundamentals of Distributed Systems on Udemy. The course material, structure, and topic flow are his work; the explanations, code examples, and diagrams in these articles are my own rewrite in my own words. If you find this useful, please consider taking the course — it goes much deeper than these articles can.

🧭 Where we are — after Days 11, 12 and 13

  • Day 11 — Honest Fallback: on a payment timeout, return PendingConfirmation honestly. We admitted the result was unknown.
  • Day 12 — Reconciliation: a worker calls CheckPaymentStatus(originalKey) and resolves each pending payment. We learned how to check the original attempt.
  • Day 13 — Durable Reconciliation: the pending work is persisted so it survives a restart — as one JSON snapshot, outbox-shaped, not transactional. We made the follow-up work survive a restart.
  • Day 14 (this document): the payment record and its reconciliation obligation must be committed together. We commit the business state and the follow-up work in one database transaction.

🎯 The one idea to take away: Durability prevents forgetting after restart. Atomicity prevents forgetting during the write. Save the business state + save the follow-up work = one database transaction. Either both commit, or neither does.

1. Why Day 13's JSON store was outbox-shaped, not transactional

Day 13 solved a real problem — in-memory work vanishes on restart, so we wrote it to disk with a safe atomic file replace. That answered one question well:

✅ Day 13 solved — durability: Will the saved work survive a restart? Yes — a fresh instance reloads the pending work from disk.

❌ Day 14 solves — atomicity: Can the payment be saved while the work item is missing? In a database with two separate writes — yes, and that is the bug.

Day 13's snapshot always wrote the payment and the work item in the same file, so they never diverged — but that convenience hid the real production hazard. In a database-backed system the natural implementation is two writes: save the payment, then save the outbox row. That is the dual write, and it has a crash window.

💡 Outbox-shaped ≠ transactional. A single JSON document with an atomic replace is not a database transaction spanning a business row and an outbox row. Day 14 builds the real thing on SQLite so the transaction boundary is visible.

2. The dual-write problem — a crash window between two durable writes

The dangerous pattern is two independent, separately-committed writes:

INSERT Payment   → COMMIT      // durable
   ... crash ...               // the window
INSERT Outbox    → COMMIT      // never happens
Enter fullscreen mode Exit fullscreen mode

If the process dies in the window, the database is left durably inconsistent:

Payment exists:   PAY-002 = PendingConfirmation
Outbox row:       missing
Enter fullscreen mode Exit fullscreen mode

The payment is perfectly durable — and completely forgotten. No worker has a row telling it this payment needs reconciling, so it stays pending forever even after the provider recovers holding the real answer. The mirror-image inconsistency (outbox row written first, payment missing) is equally possible if you flip the order.

💡 Durability did not save us. Both rows were individually durable. The missing guarantee is that they become durable together — all or nothing.

3. The SQLite schema

Both scenarios use the same small, explicit schema in a real local SQLite file (Microsoft.Data.Sqlite, direct SQL — no EF Core, no server):

CREATE TABLE Payments (
    IdempotencyKey       TEXT PRIMARY KEY,       -- the business identity
    PaymentStatus        TEXT NOT NULL,
    PaymentConfirmed     INTEGER NOT NULL,
    RequiresStatusCheck  INTEGER NOT NULL,
    IsFallback           INTEGER NOT NULL,
    TransactionId        TEXT NULL,
    CreatedAtUtc         TEXT NOT NULL,
    UpdatedAtUtc         TEXT NOT NULL
);

CREATE TABLE OutboxMessages (
    Id                   TEXT PRIMARY KEY,        -- deterministic per payment
    MessageType          TEXT NOT NULL,
    AggregateId          TEXT NOT NULL,           -- FK → Payments(IdempotencyKey)
    Payload              TEXT NOT NULL,
    Status               TEXT NOT NULL,           -- Pending | Processed
    AttemptCount         INTEGER NOT NULL,
    CreatedAtUtc         TEXT NOT NULL,
    ProcessedAtUtc       TEXT NULL,
    FOREIGN KEY (AggregateId) REFERENCES Payments(IdempotencyKey)
);

CREATE INDEX IX_Outbox_Pending ON OutboxMessages(Status) WHERE Status = 'Pending';
Enter fullscreen mode Exit fullscreen mode

The Payments.IdempotencyKey primary key is the DB-level guard against a duplicate payment; each outbox row uses a deterministic id such as OUTBOX-PAYMENT-RECONCILIATION-PAY-001, so a retried write can never create a duplicate work item either.

4. The BEFORE scenario — the dual-write gap, proven

session-14-dual-write-gap replays Days 11–13 (6 payments, hung gateway, 300 ms timeout, SemaphoreSlim(2) → all PendingConfirmation), then persists each with the dual write. A controlled failure fires between the two writes for PAY-002, PAY-004, PAY-006:

  • PAY-001/003/005: payment=COMMIT then outbox=COMMIT — both rows land.
  • PAY-002/004/006: payment=COMMIT → injected crash → outbox=MISSING. The payment is durable; its obligation never gets written.
  • Restart: a fresh Instance 2 opens the same DB and reads only the 3 pending outbox rows it can find. It reconciles those 3; the other 3 stay pending forever.

Real run: payments persisted 6, outbox persisted 3, payments missing outbox 3, orphan outbox 0. The 3 reconciled split into 1 Paid (PAY-001) + 2 Failed (PAY-003, PAY-005), leaving 3 still pending (PAY-002/004/006 — which the provider actually charged).

💡 Verdict from the real report: "THE DATA WAS DURABLE — BUT THE FOLLOW-UP WAS NOT ATOMIC ❌". Three customers were charged, and nothing in our system will ever ask about them.

5. The AFTER fix — one transaction, commit or rollback

session-14-transactional-outbox writes both rows on one connection inside one SqliteTransaction:

BEGIN TRANSACTION
  INSERT Payment       ON CONFLICT(IdempotencyKey) DO NOTHING
  INSERT OutboxMessage ON CONFLICT(Id)             DO NOTHING
COMMIT           -- both rows land together
      // or, on failure before COMMIT:
ROLLBACK         -- neither row remains
Enter fullscreen mode Exit fullscreen mode

The same failure is injected inside the transaction, before COMMIT. From the real write timeline:

PAY-001 | BEGIN → payment+outbox → COMMIT
PAY-002 | BEGIN → payment → ROLLBACK   ← injected failure
PAY-002 | BEGIN → payment+outbox → COMMIT (retry #2)
PAY-003 | BEGIN → payment+outbox → COMMIT
PAY-004 | BEGIN → payment → ROLLBACK   ← injected failure
PAY-004 | BEGIN → payment+outbox → COMMIT (retry #2)
PAY-005 | BEGIN → payment+outbox → COMMIT
PAY-006 | BEGIN → payment → ROLLBACK   ← injected failure
PAY-006 | BEGIN → payment+outbox → COMMIT (retry #2)
Enter fullscreen mode Exit fullscreen mode

3 rollbacks, 6 commits. After every rollback the operation is retried with the same idempotency key and commits both rows cleanly. The rollback is not hidden — it is in the report, on purpose.

💡 Commit-or-rollback is the whole point. A rolled-back transaction leaves nothing — no half-written payment, no orphan outbox row. There is never a moment where one row exists without the other.

6. Consistency rules and the role of the idempotency key

Retrying after a rollback must not create duplicates. The safety comes from the database, not an in-memory check:

  • Same IdempotencyKey = same payment operation. It is the primary key, so a second committed payment for the same key is impossible.
  • Deterministic outbox id (OUTBOX-PAYMENT-RECONCILIATION-<key>) is the outbox primary key, so a duplicate work row is impossible.
  • Both inserts use ON CONFLICT DO NOTHING, so a retry is a safe no-op if a row already exists.

The two durable invariants the AFTER report verifies with SQL, not assertions:

SELECT COUNT(*) FROM Payments p
 WHERE NOT EXISTS (SELECT 1 FROM OutboxMessages o WHERE o.AggregateId = p.IdempotencyKey);  -- = 0

SELECT COUNT(*) FROM OutboxMessages o
 WHERE NOT EXISTS (SELECT 1 FROM Payments p WHERE p.IdempotencyKey = o.AggregateId);        -- = 0
Enter fullscreen mode Exit fullscreen mode

💡 Never a payment without outbox; never an outbox without payment. Real run: payments missing outbox = 0, outbox missing payments = 0, duplicate payment rows = 0, duplicate outbox rows = 0.

7. Restart and outbox processing — check, never charge

After the writes, a simulated crash discards Instance 1; a fresh Instance 2 opens the same SQLite file and processes the pending outbox:

SELECT Id, AggregateId FROM OutboxMessages
 WHERE Status = 'Pending' ORDER BY CreatedAtUtc;
Enter fullscreen mode Exit fullscreen mode

For each pending message, inside its own transaction, the worker calls CheckPaymentStatus(originalKey) — a status read, never CreatePayment — applies the provider's real result, and marks the message Processed. Finalizing the payment and completing the message commit together, so a payment can never be finalized while its message stays pending:

PAY-001 | PendingConfirmation → Paid   | outbox Pending → Processed | TX-PAY-001
PAY-002 | PendingConfirmation → Paid   | outbox Pending → Processed | TX-PAY-002
PAY-003 | PendingConfirmation → Failed | outbox Pending → Processed | TransactionId=null
PAY-004 | PendingConfirmation → Paid   | outbox Pending → Processed | TX-PAY-004
PAY-005 | PendingConfirmation → Failed | outbox Pending → Processed | TransactionId=null
PAY-006 | PendingConfirmation → Paid   | outbox Pending → Processed | TX-PAY-006
Enter fullscreen mode Exit fullscreen mode

Our application restarted. The SQLite database survived. The external payment provider also survived. The provider is a separate simulated system that keeps its original outcomes across our restart; the SQLite file is our durable state; no static field carries business data between the two instances.

💡 Creation vs check — still separate ledgers. 6 gateway creation attempts (Phase 1, one per key), 0 creation calls during the worker, 6 status checks after restart. Reconciliation reads the original attempt; it never starts a new one.

8. The real implementation — proven by running it

Day 14 is three files under the session-test project: a shared OutboxSqliteCore (schema, provider, consistency queries, the shared Phase 1) and two ISessionScenario classes registered in Program.cs:

  • BEFORE: Session14DualWriteGapScenario → command session-14-dual-write-gap
  • AFTER: Session14TransactionalOutboxScenario → command session-14-transactional-outbox

The only new dependency is Microsoft.Data.Sqlite. Everything else is standard .NET — Task.Delay for the hung gateway and the status read, SemaphoreSlim(2) for the bulkhead, a linked CancellationTokenSource for the 300 ms timeout, and direct SQL over a real SQLite file created in a per-scenario temp folder and deleted afterwards.

Real BEFORE vs AFTER metrics

Metric (real run) BEFORE (dual write) AFTER (one transaction)
Write-transaction commits 9 (6 payment + 3 outbox, separate) 6 (payment + outbox together)
Write-transaction rollbacks 0 (nothing to roll back) 3 (then retried)
Committed payment rows 6 6
Committed outbox rows 3 6
Payments missing outbox rows 3 0
Outbox rows missing payments 0 0
Pending outbox loaded on restart 3 6
Status checks after restart 3 6 (one per key)
New payment attempts during worker 0 0
Resolved as Paid 1 4
Resolved as Failed 2 2
Still pending 3 0
Processed outbox rows 3 6
Duplicate payment / outbox rows 0 / 0 0 / 0
Fake successes 0 0

Transaction commit & rollback evidence (AFTER)

The real write timeline shows all three rollbacks and their retries: PAY-002, PAY-004 and PAY-006 each go BEGIN → payment → ROLLBACK, then BEGIN → payment+outbox → COMMIT (retry #2). Final: 6 write commits, 3 rollbacks, 6 processing commits.

Safety checks from the real AFTER report — all passing

  • no duplicate payment rows → max 1 row per key
  • no duplicate outbox rows → max 1 row per key
  • one gateway creation attempt per key → 6 / 6
  • zero fake successes → 0
  • tx ids only from provider, Paid only → 4 Paid w/ id, 0 Failed w/ id

💡 Verdicts from the real reports. BEFORE: "THE DATA WAS DURABLE — BUT THE FOLLOW-UP WAS NOT ATOMIC ❌". AFTER: "TRANSACTIONAL OUTBOX — THE PAYMENT AND THE PROMISE COMMITTED TOGETHER ✅". Both exit with code 0 only when every check passes.

Run it yourself 

dotnet run --project tools/Wassal.SessionTests -- session-14-dual-write-gap
dotnet run --project tools/Wassal.SessionTests -- session-14-transactional-outbox
Enter fullscreen mode Exit fullscreen mode

Each run prints the schema, the write timeline, the consistency query results, the per-payment table, the summary, the checks, and the verdict, then saves a timestamped report under:

Reports/Session-14/Session-14-Dual-Write-Gap-RunReport-<timestamp>.txt
Reports/Session-14/Session-14-Transactional-Outbox-RunReport-<timestamp>.txt
Enter fullscreen mode Exit fullscreen mode

The SQLite file is created in a per-scenario temp folder — e.g. %LOCALAPPDATA%\Temp\wassal-day14-outbox-<random>\wassal-outbox.db — and cleaned up at the end; its path is printed in the report.

9. Final takeaway

  • Two separate durable writes have a crash window that can leave a payment without its follow-up obligation — durability alone does not close it.
  • Writing the payment row and the outbox row in one database transaction makes them commit or roll back together.
  • A rollback leaves nothing; a retry with the same idempotency key is safe because DB primary keys enforce one payment and one outbox row per key.
  • On restart, the worker reads pending outbox rows and checks the original attempt — it never re-charges — finalizing payment and message in one transaction.
  • This is now a real local transactional outbox demonstration using SQLite — not outbox-shaped. Retries with backoff, leasing, dead-letter handling, and a hosted worker are later lessons.

The four days, side by side:

Day 11:  We admitted that the result was unknown.
Day 12:  We learned how to check the original attempt.
Day 13:  We made the follow-up work survive a restart.
Day 14:  We commit the payment state and the follow-up obligation together.
Enter fullscreen mode Exit fullscreen mode

💡 Day 13 solved: will the saved work survive restart? Day 14 solves: can the payment be saved while the work item is missing? Durability prevents forgetting after restart; atomicity prevents forgetting during the write.

Six payments, three injected failures, three rollbacks, six clean commits — then a restart that reloaded all six outbox rows and resolved them: 4 Paid, 2 Failed, 0 pending, 0 duplicates, 0 fake successes. Save the business state and the follow-up work in one database transaction: both, or neither.

Part of the **Fundamentals of Distributed Systems* series — building Wassal, a distributed food-delivery lab, one concept at a time.*

Top comments (0)