Transactions

A transaction groups statements so they succeed or fail together. Recording a fee payment means inserting a payment and reducing the balance — if one step fails, neither should happen.

Start with BEGIN, finish with COMMIT to save or ROLLBACK to undo everything. If any statement errors, PostgreSQL refuses further statements until you roll back. Other sessions never see half-finished changes.

SAVEPOINT lets you undo part of a transaction. The isolation level (READ COMMITTED by default; REPEATABLE READ and SERIALIZABLE are stricter) controls what a transaction sees of other transactions' concurrent changes.

transactions.sqlSQL

-- Record a payment atomically
BEGIN;
INSERT INTO payments (student_id, amount, method) VALUES (2, 100000, 'mpesa');
UPDATE students SET fee_balance = fee_balance - 100000 WHERE id = 2;
COMMIT;

SELECT full_name, fee_balance FROM students WHERE id = 2;   -- 50000.00

-- A failing step undoes the whole transaction
BEGIN;
INSERT INTO payments (student_id, amount, method) VALUES (2, 80000, 'mpesa');
UPDATE students SET fee_balance = fee_balance - 80000 WHERE id = 2;
-- ERROR: new row violates check constraint "students_fee_balance_check"
ROLLBACK;

SELECT count(*) FROM payments WHERE student_id = 2;   -- the 80000 payment was not saved

-- Savepoints: keep the good part, undo the rest
BEGIN;
UPDATE students SET email = 'baraka@example.com' WHERE id = 2;
SAVEPOINT before_risky;
UPDATE students SET email = 'amina@example.com' WHERE id = 2;   -- ERROR: duplicate email
ROLLBACK TO SAVEPOINT before_risky;
COMMIT;

SELECT full_name, email FROM students WHERE id = 2;   -- baraka@example.com

-- A stricter isolation level for a consistent multi-query report
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(fee_balance) FROM students;
SELECT sum(amount) FROM payments;
COMMIT;

Runs in your browser · PostgreSQL

Key points

Wrap related changes in BEGIN … COMMIT so they happen together or not at all.
After an error, ROLLBACK (or roll back to a savepoint) before continuing.
Put CHECK constraints on invariants (like balance ≥ 0) so bad transactions fail.

Exercise

Write a transaction that transfers a student from Form 3 stream A to stream B and logs the change in a new transfers table. Then make it fail on purpose and confirm with SELECT that nothing changed.

Show solution

Try the exercise yourself first — then compare your approach with this one.

Both changes — moving the student and logging it — sit in one transaction, so they happen together or not at all. In the failing version the log insert breaks a CHECK constraint; after ROLLBACK, the SELECT shows the student never moved.

transfers.sqlSQL

CREATE TABLE transfers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id  bigint  NOT NULL REFERENCES students (id),
    from_stream char(1) NOT NULL,
    to_stream   char(1) NOT NULL CHECK (to_stream IN ('A', 'B', 'C')),
    moved_at    timestamptz NOT NULL DEFAULT now()
);

-- Success: Rehema (Form 3, stream A) moves to stream B
BEGIN;
INSERT INTO transfers (student_id, from_stream, to_stream)
SELECT id, stream, 'B' FROM students WHERE full_name = 'Rehema Mollel';
UPDATE students SET stream = 'B' WHERE full_name = 'Rehema Mollel';
COMMIT;

-- Failure on purpose: stream 'Z' breaks the CHECK constraint
BEGIN;
UPDATE students SET stream = 'Z' WHERE full_name = 'Ali Mohamed';
INSERT INTO transfers (student_id, from_stream, to_stream)
SELECT id, 'A', 'Z' FROM students WHERE full_name = 'Ali Mohamed';
-- ERROR: new row for relation "transfers" violates check constraint "transfers_to_stream_check"
ROLLBACK;

SELECT full_name, stream FROM students WHERE full_name IN ('Rehema Mollel', 'Ali Mohamed') ORDER BY full_name;
-- Ali Mohamed | A   (unchanged)  /  Rehema Mollel | B
SELECT count(*) AS transfers_logged FROM transfers;   -- 1

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding