Delivery Semantics

The previous lesson compared the two transport models and left the same question unanswered in both: doing the work and writing the “done” information are two separate steps. On a queue, is the acknowledgement sent before the work or after? On a stream, is the offset recorded before the work or after?

The order of the two steps is not an implementation detail; it is the decision that determines the system’s delivery semantics. This lesson runs the same workload in two orderings and a third arrangement, and counts the loss and repeated-effect numbers.

What the Three Names Mean

At-most-once: every message is processed zero or one times; loss is possible, repetition is not. Writing the acknowledgement before the effect produces this mode — the work is done after the message is already counted as processed, so if something happens in between, the work never gets done at all.

At-least-once: every message is processed one or more times; repetition is possible, loss is not. Writing the acknowledgement after the effect produces this mode — the message is counted as processed after the work is done, so if something happens in between, the message gets redelivered.

Exactly-once is not a third ordering. As long as the acknowledgement and the effect are two separate steps, a gap remains between them, and the process can die in that gap. Exactly-once is not a delivery mode, it is the result of deduplication layered on top of at-least-once. The measurement will show this with two numbers.

The measurement setup builds the outbox table from the The Data Access Layer and Business Logic course, an effects table, and the processed_message guard introduced in the Idempotent Transactions lesson. The effect is sending the overdue notification; every send writes a row to the notification relation.

# setup.sh — outbox, effect table, and processed-message guard for the delivery measurement
rm -f delivery.db
sqlite3 delivery.db <<'SQL'
CREATE TABLE outbox (message_id INTEGER PRIMARY KEY, type TEXT NOT NULL,
                           body TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'pending');
CREATE TABLE notification (notification_id INTEGER PRIMARY KEY, message_id INTEGER NOT NULL,
                       member_id INTEGER NOT NULL);
CREATE TABLE processed_message (message_id INTEGER PRIMARY KEY, processed_at TEXT NOT NULL);
SQL
echo "tables: $(sqlite3 delivery.db "SELECT group_concat(name, ' ') FROM sqlite_master WHERE type='table'")"

tables: outbox notification processed_message

Same Workload, Three Modes

Twelve overdue notifications will be processed. In three of them — messages 3, 7, and 11 — the process will die in the exact middle of the first attempt. Death is represented with an exception; uncommitted work is rolled back, the process restarts, and it retries whatever messages still appear pending.

// modes.mjs — the same workload in three modes: at-most-once, at-least-once, deduplication
import { DatabaseSync } from "node:sqlite";

const CRASHES = new Set([3, 7, 11]);        // these messages have their first attempt cut in half

function reset(db) {
  for (const t of ["outbox", "notification", "processed_message"]) db.exec(`DELETE FROM ${t}`);
  for (let n = 1; n <= 12; n++)
    db.prepare("INSERT INTO outbox (message_id, type, body) VALUES (?,?,?)")
      .run(n, "overdue_notification", JSON.stringify({ memberId: (n % 3) + 1 }));
}

const send = (db, m) => db.prepare("INSERT INTO notification (message_id, member_id) VALUES (?,?)")
  .run(m.message_id, JSON.parse(m.body).memberId);
const acknowledge = (db, m) => db.prepare("UPDATE outbox SET status='processed' WHERE message_id=?")
  .run(m.message_id);

function process(db, m, mode, crash) {
  if (mode === "at-most-once") {
    acknowledge(db, m);                               // acknowledge first
    if (crash) throw new Error("crash");
    send(db, m);                                       // effect after
  } else if (mode === "at-least-once") {
    send(db, m);                                       // effect first
    if (crash) throw new Error("crash");
    acknowledge(db, m);                                 // acknowledge after
  } else {
    db.exec("BEGIN IMMEDIATE");                      // effect and guard in the same transaction
    if (db.prepare("SELECT 1 AS v FROM processed_message WHERE message_id=?").get(m.message_id) === undefined) {
      send(db, m);
      db.prepare("INSERT INTO processed_message (message_id, processed_at) VALUES (?, '2025-07-20')").run(m.message_id);
    }
    db.exec("COMMIT");
    if (crash) throw new Error("crash");
    acknowledge(db, m);
  }
}

function run(db, mode) {
  reset(db);
  let delivered = 0;
  for (const pass of [1, 2]) {                        // 1: pass with crashes, 2: restart
    for (const m of db.prepare("SELECT message_id, body FROM outbox WHERE status='pending' ORDER BY message_id").all()) {
      delivered += 1;
      try {
        process(db, m, mode, pass === 1 && CRASHES.has(m.message_id));
      } catch {
        if (db.isTransaction) db.exec("ROLLBACK");   // process died: uncommitted work rolled back
      }
    }
  }
  const count = (s) => db.prepare(s).get().n;
  return {
    delivered,
    effect: count("SELECT count(*) AS n FROM notification"),
    lost: 12 - count("SELECT count(DISTINCT message_id) AS n FROM notification"),
    repeated: count("SELECT count(*) AS n FROM notification") - count("SELECT count(DISTINCT message_id) AS n FROM notification"),
  };
}

const db = new DatabaseSync("delivery.db");
console.log("mode".padEnd(16) + "delivered  effect  lost  repeated");
for (const mode of ["at-most-once", "at-least-once", "deduplication"]) {
  const s = run(db, mode);
  console.log(mode.padEnd(16) +
    `${String(s.delivered).padStart(9)}${String(s.effect).padStart(8)}${String(s.lost).padStart(6)}${String(s.repeated).padStart(10)}`);
}

node modes.mjs

mode            delivered  effect  lost  repeated
at-most-once           12       9     3         0
at-least-once          15      15     0         3
deduplication          15      12     0         0

The first row shows the cost of at-most-once. Twelve deliveries were made, nine notifications were sent; three members never got a notification, and no one noticed, because the message had already been marked processed. Loss is silent: since no row is left pending in the outbox, the system sees itself as clean.

The second row shows the opposite direction. No message was lost, but the delivery count rose from twelve to fifteen and fifteen notifications were sent — three members received the same notification twice. In this mode, the measure of repetition is directly how many redeliveries happened.

The third row is the real point, and it needs a careful read. The delivery count is still fifteen. Deduplication did not deduplicate the delivery; the queue still handed out three messages twice. What changed is the effect: twelve notifications were sent, no fewer and no more. The guard saw on the second delivery that the message had already been processed, and skipped the effect.

The Condition Under Which Deduplication Works

The third mode giving the correct result depends on a placement detail: writing the effect and writing the guard row are inside the same transaction boundary. The two writes between BEGIN IMMEDIATE and COMMIT either commit together or roll back together. If they were separated, two new flaws would appear: if the guard is written first and the process dies before the effect is written, the message counts as processed and the notification never goes out at all — the loss from the first row comes back. If the effect is written first and the process dies before the guard, the effect gets reapplied on the second delivery — the repetition from the second row comes back.

The rule established in the Transaction Boundaries lesson shows up here once again: two writes that must be correct together have to share the same atomic boundary. The guard’s job is to make the fact “this message’s effect has been applied” durable at the same instant as the effect itself.

Here, the guard’s key was the message’s identity. If a message has no such identity, or different messages can carry the same job, the key is derived from the meaning of the work; the criterion set in the Idempotency Keys lesson of the Web API Design course is the same one — the key defines the criterion by which a repeat counts as “the same job.”

When the Effect Is Outside the Database

In the previous measurement, the effect was a table row, meaning it fit inside the same transaction as the guard. A real overdue notification goes not to a table but to an outside service, and that call cannot be wrapped inside a database transaction. The run below measures this case: the external effect is written to a file, the acknowledgement is kept in the database, and the process dies exactly between the two.

// external-effect.mjs — when the effect sits outside the database, which order loses what
import { appendFileSync, readFileSync, writeFileSync } from "node:fs";
import { DatabaseSync } from "node:sqlite";

const SENT = "sent.log";               // notifications the notification service actually sent
const RECEIVED = "receiver-keys.log";  // idempotency keys the notification service has seen

function serviceSend(key, hasGuard) {
  if (hasGuard) {
    const seen = new Set(readFileSync(RECEIVED, "utf8").split("\n").filter(Boolean));
    if (seen.has(key)) return false;              // the same key is not processed a second time
    appendFileSync(RECEIVED, key + "\n");
  }
  appendFileSync(SENT, `notification ${key}\n`);
  return true;
}

function run(db, order, hasGuard) {
  writeFileSync(SENT, ""); writeFileSync(RECEIVED, "");
  db.exec("DELETE FROM outbox");
  db.prepare("INSERT INTO outbox (message_id, type, body) VALUES (1,'overdue_notification','{}')").run();
  let calls = 0;

  for (const pass of [1, 2]) {          // 1: pass with a crash, 2: restart
    for (const m of db.prepare("SELECT message_id FROM outbox WHERE status='pending'").all()) {
      const key = `message=${m.message_id}`;
      const acknowledge = () => db.prepare("UPDATE outbox SET status='processed' WHERE message_id=?").run(m.message_id);
      try {
        if (order === "effect first") { calls += 1; serviceSend(key, hasGuard); }
        else acknowledge();
        if (pass === 1) throw new Error("crash");    // process died between the two steps
        if (order === "effect first") acknowledge();
        else { calls += 1; serviceSend(key, hasGuard); }
      } catch { /* process died: retried on the next pass */ }
    }
  }
  const effect = readFileSync(SENT, "utf8").split("\n").filter(Boolean).length;
  return { calls, effect, lost: effect === 0 ? 1 : 0, repeated: Math.max(0, effect - 1) };
}

const db = new DatabaseSync("delivery.db");
console.log("order".padEnd(34) + "calls  effect  lost  repeated");
for (const [name, order, guard] of [["effect first, ack after", "effect first", false],
                                    ["ack first, effect after", "ack first", false],
                                    ["effect first + dedup at receiver", "effect first", true]]) {
  const s = run(db, order, guard);
  console.log(name.padEnd(34) +
    `${String(s.calls).padStart(5)}${String(s.effect).padStart(8)}${String(s.lost).padStart(7)}${String(s.repeated).padStart(11)}`);
}

node external-effect.mjs

order                             calls  effect  lost  repeated
effect first, ack after               2       2      0          1
ack first, effect after               0       0      1          0
effect first + dedup at receiver      2       1      0          0

The first two rows are a closed dilemma. If the send happens first, the notification goes out twice; if the acknowledgement is written first, the notification never goes out at all. There is no third ordering in between, because two different systems — the database and the notification service — share no common transaction boundary. This is exactly the difficulty established in the Distributed Transaction Problem lesson.

The third row shows where the fix lives: the call was made twice, the effect happened once. The difference is that the guard sits not on the sending side but on the receiving side. Once the notification service sees the idempotency key and disregards the second call, the sending side staying in at-least-once mode stops being a flaw.

From this comes the lesson’s conclusion: exactly-once delivery cannot be built end to end; what can be built is exactly-once effect, and its condition is that a guard which makes the repeat idempotent sits at the same boundary as the effect. If the effect is external, that boundary is inside the receiver.

Which Mode, Where

At-most-once is not a flaw, it is a deliberate choice; its condition is that the cost of loss is smaller than the cost of processing. In a usage-metrics stream producing thousands of rows per second, losing a single row does not change the outcome, and writing an acknowledgement for every row would multiply the carrying cost.

In the loan system, the criterion reverses. An overdue notification never going out leads to the member paying a penalty; a line missing from the monthly report shows a wrong number; a returned book the branch stock summary misses shows as still on the shelf. All three call for at-least-once. The cost of repetition, though, varies from job to job: if the same stock summary is calculated twice, the result is the same, but if the same notification is sent twice, the member gets two messages. The guard is mandatory for the jobs in that second group.

Summary

• Writing the acknowledgement before the effect produces at-most-once; writing it after produces at-least-once. Exactly-once is not a separate ordering, it is the result of deduplication layered on top of at-least-once.
• On the same twelve-message workload, at-most-once produced 12 deliveries with 9 effects and 3 losses; at-least-once produced 15 deliveries with 15 effects and 3 repeated notifications.
• In the deduplicated run, the delivery count stayed at 15, but the effect dropped to 12: the guard deduplicates the effect, not the delivery.
• The guard working correctly depends on the effect and the guard row being written inside the same transaction boundary; when they are separated, loss or repetition comes back.
• When the effect sits outside the database, no ordering prevents both: sending first meant the notification went out twice, writing the acknowledgement first meant it never went out at all; with an idempotency key on the receiving side, two calls reduced to a single effect.

Next Step

Delivery count and effect count came apart, but neither measurement looked at the delivery’s order at all. There are places in the loan system where order matters: if the same book’s “issued” and “returned” events are processed out of order, the stock summary shows the book as both on the shelf and with the member. In earlier lessons, the way to speed work up was adding consumers; adding consumers breaks order. The next lesson measures this tension: where partitioning by key preserves order and where it drops it, and how concurrency and order violations change as the number of partitions grows.