Transactions in Practice

Wrap multiple writes in a transaction — either everything commits or nothing does

Imagine transferring 50 kegs from Brewery A's warehouse to Brewery B's. You need to deduct 50 from A and add 50 to B. If the deduction succeeds but the addition fails, the inventory is wrong — 50 kegs vanished. A transaction prevents this: either both statements commit together, or ROLLBACK undoes both.\n\nPostgreSQL is always in a transaction — even a bare INSERT is wrapped in an implicit single-statement transaction. BEGIN starts an explicit multi-statement transaction.\n\nSAVEPOINT lets you mark a point inside a transaction. ROLLBACK TO SAVEPOINT undoes only the statements after that marker, without aborting the whole transaction.

The Basic Transaction Pattern

BEGIN;

UPDATE breweries SET city = 'Brno'
WHERE name = 'Golden Brewery';

-- Check the result before committing
SELECT name, city FROM breweries WHERE name = 'Golden Brewery';

COMMIT;  -- makes the change permanent
-- or ROLLBACK;  -- erases everything since BEGIN

The Inventory Transfer Pattern

BEGIN;

-- Deduct from source
UPDATE breweries SET founded_year = founded_year - 1
WHERE name = 'Golden Brewery';

-- Add to destination
UPDATE breweries SET founded_year = founded_year + 1
WHERE name = 'Amber Brewery';

-- Both succeed → commit both, or fail → rollback both
COMMIT;

SAVEPOINT — Partial Rollback

BEGIN;

INSERT INTO styles (name, abv_range, ibu_range)
VALUES ('Test Style A', numrange(5.0, 7.0, '[)'), numrange(30, 60, '[)'));

SAVEPOINT after_first_insert;

INSERT INTO styles (name, abv_range, ibu_range)
VALUES ('Test Style B', numrange(4.0, 6.0, '[)'), numrange(20, 40, '[)'));

-- Undo only the second insert, keep the first
ROLLBACK TO SAVEPOINT after_first_insert;

COMMIT;  -- only 'Test Style A' is saved

ACID Properties

Atomicity — all statements in a transaction succeed or all are rolled back
Consistency — the database moves from one valid state to another
Isolation — concurrent transactions do not interfere with each other
Durability — committed data survives crashes

Transaction

A group of SQL statements treated as a single atomic unit. Either all statements commit (COMMIT) and become permanently visible, or all are rolled back (ROLLBACK) and leave no trace. PostgreSQL wraps every individual statement in an implicit single-statement transaction if no explicit BEGIN is present. BEGIN starts an explicit multi-statement transaction.

SAVEPOINT

A named marker inside an active transaction. ROLLBACK TO SAVEPOINT undoes all statements after that marker without aborting the entire transaction. RELEASE SAVEPOINT removes the marker (the work remains). Useful for retrying a sub-operation within a larger transaction without losing prior work.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user. Transaction exercises use the beers and warehouses tables.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

👀 Check Warehouse Inventory

Look at the warehouses table to see the starting keg counts before any transfers.

SELECT brewery_name, keg_count FROM warehouses ORDER BY brewery_name;

brewery_name | keg_count ----------------+----------- Amber Brewery | 150 Golden Brewery | 200 (2 rows)

🔐 BEGIN and ROLLBACK — Undo a Change

Start a transaction, update a keg count, verify the change within your session, then ROLLBACK to undo it. Verify the count returned to its original value.

BEGIN;

UPDATE warehouses SET keg_count = keg_count - 50 WHERE brewery_name = 'Golden Brewery';

SELECT brewery_name, keg_count FROM warehouses;

ROLLBACK;

SELECT brewery_name, keg_count FROM warehouses;

✅ Atomic Transfer — BEGIN and COMMIT

Transfer 50 kegs from Golden Brewery to Amber Brewery in a single transaction. Deduct 50 from Golden, add 50 to Amber. Verify both changes, then COMMIT.

BEGIN;

UPDATE warehouses SET keg_count = keg_count - 50 WHERE brewery_name = 'Golden Brewery';

UPDATE warehouses SET keg_count = keg_count + 50 WHERE brewery_name = 'Amber Brewery';

SELECT brewery_name, keg_count FROM warehouses ORDER BY brewery_name;

COMMIT;

brewery_name | keg_count ----------------+----------- Amber Brewery | 200 Golden Brewery | 150 (2 rows) COMMIT

💾 SAVEPOINT — Partial Rollback

Inside a transaction, insert a test style, set a SAVEPOINT, insert a second test style, then ROLLBACK TO SAVEPOINT to undo only the second insert. COMMIT to save only the first style, then verify and clean up.

BEGIN;

INSERT INTO styles (name, abv_range, ibu_range) VALUES ('TX Style A', numrange(5.0, 7.0, '[)'), numrange(30, 60, '[)')) RETURNING name;

SAVEPOINT after_a;

INSERT INTO styles (name, abv_range, ibu_range) VALUES ('TX Style B', numrange(4.0, 6.0, '[)'), numrange(20, 40, '[)')) RETURNING name;

ROLLBACK TO SAVEPOINT after_a;

COMMIT;

SELECT name FROM styles WHERE name LIKE 'TX Style%';

DELETE FROM styles WHERE name LIKE 'TX Style%';

name ------------ TX Style A (1 row)

Lab complete! You can now write atomic multi-statement operations:\n\n\n BEGIN : ✅ starts an explicit transaction\n COMMIT : ✅ makes all changes permanent\n ROLLBACK : ✅ undoes all changes since BEGIN\n SAVEPOINT name : ✅ marks a partial-rollback point\n ROLLBACK TO sp : ✅ undoes only changes after the savepoint\n

Enable JavaScript to run the live terminal and track your progress.