Postgres Lab Starting database… Playground

PostgreSQL transactions

A transaction groups statements so they succeed or fail as one unit. The classic example: moving money takes two updates; if the second fails, the first must not stick. PostgreSQL guarantees ACID: Atomicity (all or nothing), Consistency (constraints always hold), Isolation (concurrent transactions don’t see each other’s half-done work) and Durability (once committed, it survives a crash).

BEGIN starts a transaction, COMMIT makes it permanent, ROLLBACK undoes it. Outside an explicit transaction, every single statement is its own transaction (“autocommit”). After any error inside a transaction, PostgreSQL refuses further commands until you ROLLBACK, either fully or to a SAVEPOINT.

Isolation levels: READ COMMITTED (default: each statement sees data committed before it began), REPEATABLE READ (the whole transaction sees one snapshot), and SERIALIZABLE (behaves as if transactions ran one at a time; you must be ready to retry). PostgreSQL implements these with MVCC: readers never block writers and writers never block readers. Concurrency needs two connections, so this single-tab console can’t demonstrate it, but the commands are shown below.

When two transactions update the same row, the second waits for the first. Use SELECT ... FOR UPDATE to lock rows you’re about to change.

Syntax

BEGIN [ISOLATION LEVEL REPEATABLE READ];
  ...
  SAVEPOINT sp;
  ...
  ROLLBACK TO SAVEPOINT sp;
COMMIT;   -- or ROLLBACK;

Examples

Edit the code and run it. Output is printed the way psql prints it.

18.1Set up two bank accounts

DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  id      text PRIMARY KEY,
  balance numeric(10,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES ('alice', 500), ('bob', 100);
SELECT * FROM accounts ORDER BY id;

The CHECK (balance >= 0) rule means no account can go negative. Run this first; the next examples use it.

18.2A transfer that commits

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 'alice';
UPDATE accounts SET balance = balance + 200 WHERE id = 'bob';
COMMIT;

SELECT * FROM accounts ORDER BY id;

Both updates become visible together at COMMIT. Any other connection would see either the before or the after state, never Alice’s money gone and Bob’s not yet arrived.

18.3A transfer that fails and rolls back

BEGIN;
UPDATE accounts SET balance = balance + 1000 WHERE id = 'bob';
UPDATE accounts SET balance = balance - 1000 WHERE id = 'alice';  -- breaks CHECK
COMMIT;

The second update violates the check constraint, the transaction is aborted, and Bob’s +1000 is thrown away too. Run SELECT * FROM accounts; in the playground to confirm nothing changed. (In psql you would also need ROLLBACK; to leave the failed transaction; the console here does that for you.)

18.4SAVEPOINT: undo part of a transaction

BEGIN;
UPDATE accounts SET balance = balance + 50 WHERE id = 'alice';
SAVEPOINT before_bonus;
UPDATE accounts SET balance = balance + 9999 WHERE id = 'bob';
ROLLBACK TO SAVEPOINT before_bonus;
COMMIT;

SELECT * FROM accounts ORDER BY id;

Alice’s +50 is kept; Bob’s accidental bonus is undone by rolling back only to the savepoint.

18.5Isolation level and row locks

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 'alice' FOR UPDATE;
SHOW transaction_isolation;
COMMIT;

FOR UPDATE locks Alice’s row until the transaction ends, so another session trying to change it would wait. SHOW displays a setting.