Transaction Boundaries

The migration runner opened every step with BEGIN, closed it with COMMIT, and called ROLLBACK on failure. That was what made the step itself and the version record get written together.

The same question holds for the application’s normal operation. A loan request is not a single write: a loan record is added, the book’s status changes, a row is written to the entry log. Must all of these commit together, where is the boundary drawn, and who opens it? The concept of a transaction was defined in the Data Modeling and Relational Theory course; this lesson establishes where the boundary sits in the application layer.

A Task with Three Writes

The schema below adds the book’s status and the entry log. The entry type is constrained by a check condition; throughout the lesson, this constraint is what produces the error.

# setup.sh — sets up the lesson database from scratch
rm -f library.db
sqlite3 library.db >/dev/null <<'SQL'
PRAGMA journal_mode = WAL;
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL,
                    status TEXT NOT NULL DEFAULT 'on_shelf' CHECK (status IN ('on_shelf','on_loan')));
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
CREATE TABLE entry (entry_id INTEGER PRIMARY KEY, loan_id INTEGER NOT NULL,
                      type TEXT NOT NULL CHECK (type IN ('issued','returned')), date TEXT NOT NULL);
INSERT INTO book VALUES (1,'Blindness','on_shelf'),(2,'The Disconnected','on_shelf'),(3,'The Book of Sand','on_shelf');
SQL

Being able to see the status at every step requires a small reader.

// status.mjs — prints the row counts of the three relations and the book's status
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");
const count = (t) => db.prepare(`SELECT count(*) AS n FROM ${t}`).get().n;
console.log(`loan=${count("loan")}  entry=${count("entry")}  book1_status=${
  db.prepare("SELECT status FROM book WHERE book_id = 1").get().status}`);

Without a Boundary

The first version runs the three writes back to back; there is no boundary between them.

// no-transaction.mjs — three writes, no transaction boundary
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");
const type = process.argv[2] ?? "issued";

const result = db.prepare(
  "INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)").run(1, 4, "2025-07-20");
const loanId = Number(result.lastInsertRowid);
db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = ?").run(1);
try {
  db.prepare("INSERT INTO entry (loan_id, type, date) VALUES (?,?,?)")
    .run(loanId, type, "2025-07-20");
  console.log("all three writes completed");
} catch (h) {
  console.log("third write failed:", h.message);
}

The error is produced by giving the third write an invalid type.

sh setup.sh
node no-transaction.mjs "overdue"
node status.mjs

third write failed: CHECK constraint failed: type IN ('issued','returned')
loan=1  entry=0  book1_status=on_loan

The resulting state is inconsistent. The loan record exists, the book shows as on loan, but there is no corresponding row in the entry log. From the application’s point of view, this is a book that has been “issued but not recorded as issued”; it does not appear in any report, and it is unclear which rule the return flow should apply.

Inside the Boundary

The result changes when the same three writes are placed inside a transaction boundary.

// with-transaction.mjs — the same three writes, inside a single transaction boundary
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");
const type = process.argv[2] ?? "issued";

db.exec("BEGIN");
try {
  const result = db.prepare(
    "INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)").run(1, 4, "2025-07-20");
  const loanId = Number(result.lastInsertRowid);
  db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = ?").run(1);
  db.prepare("INSERT INTO entry (loan_id, type, date) VALUES (?,?,?)")
    .run(loanId, type, "2025-07-20");
  db.exec("COMMIT");
  console.log("transaction committed");
} catch (h) {
  db.exec("ROLLBACK");
  console.log("transaction rolled back:", h.message);
}

sh setup.sh
node with-transaction.mjs "overdue"
node status.mjs
node with-transaction.mjs issued
node status.mjs

transaction rolled back: CHECK constraint failed: type IN ('issued','returned')
loan=0  entry=0  book1_status=on_shelf
transaction committed
loan=1  entry=1  book1_status=on_loan

The first call left no write in place; the second left all three. There is no in-between state. This is the definition of a unit of work: a set of writes that looks like a single change from the outside, and is applied either completely or not at all.

The boundary of a unit of work is the boundary of the business rule, not a technical boundary. “Issuing a loan” is a unit of work because half of it is meaningless. “Preparing the end-of-day report” and “issuing a loan” are separate units of work, because the failure of one does not invalidate the other.

If the Boundary Is Drawn at the Repository

Where the boundary should not be drawn can be seen by looking at what happens when it is. If every repository method opens and closes its own transaction, two methods cannot be atomic together.

// own-transaction.mjs — what happens if each repository method opens and closes its own transaction
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

const loanRepository = {
  add(bookId, memberId, date) {
    db.exec("BEGIN");
    const s = db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)")
                .run(bookId, memberId, date);
    db.exec("COMMIT");
    return Number(s.lastInsertRowid);
  },
};
const entryRepository = {
  add(loanId, type, date) {
    db.exec("BEGIN");
    try {
      db.prepare("INSERT INTO entry (loan_id, type, date) VALUES (?,?,?)")
        .run(loanId, type, date);
      db.exec("COMMIT");
    } catch (h) { db.exec("ROLLBACK"); throw h; }
  },
};

try {
  const id = loanRepository.add(1, 4, "2025-07-20");
  entryRepository.add(id, "overdue", "2025-07-20");
} catch (h) {
  console.log("service got an error:", h.message);
}

console.log("try opening an outer transaction:");
db.exec("BEGIN");
try {
  loanRepository.add(2, 5, "2025-07-21");
} catch (h) {
  console.log("  ->", h.message);
  db.exec("ROLLBACK");
}

sh setup.sh
node own-transaction.mjs
node status.mjs

service got an error: CHECK constraint failed: type IN ('issued','returned')
try opening an outer transaction:
  -> cannot start a transaction within a transaction
loan=1  entry=0  book1_status=on_shelf

Two separate problems show up in the same output. First: the loan record committed, the entry could not be written; even though the service caught an error, a half-finished state remained in the database. Second: when the service tried to solve the problem by opening a transaction itself, the repository method could not open a second transaction from inside and raised an error.

The result is the rule: the repository does not open the transaction. The repository works on the connection it is given; a higher layer decides where the boundary begins and ends.

The Unit of Work

The structure that carries the boundary is the same structure that supplies the connection the repositories will use.

// unit-of-work.mjs — a unit of work that keeps the transaction boundary in one place; repositories take the connection from outside
export class UnitOfWork {
  constructor(db) { this.db = db; this.depth = 0; }
  run(work) {
    const isNested = this.depth > 0;
    const savepoint = `savepoint_${this.depth}`;
    this.db.exec(isNested ? `SAVEPOINT ${savepoint}` : "BEGIN");
    this.depth += 1;
    try {
      const result = work(this.db);
      this.db.exec(isNested ? `RELEASE ${savepoint}` : "COMMIT");
      return result;
    } catch (h) {
      this.db.exec(isNested ? `ROLLBACK TO ${savepoint}` : "ROLLBACK");
      throw h;
    } finally {
      this.depth -= 1;
    }
  }
}

export const loanRepository = {
  add(db, bookId, memberId, date) {
    return Number(db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)")
                    .run(bookId, memberId, date).lastInsertRowid);
  },
};
export const bookRepository = {
  markLoan(db, bookId) {
    db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = ?").run(bookId);
  },
};
export const entryRepository = {
  add(db, loanId, type, date) {
    db.prepare("INSERT INTO entry (loan_id, type, date) VALUES (?,?,?)")
      .run(loanId, type, date);
  },
};

The depth counter distinguishes nested calls. The outermost call opens the real transaction; the inner calls set up the savepoint introduced in the Advanced SQL course. This way, the failure of an inner unit rolls back only its own writes, without bringing down the outer unit.

// service.mjs — the unit of work gathers three repository calls into a single boundary
import { DatabaseSync } from "node:sqlite";
import { UnitOfWork, loanRepository, bookRepository, entryRepository } from "./unit-of-work.mjs";

const unitOfWork = new UnitOfWork(new DatabaseSync("library.db"));

function issueLoan(bookId, memberId, date, type) {
  return unitOfWork.run((db) => {
    const loanId = loanRepository.add(db, bookId, memberId, date);
    bookRepository.markLoan(db, bookId);
    entryRepository.add(db, loanId, type, date);
    return loanId;
  });
}

for (const [bookId, type] of [[1, "overdue"], [2, "issued"]]) {
  try {
    console.log(`book ${bookId} issued, loan_id =`, issueLoan(bookId, 4, "2025-07-20", type));
  } catch (h) {
    console.log(`book ${bookId} failed:`, h.message);
  }
}

sh setup.sh
node service.mjs
node status.mjs
sqlite3 -header -column library.db "SELECT book_id, status FROM book;"

book 1 failed: CHECK constraint failed: type IN ('issued','returned')
book 2 issued, loan_id = 1
loan=1  entry=1  book1_status=on_shelf
book_id  status  
-------  --------
1        on_shelf
2        on_loan 
3        on_shelf

The first request was rolled back completely: book 1 stayed on the shelf. The second request committed all three writes together. The service code contains no BEGIN; the boundary is the run call itself.

The behavior of a nested boundary can be seen as well.

// nested.mjs — the outer unit of work still commits even though the inner one fails
import { DatabaseSync } from "node:sqlite";
import { UnitOfWork, loanRepository, bookRepository, entryRepository } from "./unit-of-work.mjs";

const unitOfWork = new UnitOfWork(new DatabaseSync("library.db"));

unitOfWork.run((db) => {
  const loanId = loanRepository.add(db, 3, 6, "2025-07-22");
  bookRepository.markLoan(db, 3);
  entryRepository.add(db, loanId, "issued", "2025-07-22");
  try {
    unitOfWork.run((inner) => entryRepository.add(inner, loanId, "notification", "2025-07-22"));
  } catch (h) {
    console.log("inner unit of work rolled back:", h.message);
  }
  console.log("outer unit of work continues, loan_id =", loanId);
});

sh setup.sh
node nested.mjs
node status.mjs

inner unit of work rolled back: CHECK constraint failed: type IN ('issued','returned')
outer unit of work continues, loan_id = 1
loan=1  entry=1  book1_status=on_shelf

The inner unit’s write was rolled back, the outer three writes committed. The entry count is one; if it had been two, the inner unit would not have been rolled back.

The Width of the Boundary

Drawing the boundary wide increases atomicity, but it has a cost: the locks a transaction holds do not release for as long as it stays open. The cost can be measured.

// long-transaction.mjs — keeps a write transaction open for the given duration
import { DatabaseSync } from "node:sqlite";
import { writeFileSync, rmSync } from "node:fs";
const db = new DatabaseSync("library.db");
const durationMs = Number(process.argv[2]);
db.exec("BEGIN IMMEDIATE");
db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = 1").run();
writeFileSync("ready", "");                       // B can now try
const end = Date.now() + durationMs;
while (Date.now() < end) { /* model of waiting on an outbound call */ }
db.exec("COMMIT");
rmSync("ready");
console.log(`A: transaction stayed open for ${durationMs} ms and committed`);

// waiting-write.mjs — how long the second connection waits to write to the same row
import { DatabaseSync } from "node:sqlite";
import { existsSync } from "node:fs";
while (!existsSync("ready")) { /* wait for A to open its transaction */ }
const db = new DatabaseSync("library.db");
db.exec("PRAGMA busy_timeout = 10000");
const t = performance.now();
db.exec("BEGIN IMMEDIATE");
db.prepare("UPDATE book SET status = 'on_shelf' WHERE book_id = 1").run();
db.exec("COMMIT");
console.log(`B: waited ${(performance.now() - t).toFixed(0)} ms to write`);

# boundary-width.sh — the longer the transaction stays open, the longer the second writer waits
for duration in 100 400 800; do
  sh setup.sh >/dev/null
  rm -f ready
  node waiting-write.mjs &
  waiting=$!
  node long-transaction.mjs $duration
  wait $waiting
done

sh boundary-width.sh

A: transaction stayed open for 100 ms and committed
B: waited 114 ms to write
A: transaction stayed open for 400 ms and committed
B: waited 403 ms to write
A: transaction stayed open for 800 ms and committed
B: waited 844 ms to write

The second writer’s wait tracks the first transaction’s open duration. The times are machine-dependent; what does not change is that the relationship is linear. The loop inside long-transaction.mjs is a model, and it represents waiting inside a transaction for an outbound call — sending a notification, calling a payment service, writing a file.

Two rules follow from this. No outbound call is made inside a transaction boundary; an outbound call runs either before or after the boundary. And the boundary is never held one write wider than the business rule requires.

Summary

• When one of the three writes failed, the unbounded version left a half-finished state: the loan record was added and the book’s status changed, but the entry log stayed empty.
• When the same writes were placed inside a single transaction boundary, the result was either all of them or none; a unit of work is the name for this set, and the business rule determines its boundary.
• Opening a transaction per repository produced two problems: the calls failed to compose, and they conflicted with a transaction opened from outside. The repository does not open the transaction; a higher layer does.
• The unit of work sets up a real transaction at the outermost level and a savepoint on the inside; the failure of an inner unit did not bring down the outer one.
• For as long as a transaction stays open, the second writer waits: transactions that stayed open 100, 400, and 800 ms produced waits of 114, 403, and 844 ms respectively. An outbound call is not placed inside a transaction boundary.

Next Step

In this lesson, two connections wanted to write to the same row, and one waited for the other. A write–write conflict is the most visible case; the ones on the read side are quieter. If another transaction reads the same rows while one is in progress, what does it see: the state before the change, the state after, or a mix of both depending on timing? The answer depends on the isolation level, and the level chosen changes the data the application sees. The next lesson runs the same query at two different levels and measures how a nonrepeatable read and a phantom row look from the application.