What a Transaction Is

~12 min · transactions, concepts

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

A unit of work the database treats as one

A transaction is a sequence of statements the database treats as a single all-or-nothing unit. Either every statement commits, or none of them do. That is the four-letter promise of ACID, made concrete: BEGIN opens the transaction, COMMIT makes its effects permanent, ROLLBACK throws everything away as if it never happened.

The autocommit default

Without BEGIN, every statement runs in its own implicit transaction (autocommit). For single-statement work this is fine. For anything that touches more than one row across more than one statement and needs the changes to land together, you must wrap them in BEGIN ... COMMIT.

Savepoints — transactions inside transactions

A savepoint is a marker inside an open transaction you can roll back to without losing earlier work. Useful for long batch jobs where one failure shouldn't undo everything that succeeded so far.

Code

The basic shape·sql

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
-- Both updates land together, or neither does.

Rolling back·sql

BEGIN;
DELETE FROM users WHERE last_login < now() - INTERVAL '5 years';
-- ... look at the result; something looks wrong ...
ROLLBACK;
-- Database is unchanged. The DELETE never happened.

Savepoint·sql

BEGIN;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT after_order;
INSERT INTO order_items (...) VALUES (...);
-- If this fails, undo only the items but keep the order
ROLLBACK TO SAVEPOINT after_order;
-- Try a different approach
INSERT INTO order_items (...) VALUES (...);
COMMIT;

External links

PostgreSQL Transactions

Exercise

In psql, do a multi-step UPDATE inside BEGIN/COMMIT. Then repeat the same with ROLLBACK. Confirm the final state is what you expect in each case.