Idempotent Transactions

The previous lesson built the outbox scheme and left one condition unmet: processing the same message a second time should not have produced an additional effect. In the measurement, the member’s open loan count rose from one to two; the condition was not met.

The same problem appears on every retry. When a client gets a timeout and repeats a request, whether the server processed the first one is not known; the response may have been lost along the way. This lesson defines when a repeat is harmless and makes the cases where it is not harmless.

A write is an idempotent operation if its second and later runs produce no effect beyond the first. The Web API Design course covered the same property for HTTP methods under the name idempotent method; what matters here is how that property gets built at the data access layer.

Three Write Shapes

Issuing a loan involves three writes: the book’s status changes, the member’s open loan count increases, and a loan row is inserted. The three behave differently under the same repetition.

# setup.sh — empty starting state for the measurement
rm -f library.db
sqlite3 library.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 member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, open_loans INTEGER NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
INSERT INTO book VALUES (1,'Blindness','on_shelf');
INSERT INTO member VALUES (4,'Derek',0);
SQL

// three-writes.mjs — three write shapes, each run three times in a row
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

const writes = {
  "status assignment ": () => db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = 1").run(),
  "counter increment ": () => db.prepare("UPDATE member SET open_loans = open_loans + 1 WHERE member_id = 4").run(),
  "record insert     ": () => db.prepare(
    "INSERT INTO loan (book_id, member_id, pickup_date) VALUES (1,4,'2025-07-20')").run(),
};

for (const [name, write] of Object.entries(writes)) {
  for (let i = 0; i < 3; i++) write();
}
const b = db.prepare("SELECT status FROM book WHERE book_id = 1").get().status;
const m = db.prepare("SELECT open_loans AS n FROM member WHERE member_id = 4").get().n;
const l = db.prepare("SELECT count(*) AS n FROM loan").get().n;
console.log(`after three runs:  book.status=${b}  member.open_loans=${m}  loan rows=${l}`);

sh setup.sh
node three-writes.mjs

after three runs:  book.status=on_loan  member.open_loans=3  loan rows=3

The distinction is clear. Assignment is idempotent: the status = 'on_loan' write brings the target to the same value no matter how many times it runs. Increment is not idempotent, because the result depends on the previous value. Insert is not idempotent, because every call produces a new row.

The rule follows from this: a write is idempotent if its result depends only on the target state; it is not if the result depends on how many times it has run.

Converting to an Idempotent Shape

Both problem writes can be fixed by changing shape. For the record insert, a business-meaningful uniqueness constraint is defined, and the conflict behavior is specified. The counter increment is replaced with an assignment instead: the counter stops being an accumulation and becomes a value derived from the existing rows.

# setup2.sh — business-meaningful uniqueness constraint for the record insert
rm -f library.db
sqlite3 library.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 member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, open_loans INTEGER NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
CREATE UNIQUE INDEX loan_unique ON loan (book_id, member_id, pickup_date);
INSERT INTO book VALUES (1,'Blindness','on_shelf');
INSERT INTO member VALUES (4,'Derek',0);
SQL

// three-writes-idempotent.mjs — the same three tasks, in a shape where repetition produces no effect
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

const writes = {
  "status assignment": () => db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = 1").run(),
  "record insert": () => db.prepare(
    `INSERT INTO loan (book_id, member_id, pickup_date) VALUES (1,4,'2025-07-20')
     ON CONFLICT (book_id, member_id, pickup_date) DO NOTHING`).run(),
  "counter derivation": () => db.prepare(
    `UPDATE member SET open_loans =
       (SELECT count(*) FROM loan WHERE member_id = 4 AND return_date IS NULL)
     WHERE member_id = 4`).run(),
};

for (let i = 0; i < 3; i++) {
  const affected = Object.entries(writes).map(([name, write]) => `${name}=${write().changes}`);
  console.log(`round ${i + 1}: ${affected.join("  ")}`);
}
const b = db.prepare("SELECT status FROM book WHERE book_id = 1").get().status;
const m = db.prepare("SELECT open_loans AS n FROM member WHERE member_id = 4").get().n;
const l = db.prepare("SELECT count(*) AS n FROM loan").get().n;
console.log(`after three rounds:  book.status=${b}  member.open_loans=${m}  loan rows=${l}`);

sh setup2.sh
node three-writes-idempotent.mjs

round 1: status assignment=1  record insert=1  counter derivation=1
round 2: status assignment=1  record insert=0  counter derivation=1
round 3: status assignment=1  record insert=0  counter derivation=1
after three rounds:  book.status=on_loan  member.open_loans=1  loan rows=1

After three rounds, a single loan row and the correct counter value remain. The affected-row counts show the mechanism: from the second round on, the record insert has zero effect. The counter derivation keeps affecting one row every round, but the value it writes does not change; idempotence does not mean the affected-row count is zero, it means the result does not change.

Which columns the uniqueness constraint is built from is a business decision. The constraint here says a member cannot borrow the same book twice on the same day. If the rule is wrong, the idempotence is built wrong too; the constraint must be chosen from the columns that genuinely define the same business intent.

Idempotency Key

A business-meaningful uniqueness cannot always be found. The same member can return the same book and borrow it again the same day; in that case, the second request is not a duplicate request, it is a separate one. The way to tell the intent apart is for the requester to give it a name. The idempotency key, introduced in the Web API Design course, is written to a relation at the data access layer.

The critical detail is the moment of writing: the key and the effect must be written in the same transaction. If they are written in separate transactions, a process that crashes between the two leaves either an unused key or an effect with no key.

# setup3.sh — idempotency-relation setup
rm -f library.db
sqlite3 library.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 loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
CREATE TABLE idempotency (key TEXT PRIMARY KEY, response TEXT NOT NULL);
INSERT INTO book VALUES (1,'Blindness','on_shelf'),(2,'The Disconnected','on_shelf');
SQL

// idempotency.mjs — key and response are written in the same transaction as the effect
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

function issueLoan(key, bookId, memberId, date) {
  db.exec("BEGIN IMMEDIATE");
  const previous = db.prepare("SELECT response FROM idempotency WHERE key = ?").get(key);
  if (previous !== undefined) {
    db.exec("ROLLBACK");
    return { ...JSON.parse(previous.response), replayed: true };
  }
  const s = db.prepare(
    "UPDATE book SET status = 'on_loan' WHERE book_id = ? AND status = 'on_shelf'").run(bookId);
  let response;
  if (s.changes !== 1) {
    response = { status: "rejected", reason: "book not on shelf" };
  } else {
    const id = Number(db.prepare(
      "INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)")
      .run(bookId, memberId, date).lastInsertRowid);
    response = { status: "issued", loanId: id };
  }
  db.prepare("INSERT INTO idempotency (key, response) VALUES (?,?)").run(key, JSON.stringify(response));
  db.exec("COMMIT");
  return { ...response, replayed: false };
}

for (const [key, bookId] of [["LN-7f21", 1], ["LN-7f21", 1], ["LN-7f21", 1], ["LN-9c04", 1]]) {
  console.log(`${key} book=${bookId} ->`, JSON.stringify(issueLoan(key, bookId, 4, "2025-07-20")));
}
console.log("loan rows:", db.prepare("SELECT count(*) AS n FROM loan").get().n);
console.log("idempotency rows:", db.prepare("SELECT count(*) AS n FROM idempotency").get().n);

sh setup3.sh
node idempotency.mjs

LN-7f21 book=1 -> {"status":"issued","loanId":1,"replayed":false}
LN-7f21 book=1 -> {"status":"issued","loanId":1,"replayed":true}
LN-7f21 book=1 -> {"status":"issued","loanId":1,"replayed":true}
LN-9c04 book=1 -> {"status":"rejected","reason":"book not on shelf","replayed":false}
loan rows: 1
idempotency rows: 2

Three calls with the same key produced a single loan record, and all three returned the same identifier. Storing the response does more than just block the effect: the duplicate request also gets a successful response. If the response were not stored, the second call would have to say “already done,” and the client might interpret that as an error.

The fourth row shows an important distinction. A request with a different key is not a duplicate request; it was processed and rejected under the business rule. A rejection is a result too, and it was stored as well — the idempotency relation has two rows. If it were not stored, retrying the same rejected request would run the rule again.

The idempotency relation grows without bound. Keys need a retention window; the window is chosen longer than the clients’ retry period and short enough that the storage cost stays acceptable.

The Outbox Consumer

The measurement from the previous lesson can now be fixed. As the consumer processes a message, it records the message identifier in the same transaction.

# setup4.sh — the consumer side
rm -f membership.db
sqlite3 membership.db >/dev/null <<'SQL'
CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, open_loans INTEGER NOT NULL);
CREATE TABLE processed_message (message_id INTEGER PRIMARY KEY, processed_at TEXT NOT NULL);
INSERT INTO member VALUES (6,'Owen',0);
SQL

// consume.mjs — the message identifier is written in the same transaction as the effect; the second delivery stays idempotent
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("membership.db");
const messageId = Number(process.argv[2]);
const memberId = Number(process.argv[3]);

db.exec("BEGIN IMMEDIATE");
const fresh = db.prepare(
  "INSERT INTO processed_message (message_id, processed_at) VALUES (?, datetime('now')) ON CONFLICT DO NOTHING")
  .run(messageId);
if (fresh.changes === 0) {
  db.exec("ROLLBACK");
  console.log(`message ${messageId}: already processed, effect not applied`);
} else {
  db.prepare("UPDATE member SET open_loans = open_loans + 1 WHERE member_id = ?").run(memberId);
  db.exec("COMMIT");
  console.log(`message ${messageId}: processed`);
}
console.log(`  member ${memberId} open loans =`,
  db.prepare("SELECT open_loans AS n FROM member WHERE member_id = ?").get(memberId).n);

sh setup4.sh
node consume.mjs 1 6
node consume.mjs 1 6
node consume.mjs 2 6

message 1: processed
  member 6 open loans = 1
message 1: already processed, effect not applied
  member 6 open loans = 1
message 2: processed
  member 6 open loans = 2

Message one’s two deliveries produced a single effect; message two was processed as a separate task. The counter that rose to two in the previous lesson is now one. The counter-increment write itself is still not idempotent; what is idempotent is the consumer as a whole. Idempotence comes from either the write’s shape or a guard record; either one is enough.

Writing the guard record in the same transaction as the effect is mandatory here too. If the record is written first and the process crashes afterward, the message counts as processed but its effect was never applied; if the effect is applied first and the record fails to get written, the message gets processed again. Bringing the two under a single boundary closes off both failures.

Summary

• A write is idempotent if its result depends only on the target state; in the measurement, the status assignment gave the same result after three runs, while the counter increment produced 3 and the record insert produced 3 rows.
• The record insert was converted to an idempotent shape with a business-meaningful uniqueness constraint and a conflict behavior, and the counter increment with a derived assignment; a single row remained after three rounds.
• When a business-meaningful uniqueness cannot be found, the request carries its own name; the idempotency key is written in the same transaction as the effect.
• Storing the response lets the duplicate request also get a successful result; a rejected result is stored too.
• When the outbox consumer records the message identifier in the same transaction as the effect, a message delivered twice produced a single effect.

Next Step

The transactions topic closed the four questions that establish a write’s correctness: where the boundary is drawn, what a read sees, how a concurrent update is resolved, and why a retry is safe. These questions were about correctness; none of them were about speed. A data access layer that runs correctly can still be slow, and the source of the slowness is usually not the query itself but the query count. The mapper built in the first lesson ran ten queries for three records. The next topic measures that count, shows how it grows with the record count, and proves through implementation how batch fetching keeps it fixed.