Transactions

All of it or none of it, and what "isolation" is protecting you from.

After this lesson you can

Wrap several statements so they succeed or fail together
Name what each of the four ACID letters guarantees
Explain the anomalies the isolation levels rule out

A transaction groups statements so the database applies all of them or none of them.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the second update fails, ROLLBACK leaves the first as if it never happened. Money does not evaporate between two statements.

ACID, in one line each

Atomic — the whole transaction applies, or none of it does.
Consistent — constraints hold before and after; a transaction cannot leave the database in a state the schema forbids.
Isolated — concurrent transactions do not see each other's half-finished work.
Durable — once COMMIT returns, the change survives the power going out.

What isolation is actually for

Isolation levels are defined by the anomalies they forbid:

Dirty read — you see another transaction's uncommitted change. Every level above READ UNCOMMITTED forbids this, and Postgres never allows it.
Non-repeatable read — you read a row twice in one transaction and get two different values, because somebody committed in between. REPEATABLE READ forbids it.
Phantom read — you run the same WHERE twice and the second run has extra rows. SERIALIZABLE forbids it.

Postgres defaults to READ COMMITTED: each statement sees a snapshot taken when that statement started. Two statements in the same transaction can therefore disagree, which surprises people who assumed the transaction froze the world.

Try it

Given — already loaded, nothing to run heresql

1CREATE TABLE accounts (2  id      int PRIMARY KEY,3  owner   text NOT NULL,4  balance int  NOT NULL CHECK (balance >= 0)5);6INSERT INTO accounts VALUES (1, 'Nino', 300), (2, 'Ana', 50);

A transfer that either happens twice over or not at allpostgres-16

1-- The CHECK constraint refuses to let Nino go below zero, so this2-- transfer of 1000 cannot complete. Nothing is half-applied.3SELECT owner, balance FROM accounts ORDER BY id;

What to look for

The practical warning

A transaction holds locks and a database connection for as long as it is open. Doing anything slow inside one — an HTTP call to a payment provider, a large file write, waiting on a user — holds that connection while it happens, and a pool of twenty connections drains in seconds under load. Keep transactions short and keep network calls outside them.

Try it yourself

2 visible tests · 2 hidden tests

Table accounts(id, owner, balance). A transfer is only safe to commit if it never leaves a balance negative — that is exactly what a CHECK constraint or a transaction's own validation would enforce. Return the owner of every account that is already negative, so you would know before touching a single row that something upstream broke isolation. Order by owner.

(Grading here can only run a read: the sandbox executes every submission inside a read-only transaction, so this task is a SELECT, not a transfer. What it checks — a correct filter and a correct order — is real; the framing is what a pre-transfer safety check looks like.)

Given — already loaded, nothing to run heresql

1CREATE TABLE accounts (2  id      int PRIMARY KEY,3  owner   text NOT NULL,4  balance int NOT NULL5);6INSERT INTO accounts VALUES7  (1, 'Nino', 300),8  (2, 'Ana', -50),9  (3, 'Luka', 0),10  (4, 'Mari', -1);

Loading editor…