transaction

Commit related database changes together or roll them back.

Use this when A failed step leaves an object only partly saved.

without the term

“Sometimes the order gets created but the inventory doesn't get reduced, because the second step failed. Can you make it so either everything saves or nothing does?”

with the term · How do you ask a model for transaction?

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

X Bluesky LinkedIn

What is transaction?

A transaction groups database operations into a commit unit. Its isolation level determines how concurrent work can interact.

Why does transaction matter when vibe coding?

Sequential writes can each succeed independently and leave inconsistent state.

How do you do transaction?

Keep the database unit short. Check failure between writes and concurrent access to shared records.

What goes wrong with transaction?

An external API call is assumed to roll back with the database.

Before and after

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.

Shared import for both versions

import { DatabaseSync } from 'node:sqlite';

Before

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

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')");
}

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.

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;
  }
}

Check the change

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.

Save the runnable comparison, then run node --experimental-sqlite --test transaction.mjs with Node.js 22.12 or later. This uses Node’s experimental SQLite API and the runtime flag shown above.

Download the complete teaching example: the bug, prompt, runnable code, expected result, limits, and sources, linked back to this page.

Show the assertions

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(); }
});

When this is not enough

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.

The next decision

unique constraint: Enforce the business keys inside the database too.
idempotency: A transaction groups writes; a repeated operation still needs a defined identity and outcome.

Sources and verification

SQLite: Transactions
Explicit BEGIN, COMMIT, and ROLLBACK and SQLite transaction behavior.
Node.js 22: SQLite
Executing the actual SQL in an in-memory database with DatabaseSync.

Sources checked 2026-10-11. These are illustrative examples with automated checks. How we check content.

Did this help with your task?

After trying the prompt or check, tell us what happened. This optional response records only your choice and this term.