Transactions & Atomicity

Some operations are only correct when several writes succeed together. A warehouse transfer must not remove units from one location and then lose them before adding them elsewhere. A transaction defines the boundary of that all-or-nothing change.

Start with the invariant

An invariant is a condition that must remain true after an operation. Suppose stock is stored in two warehouse rows. Moving three units must preserve the total quantity. It must not create a negative source quantity or silently accept a nonexistent destination.

Without a transaction:

subtract from source → process fails → destination is never updated

The SQL statements can each be individually valid while the use case is wrong. The PostgreSQL transaction tutorial describes transactions as a unit of work that commits or rolls back together.

Prepare the stock table

In practice/, add database-migrations/003_stock.sql, using the next unused migration number. The CHECK constraint prevents negative stock:

-- migrate:up
CREATE TABLE lab_stock (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  quantity INTEGER NOT NULL CHECK (quantity >= 0)
);
-- migrate:down
DROP TABLE lab_stock;

From practice/, apply the development migration, then recreate only the isolated test project. The final command applies the entire migration chain to its fresh database:

docker compose run --rm database-migrations
docker compose -p wsd-practice-test -f compose.yaml -f compose.test.yaml down
docker compose -p wsd-practice-test -f compose.yaml -f compose.test.yaml up -d --build database database-migrations

Use the transaction-scoped connection

postgres.js provides sql.begin(async (tx) => ...). It supplies a transaction-scoped connection, tx, to the callback.

First, create api/src/repositories/stock-repository.ts with two functions that update stock:

import type postgres from "postgres";

export const debit = async (
  tx: postgres.TransactionSql, id: number, quantity: number,
): Promise<boolean> => {
  const rows = await tx`
    UPDATE lab_stock SET quantity = quantity - ${quantity}
    WHERE id = ${id} AND quantity >= ${quantity} RETURNING id
  `;
  return rows.length === 1;
};

export const credit = async (
  tx: postgres.TransactionSql, id: number, quantity: number,
): Promise<boolean> => {
  const rows = await tx`
    UPDATE lab_stock SET quantity = quantity + ${quantity}
    WHERE id = ${id} RETURNING id
  `;
  return rows.length === 1;
};

The debit combines its stock check and change in one SQL statement. It returns false when the source is missing or lacks enough stock. The credit returns false when the destination is missing. These results let the service decide whether the transfer can complete.

Then, create api/src/services/stock-service.ts:

import { sql } from "@src/database.ts";
import * as stock from "@src/repositories/stock-repository.ts";

export const transferStock = async (
  fromId: number, toId: number, quantity: number,
) => {
  await sql.begin(async (tx) => {
    const debited = await stock.debit(tx, fromId, quantity);
    if (!debited) throw new Error("Source missing or insufficient stock");

    const credited = await stock.credit(tx, toId, quantity);
    if (!credited) throw new Error("Destination missing");
  });
};

The service imports the shared sql client from api/src/database.ts, opens this transaction, and passes tx to every participating repository function. Those functions use the supplied connection instead of importing the application pool or opening transactions of their own.

Both repository calls receive the same tx. If the destination is missing after the debit, the service throws and the transaction rolls back that earlier write. Successful completion of the callback leads to commit. The caller must await the operation before reporting success.

A successful callback can also return a value, such as a created record. sql.begin(...) resolves with that value after commit. To pass it to the caller, the service can use return await sql.begin(...).

To roll back a transaction, throw an exception inside the callback.

A rollback test must observe stored state

Figure 1 follows the failure exercised by the test below. The source update has already run when the service discovers that the destination is missing. Throwing inside the callback makes PostgreSQL roll back that earlier write.

The test first transfers three units between rows holding 10 and 5, producing balances of [7, 8]. It then attempts a transfer to a missing destination. The fixture creates and deletes a third row to obtain an ID known to be absent, so the second transfer fails after its source update. Both balances must remain [7, 8] after rejection.

The test callback receives Deno’s test context as t. Each awaited t.step(...) names a check and runs it before the next one. The steps share their fixture rows and pool, so the cleanup pattern from Chapter 4 belongs after both steps.

Create api/tests/stock-service_test.ts:

import { assertEquals, assertRejects } from "@std/assert";
import { sql } from "@src/database.ts";
import { transferStock } from "@src/services/stock-service.ts";

Deno.test("stock transfers preserve stored state on failure", async (t) => {
  const ids: number[] = [];
  try {
    for (const quantity of [10, 5, 0]) {
      const [row] = await sql`INSERT INTO lab_stock (quantity) VALUES (${quantity}) RETURNING id`;
      ids.push(row.id);
    }
    const [source, destination, missing] = ids;
    await sql`DELETE FROM lab_stock WHERE id = ${missing}`;

    await t.step("commit both balances", async () => {
      await transferStock(source, destination, 3);
      const rows = await sql`SELECT quantity FROM lab_stock WHERE id IN ${sql(ids)} ORDER BY id`;
      assertEquals(rows.map((row) => row.quantity), [7, 8]);
    });

    await t.step("a missing destination rolls back the debit", async () => {
      await assertRejects(() => transferStock(source, missing, 2), Error, "Destination missing");
      const rows = await sql`SELECT quantity FROM lab_stock WHERE id IN ${sql(ids)} ORDER BY id`;
      assertEquals(rows.map((row) => row.quantity), [7, 8]);
    });

  } finally {
    try {
      if (ids.length > 0) {
        await sql`DELETE FROM lab_stock WHERE id IN ${sql(ids)}`;
      }
    } finally {
      await sql.end();
    }
  }
});

The stored quantities establish both commit and rollback of the earlier debit. Checking only the rejected promise would not reveal whether that debit remained in the database. A fake repository cannot establish PostgreSQL rollback behavior.

From practice/, run:

docker compose -p wsd-practice-test -f compose.yaml -f compose.test.yaml run --build --rm api deno test --cached-only --allow-env --allow-net tests/stock-service_test.ts

Parse ordinary input before opening a transaction; perform checks that depend on stored state inside its protected operations. In the next exercise, the service coordinates several writes by passing the same tx to the supplied repository functions, then returns the created result after commit.

Database rollback does not undo external actions

An email sent during a database transaction cannot be undone by PostgreSQL rollback. The same applies to a request accepted by another service: only the participating database writes roll back. Handling external delivery failures is covered later in the course.

Check Your Understanding

1. Moving stock subtracts units from one warehouse row and adds the same units to another. Which total-quantity invariant connects those writes?
2. What does the transfer service decide, what do its repository functions do, and why must both repository calls use the same transaction-scoped connection?
3. Why is returning an error-shaped object not necessarily a rollback?
4. What must a rollback test inspect besides an exception?
5. Which effects are outside the database transaction’s guarantee?