Distributed Transaction Problem

Both solutions in the previous lesson worked inside a single database. The same engine managed both the lock and the version counter; a ROLLBACK call rolled back all the writes together.

This foundation disappears if the loan service is not on its own. If the book catalog sits in one service and member records in another, an “issue a loan” request touches two separate databases, and neither can see the other’s transaction. This lesson builds that situation, measures the loss, and shows two patterns.

The Limit of the Model

The setup below is not a real distributed system. The two services are two modules running on the same machine, on two separate SQLite files; there is no network between them, and calls are made in-process. The one property the model carries is that the two databases do not share a common transaction boundary. The loss of atomicity arises from this property; real distributed-system problems such as network latency, partial failure, and timeouts are not in the model and cannot be inferred from it.

# setup.sh — two services' separate databases
rm -f catalog.db membership.db
sqlite3 catalog.db >/dev/null <<'SQL'
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL,
                    status TEXT NOT NULL CHECK (status IN ('on_shelf','on_loan')));
CREATE TABLE outbox (message_id INTEGER PRIMARY KEY, type TEXT NOT NULL,
                           book_id INTEGER NOT NULL, member_id INTEGER NOT NULL,
                           status TEXT NOT NULL DEFAULT 'pending');
INSERT INTO book VALUES (1,'Blindness','on_shelf'),(2,'The Disconnected','on_shelf'),(3,'The Book of Sand','on_shelf');
SQL
sqlite3 membership.db >/dev/null <<'SQL'
CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL,
                  open_loans INTEGER NOT NULL, limit_count INTEGER NOT NULL);
INSERT INTO member VALUES (4,'Derek',2,3),(5,'Grace',3,3),(6,'Owen',0,9);
SQL

Each service establishes its own transaction boundary internally. It exposes to the outside only calls that carry business meaning.

// services.mjs — two separate databases, two separate services; no shared transaction
import { DatabaseSync } from "node:sqlite";

export const catalog = {
  db: new DatabaseSync("catalog.db"),
  markLoan(bookId) {
    this.db.exec("BEGIN IMMEDIATE");
    const s = this.db.prepare(
      "UPDATE book SET status = 'on_loan' WHERE book_id = ? AND status = 'on_shelf'").run(bookId);
    this.db.exec(s.changes === 1 ? "COMMIT" : "ROLLBACK");
    if (s.changes !== 1) throw new Error("catalog: book not on shelf");
  },
  markShelved(bookId) {
    if (process.env.CATALOG_UNREACHABLE === "1") throw new Error("catalog: service unreachable");
    this.db.exec("BEGIN IMMEDIATE");
    this.db.prepare("UPDATE book SET status = 'on_shelf' WHERE book_id = ?").run(bookId);
    this.db.exec("COMMIT");
  },
  status(bookId) {
    return this.db.prepare("SELECT status FROM book WHERE book_id = ?").get(bookId).status;
  },
};

export const membership = {
  db: new DatabaseSync("membership.db"),
  addLoan(memberId) {
    this.db.exec("BEGIN IMMEDIATE");
    const s = this.db.prepare(
      "UPDATE member SET open_loans = open_loans + 1 WHERE member_id = ? AND open_loans < limit_count")
      .run(memberId);
    this.db.exec(s.changes === 1 ? "COMMIT" : "ROLLBACK");
    if (s.changes !== 1) throw new Error("membership: loan limit exceeded");
  },
  count(memberId) {
    return this.db.prepare("SELECT open_loans FROM member WHERE member_id = ?").get(memberId).open_loans;
  },
};

The Loss of Atomicity

The most direct way to write this makes the two calls in sequence.

// uncompensated.mjs — calls the two services in sequence; no way back if the second one fails
import { catalog, membership } from "./services.mjs";
const [bookId, memberId] = process.argv.slice(2).map(Number);
try {
  catalog.markLoan(bookId);
  membership.addLoan(memberId);
  console.log(`loan issued: book ${bookId} -> member ${memberId}`);
} catch (h) {
  console.log("failed:", h.message);
}
console.log(`  catalog: book ${bookId} status = ${catalog.status(bookId)}`);
console.log(`  membership: member ${memberId} open loans = ${membership.count(memberId)}`);

Member 4 has two open records and a limit of three; the request should go through. Member 5 is at the limit; the request should be rejected.

sh setup.sh
node uncompensated.mjs 1 4
echo "---"
node uncompensated.mjs 2 5

loan issued: book 1 -> member 4
  catalog: book 1 status = on_loan
  membership: member 4 open loans = 3
---
failed: membership: loan limit exceeded
  catalog: book 2 status = on_loan
  membership: member 5 open loans = 3

The second request was rejected, but the book stayed on loan in the catalog. There is now a loaned book that no one took and that appears in no member’s account. No one can borrow it; it cannot be returned either, because there is no record to return.

The try block does not fix this. The error was caught, the code reported it; the change in the catalog had already committed. Atomicity holds inside a transaction boundary; it does not hold between two separate boundaries.

The classic solution is two-phase commit: a coordinator asks every participant “are you ready,” and if all answer yes, it says “commit.” The cost is that participants have to hold their locks between the vote and the decision; if the coordinator becomes unreachable in that gap, the participants wait in an undetermined state. In systems where service boundaries are separated, this cost is mostly unacceptable, and the two patterns below are preferred instead.

The Compensation Pattern

The first pattern imitates atomicity. A compensating step is defined that undoes the effect of each step; when a step fails, the ones that already completed are compensated in reverse. This pattern is called a saga.

// saga.mjs — every step has a compensating step; on failure, completed steps are compensated in reverse
import { catalog, membership } from "./services.mjs";
const [bookId, memberId] = process.argv.slice(2).map(Number);

const steps = [
  { name: "catalog.markLoan", run: () => catalog.markLoan(bookId),
    compensate: () => catalog.markShelved(bookId) },
  { name: "membership.addLoan", run: () => membership.addLoan(memberId), compensate: () => {} },
];

const completed = [];
try {
  for (const s of steps) { s.run(); completed.push(s); console.log(`  step done: ${s.name}`); }
  console.log(`loan issued: book ${bookId} -> member ${memberId}`);
} catch (h) {
  console.log("  error:", h.message);
  let missing = 0;
  for (const s of completed.reverse()) {
    try { s.compensate(); console.log(`  compensated: ${s.name}`); }
    catch (t) { missing += 1; console.log(`  COMPENSATION FAILED: ${s.name} -> ${t.message}`); }
  }
  console.log(missing === 0 ? "request rolled back" : `request left half-finished (${missing} compensation pending)`);
}
console.log(`  catalog: book ${bookId} status = ${catalog.status(bookId)}`);
console.log(`  membership: member ${memberId} open loans = ${membership.count(memberId)}`);

sh setup.sh
node saga.mjs 2 5

  step done: catalog.markLoan
  error: membership: loan limit exceeded
  compensated: catalog.markLoan
request rolled back
  catalog: book 2 status = on_shelf
  membership: member 5 open loans = 3

The book went back to the shelf. From the outside, the result looks like a rollback, but it is not the same thing. In a rollback, the change never becomes visible; in compensation, the change is visible for a while and is then closed off by an opposite change. Anyone who queries the book in that interval sees it as on loan. The system is inconsistent during that interval and reaches consistency later.

The compensating step itself is also a business decision. “Remove the loan mark” is a step that can be compensated. “Send the member a notification” cannot; a notification that has already been sent cannot be taken back, though it can be corrected with a second notification. This is why steps that cannot be compensated are placed at the end of the sequence.

The Limit of Compensation

The compensating call is itself a remote call, and it can fail.

sh setup.sh
CATALOG_UNREACHABLE=1 node saga.mjs 2 5

  step done: catalog.markLoan
  error: membership: loan limit exceeded
  COMPENSATION FAILED: catalog.markLoan -> catalog: service unreachable
request left half-finished (1 compensation pending)
  catalog: book 2 status = on_loan
  membership: member 5 open loans = 3

This returns to the exact same inconsistent state as the beginning. The saga pattern did not remove the problem, it carried the responsibility: there is now a request sitting in a “compensation pending” state in the system, and someone has to retry it. This is why compensating steps are recorded durably; even if the process crashes, a trace of the pending compensation has to remain.

The Outbox

The second pattern catches the problem from a different angle. While the catalog service changes the status, it also writes the “this happened” information to its own database, in the same transaction. Because the two writes sit inside a single boundary, either both happen or neither does.

// write-to-outbox.mjs — writes the status change and the message in the same transaction
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("catalog.db");
const [bookId, memberId] = process.argv.slice(2).map(Number);

db.exec("BEGIN IMMEDIATE");
const s = db.prepare(
  "UPDATE book SET status = 'on_loan' WHERE book_id = ? AND status = 'on_shelf'").run(bookId);
if (s.changes !== 1) { db.exec("ROLLBACK"); console.log("book not on shelf"); process.exit(1); }
db.prepare("INSERT INTO outbox (type, book_id, member_id) VALUES ('loan_issued',?,?)")
  .run(bookId, memberId);
db.exec("COMMIT");
console.log(`catalog: book ${bookId} on loan, message written to outbox`);

Getting the message to its destination is a separate process. This pattern is called an outbox.

// relay.mjs — sends outbox messages to the destination service; stops before marking with --crash
import { DatabaseSync } from "node:sqlite";
import { membership } from "./services.mjs";
const crash = process.argv.includes("--crash");
const db = new DatabaseSync("catalog.db");

const pending = db.prepare(
  "SELECT message_id, book_id, member_id FROM outbox WHERE status = 'pending' ORDER BY message_id").all();

for (const m of pending) {
  membership.addLoan(m.member_id);
  console.log(`relay: message ${m.message_id} sent (member ${m.member_id})`);
  if (crash) { console.log("relay: crashed before marking"); process.exit(0); }
  db.prepare("UPDATE outbox SET status = 'sent' WHERE message_id = ?").run(m.message_id);
}
console.log(`relay: ${pending.length} messages processed`);

The gain is this: if the status changed, the message is guaranteed to exist. If the relay crashes, the message waits in the outbox, and it is sent once the relay restarts. No information is lost.

At-Least-Once Delivery

The gain has a cost. The relay can crash after sending the message but before marking the outbox.

sh setup.sh
node write-to-outbox.mjs 3 6
node relay.mjs --crash
echo "-- rerun --"
node relay.mjs
sqlite3 membership.db "SELECT member_id, open_loans FROM member WHERE member_id = 6;"
sqlite3 catalog.db "SELECT message_id, status FROM outbox;"

catalog: book 3 on loan, message written to outbox
relay: message 1 sent (member 6)
relay: crashed before marking
-- rerun --
relay: message 1 sent (member 6)
relay: 1 messages processed
6|2
1|sent

A single loan was issued, and the member’s open loan count rose to two. The message was delivered twice, and its effect was applied twice.

This is unavoidable. The relay writing the “I sent it” information and the destination processing the message happen in two separate databases; that is, the exact same problem from the first section is back in front of us, one layer down. The only way to cut off the infinite regress is to lower the guarantee and fix up the result: delivery is made at least once, and the receiver produces no additional effect when it receives the same message a second time. The receiver carrying this property is the condition the outbox pattern has to satisfy.

Summary

• Writes made to two separate databases do not share a common transaction boundary; when the second call failed, the first one stayed committed, and a loaned book that no one had taken resulted.
• Two-phase commit preserves atomicity; its cost is that participants hold locks while waiting for the decision.
• The compensation pattern unwinds completed steps in reverse; its difference from a rollback is that the inconsistent state is visible for a while.
• The compensating call can fail too; in that case the pattern does not remove the problem, it leaves a pending compensation record and requires a retry.
• The outbox prevents information loss by writing the status change and the message in the same transaction; its cost is that the message is delivered at least once — sometimes more than once.

Next Step

The outbox pattern has one unmet condition left: processing the same message a second time must not produce an additional effect. In this measurement, the member’s open loan count rose to two, so the condition was not met. The same problem shows up in every retry that comes over the network; when a client gets a timeout and repeats a request, the server needs to know whether it already processed the first one. The next lesson defines why a repeated write produces no additional effect, separates writes that naturally carry this property from ones that do not, and makes the ones that do not safe with an idempotency key.