# transaction: a worked example

Canonical page: https://vibe-coding-101.brighteng.org/c/transaction/#before-and-after

## The problem

Moving one seat from availability into a reservation needs two database writes. If saving the reservation fails after decrementing availability, the system should not silently lose the seat.

## Prompt to paste

Wrap these related database writes in a transaction. Inject a failure between them and verify that none of the partial changes remain.

## Before

Independent writes commit separately. An injected failure after the availability change leaves zero seats and no reservation.

## After

Begin a transaction, execute both writes, and commit together. Roll back on an error and rethrow it. A failed reservation then leaves the original availability intact.

## Run the check

Save the following code as transaction.mjs and run `node --experimental-sqlite --test transaction.mjs` with Node.js 22.12 or later. The SQLite examples use an experimental built-in API and the shown runtime flag.

```js
import test from 'node:test';
import assert from 'node:assert/strict';
import { DatabaseSync } from 'node:sqlite';

function before(db, fail = false) {
  db.exec('UPDATE inventory SET seats = seats - 1');
  if (fail) throw new Error('reservation failed');
  db.exec("INSERT INTO reservations VALUES ('r1')");
}

function after(db, fail = false) {
  db.exec('BEGIN');
  try {
    db.exec('UPDATE inventory SET seats = seats - 1');
    if (fail) throw new Error('reservation failed');
    db.exec("INSERT INTO reservations VALUES ('r1')");
    db.exec('COMMIT');
  } catch (error) {
    db.exec('ROLLBACK');
    throw error;
  }
}

function fixture() {
  const db = new DatabaseSync(':memory:');
  db.exec(`CREATE TABLE inventory (seats INTEGER NOT NULL CHECK (seats >= 0));
    INSERT INTO inventory VALUES (1);
    CREATE TABLE reservations (id TEXT NOT NULL UNIQUE);`);
  return db;
}
function state(db) {
  return [db.prepare('SELECT seats FROM inventory').get().seats,
    db.prepare('SELECT count(*) AS n FROM reservations').get().n];
}

test('a failure between independent writes loses a seat', () => {
  const db = fixture();
  try {
    assert.throws(() => before(db, true), /reservation failed/);
    assert.deepEqual(state(db), [0, 0]);
  } finally { db.close(); }
});

test('a transaction rolls back failure and commits success', () => {
  const db = fixture();
  try {
    assert.throws(() => after(db, true), /reservation failed/);
    assert.deepEqual(state(db), [1, 0]);
    after(db);
    assert.deepEqual(state(db), [0, 1]);
    assert.throws(() => after(db), /CHECK constraint failed/);
    assert.deepEqual(state(db), [0, 1]);
  } finally { db.close(); }
});
```

## Expected result

The independent-write failure leaves [0 available seats, 0 reservations]. Rolling back leaves [1, 0], and a successful transaction leaves [0, 1]. A later attempt violating the nonnegative-seat constraint leaves the committed state unchanged.

## Limits

This synchronous SQLite example assumes one inventory row and tests a failure between statements. It does not prove crash recovery, high-contention behavior, or a production booking design. Select isolation and conflict/retry handling for your database and workload. A database rollback cannot undo a completed payment, email, or other external API call. Keep such coordination explicit.

## Sources

- [SQLite: Transactions](https://www.sqlite.org/lang_transaction.html): Explicit BEGIN, COMMIT, and ROLLBACK and SQLite transaction behavior.
- [Node.js 22: SQLite](https://nodejs.org/docs/latest-v22.x/api/sqlite.html): Executing the actual SQL in an in-memory database with DatabaseSync.

Sources checked: 2026-10-11. These are original illustrative examples with automated checks. Human editorial approval is not recorded. Policy: https://vibe-coding-101.brighteng.org/editorial/