Transactions and savepoints

A transaction is a unit of work, not a speed trick. Name the business fact that must not exist halfway—an order with its lines, a debit with its credit—then BEGIN, write, and either COMMIT or ROLLBACK. A savepoint is a bookmark inside that unit, for when only the later steps should be undone.

Without BEGIN, each statement commits itself

SQLite (and most engines) autocommit successful statements when no transaction is open. The two inserts below are two units of work. If the process died after the first, the first row would already be durable. That is why “insert the order, then insert the lines” without BEGIN can leave a header with no items.

CREATE TABLE notes (
  note_id INTEGER PRIMARY KEY,
  body TEXT NOT NULL
);

INSERT INTO notes (body) VALUES ('first autocommit');
INSERT INTO notes (body) VALUES ('second autocommit');

SELECT note_id, body
FROM notes
ORDER BY note_id;

Edit the query, predict the rows it will return, then run it.

BEGIN then COMMIT publishes the whole unit

BEGIN opens a transaction. Inserts inside it are not finished business until COMMIT. After commit, a SELECT sees both notes. The unit of work was “store these two lines together,” and the database kept that promise.

CREATE TABLE notes (
  note_id INTEGER PRIMARY KEY,
  body TEXT NOT NULL
);

BEGIN;
INSERT INTO notes (body) VALUES ('inside the unit');
INSERT INTO notes (body) VALUES ('still inside the unit');
COMMIT;

SELECT note_id, body
FROM notes
ORDER BY note_id;

Edit the query, predict the rows it will return, then run it.

ROLLBACK forgets the open unit, not earlier commits

The first insert sits outside the transaction, so it is already committed. The next two sit inside BEGIN and are discarded by ROLLBACK. Read the table: one row remains. Rollback is not “undo the database.” It is “undo this unit.”

CREATE TABLE notes (
  note_id INTEGER PRIMARY KEY,
  body TEXT NOT NULL
);

INSERT INTO notes (body) VALUES ('already committed');

BEGIN;
INSERT INTO notes (body) VALUES ('should vanish');
INSERT INTO notes (body) VALUES ('also vanish');
ROLLBACK;

SELECT note_id, body
FROM notes
ORDER BY note_id;

Edit the query, predict the rows it will return, then run it.

A savepoint undoes part of an open transaction

SAVEPOINT names a bookmark. ROLLBACK TO that name undoes work after the bookmark and keeps work before it. RELEASE drops the bookmark. The transaction is still open until COMMIT or a full ROLLBACK.

Use this during a bulk import: keep the reviewed batch, throw away the experimental batch, then commit. Full rollback would discard the reviewed rows too.

CREATE TABLE import_rows (
  sku TEXT PRIMARY KEY,
  title TEXT NOT NULL
);

BEGIN;
INSERT INTO import_rows (sku, title) VALUES ('NB-1', 'Notebook');
SAVEPOINT after_good_batch;
INSERT INTO import_rows (sku, title) VALUES ('ST-1', 'Sticker');
INSERT INTO import_rows (sku, title) VALUES ('PS-1', 'Poster');
ROLLBACK TO after_good_batch;
RELEASE after_good_batch;
COMMIT;

SELECT sku, title
FROM import_rows
ORDER BY sku;

Edit the query, predict the rows it will return, then run it.

A transfer is one fact: money leaves and arrives

Debiting Ada 400 cents and crediting Grace 400 cents is not two independent updates. It is one transfer. Wrap both in a transaction so a crash cannot credit without debiting. Starting balances are 500 and 100. After a successful 400-cent transfer they should be 100 and 500.

CREATE TABLE wallets (
  wallet_id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO wallets (wallet_id, owner, cents)
VALUES
  (1, 'Ada', 500),
  (2, 'Grace', 100);

BEGIN;
UPDATE wallets SET cents = cents - 400
WHERE wallet_id = 1 AND cents >= 400;
UPDATE wallets SET cents = cents + 400
WHERE wallet_id = 2;
COMMIT;

SELECT wallet_id, owner, cents
FROM wallets
ORDER BY wallet_id;

Edit the query, predict the rows it will return, then run it.

A transaction will happily commit a business bug

SQL success is not business success. UPDATE ... WHERE cents >= 900 matches zero rows when Ada has 500. That statement still “succeeds.” The following credit still runs. After COMMIT, Grace has 1000 cents and Ada still has 500. You invented money. The transaction did exactly what you asked.

CREATE TABLE wallets (
  wallet_id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO wallets (wallet_id, owner, cents)
VALUES
  (1, 'Ada', 500),
  (2, 'Grace', 100);

BEGIN;
UPDATE wallets SET cents = cents - 900
WHERE wallet_id = 1 AND cents >= 900;
UPDATE wallets SET cents = cents + 900
WHERE wallet_id = 2;
COMMIT;

SELECT wallet_id, owner, cents
FROM wallets
ORDER BY wallet_id;

Edit the query, predict the rows it will return, then run it.

Capture changes() after the debit. Only credit when that count is 1. Zero-row debit, zero-row credit, balances unchanged. No error required—just a guard that matches the transfer rule.

CREATE TABLE wallets (
  wallet_id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO wallets (wallet_id, owner, cents)
VALUES
  (1, 'Ada', 500),
  (2, 'Grace', 100);

BEGIN;
UPDATE wallets SET cents = cents - 900
WHERE wallet_id = 1 AND cents >= 900;
CREATE TEMP TABLE debit_result AS SELECT changes() AS applied;
UPDATE wallets SET cents = cents + 900
WHERE wallet_id = 2 AND (SELECT applied FROM debit_result) = 1;
COMMIT;

SELECT wallet_id, owner, cents
FROM wallets
ORDER BY wallet_id;

Edit the query, predict the rows it will return, then run it.

Parent and child rows are one unit too

An order without items is not an order. Turn foreign keys on for this connection, then insert the header and the lines before COMMIT. If you skipped BEGIN and the second item insert failed, SQLite would already have committed the header. That is the autocommit trap from the first example, now with a real schema.

PRAGMA foreign_keys = ON;

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL
);

CREATE TABLE order_items (
  item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders (order_id),
  sku TEXT NOT NULL,
  qty INTEGER NOT NULL CHECK (qty > 0)
);

BEGIN;
INSERT INTO orders (order_id, customer) VALUES (10, 'Ada');
INSERT INTO order_items (order_id, sku, qty) VALUES
  (10, 'NB-1', 1),
  (10, 'ST-1', 2);
COMMIT;

SELECT o.order_id, o.customer, i.sku, i.qty
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
ORDER BY i.item_id;

Edit the query, predict the rows it will return, then run it.

A transaction checklist

1. Write the business sentence: which facts must appear together or not at all.
2. BEGIN (or start the transaction in your client library).
3. Perform the writes. Keep the WHERE clauses from the last two lessons.
4. Check results that SQL treats as success but the business treats as failure: changes() = 0, missing parent, insufficient seats.
5. On failure, ROLLBACK. On a recoverable inner failure, ROLLBACK TO a savepoint.
6. On success, COMMIT, then SELECT the unit you intended to publish.
7. Remember the engine: PostgreSQL needs rollback after an error; SQLite may still have earlier statements in the transaction.

Independent lab: one seat, two booking attempts

A workshop has one seat. Ada's booking must decrement seats and insert a registration in one transaction, gated on changes(). Grace tries the same afterward. Predict: Ada is registered, seats_left is 0, Grace is absent. Run it. Then change the first COMMIT to ROLLBACK and run again: both learners should be absent and the seat should remain 1.

PRAGMA foreign_keys = ON;

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  seats_left INTEGER NOT NULL CHECK (seats_left >= 0)
);

CREATE TABLE registrations (
  registration_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL REFERENCES workshops (workshop_id),
  learner TEXT NOT NULL
);

INSERT INTO workshops (workshop_id, title, seats_left)
VALUES (10, 'Transactions in practice', 1);

BEGIN;
UPDATE workshops
SET seats_left = seats_left - 1
WHERE workshop_id = 10 AND seats_left >= 1;
CREATE TEMP TABLE seat_ok AS SELECT changes() AS applied;
INSERT INTO registrations (workshop_id, learner)
SELECT 10, 'Ada'
WHERE (SELECT applied FROM seat_ok) = 1;
COMMIT;

BEGIN;
UPDATE workshops
SET seats_left = seats_left - 1
WHERE workshop_id = 10 AND seats_left >= 1;
CREATE TEMP TABLE seat_ok_2 AS SELECT changes() AS applied;
INSERT INTO registrations (workshop_id, learner)
SELECT 10, 'Grace'
WHERE (SELECT applied FROM seat_ok_2) = 1;
COMMIT;

SELECT w.workshop_id, w.title, w.seats_left, r.learner
FROM workshops AS w
LEFT JOIN registrations AS r ON r.workshop_id = w.workshop_id
ORDER BY r.registration_id;

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Inserting a parent, then children, with autocommit, and calling that “atomic.”
• Assuming a zero-row UPDATE aborts the transaction.
• Crediting a wallet without checking that the debit applied.
• Catching an error in PostgreSQL and continuing to write on the same transaction.
• Using ROLLBACK TO when you meant a full ROLLBACK, or the reverse.
• Forgetting PRAGMA foreign_keys = ON in SQLite and believing the FK protected you.
• Leaving a transaction open while waiting on a user or an HTTP call.
• Skipping the verification SELECT after COMMIT.

Lesson review

• I can explain autocommit versus an explicit BEGIN.
• I can COMMIT a unit of work and ROLLBACK an uncommitted one.
• I can use a savepoint to undo only the later part of a transaction.
• I can wrap a debit and credit so money cannot be created by a zero-row debit.
• I can keep parent and child rows in one transaction with foreign keys on.
• I can say how PostgreSQL and SQLite differ after an error inside a transaction.