Module 23 — Transactions

1. BEGIN, COMMIT, ROLLBACK

What it is: BEGIN starts a transaction, COMMIT saves all changes permanently, and ROLLBACK undoes all changes since BEGIN. Within a transaction, changes are tentative until committed.

Why we use it: Transactions provide a safety net — you can test changes before committing. If something goes wrong, ROLLBACK restores the database to its previous state. This prevents data corruption from partial failures.

When we use it: Every time you run multiple related statements that should succeed or fail together — transfers, multi-step updates, data migrations, or any operation where partial failure is unacceptable.

BEGIN;  -- Start the transaction
 
UPDATE users SET role = 'admin' WHERE id = 1;
DELETE FROM temp_data WHERE id > 100;
 
-- Check the results
SELECT * FROM users WHERE id = 1;
 
-- If everything looks good:
COMMIT;  -- Save changes permanently
 
-- If something went wrong:
ROLLBACK;  -- Undo everything

Transaction Lifecycle

2. Why Transactions Matter

What it is: Transactions ensure atomicity — all statements in a transaction succeed or fail as a unit. Without transactions, a failure halfway through leaves the database in an inconsistent state (e.g., money deducted from one account but not added to another).

Why we use it: In production, failures happen — network issues, server crashes, constraint violations. Transactions ensure your data stays consistent even when things go wrong.

When we use it: Every time multiple statements need to be atomic — financial transfers, order processing, multi-table updates, or any operation where partial completion would corrupt data.

The Problem

Without transactions, a failure halfway through leaves your database in an inconsistent state.

-- Without transactions: money could be lost!
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Server crashes here — money deducted from account 1 but never added to account 2!
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

The Solution

-- With transactions: both succeed or both fail
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

3. SAVEPOINT — Partial Rollback

What it is: SAVEPOINT creates a checkpoint within a transaction. You can ROLLBACK TO a savepoint to undo changes after that point, while keeping changes before it. It's like an "undo point" within a transaction.

Why we use it: Sometimes you want to try something risky within a transaction without risking the entire operation. Savepoints let you experiment with a fallback — "try to insert Bob, but if it fails, keep Alice".

When we use it: When processing multiple items where some might fail, when implementing retry logic, or when you need to undo part of a transaction without losing all progress.

BEGIN;
 
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
SAVEPOINT after_alice;
 
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');
-- Oops, something wrong with Bob
ROLLBACK TO after_alice;
 
-- Alice is still saved (within the transaction)
INSERT INTO users (username, email) 

4. ACID Properties

What it is: ACID is an acronym for the four properties that guarantee database transactions are reliable: Atomicity (all or nothing), Consistency (data always valid), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes).

Why we use it: ACID is what makes PostgreSQL trustworthy. Without these guarantees, you couldn't rely on your data being correct after a crash, a power outage, or concurrent access.

When we use it: Every time you use a transaction, PostgreSQL enforces ACID automatically. Understanding these properties helps you design reliable systems and debug issues.

5. Auto-Commit

What it is: Auto-commit is the default behavior where each SQL statement is automatically committed as soon as it executes — no explicit BEGIN/COMMIT needed. In psql, this happens for every statement outside a transaction.

Why we use it: Auto-commit is convenient for simple queries, but dangerous for multi-statement operations. That's why you should always wrap UPDATE and DELETE in explicit transactions.

When we use it: Auto-commit is the default for simple queries. Use explicit transactions (BEGIN/COMMIT) for any multi-statement operation where atomicity matters.

-- This is immediately permanent (auto-commit):
DELETE FROM users WHERE id = 5;
 
-- This can be undone:
BEGIN;
DELETE FROM users WHERE id = 5;
ROLLBACK;  -- User is restored!

Common Mistakes to Avoid

1. Forgetting COMMIT — Changes stay in limbo until committed
2. Forgetting ROLLBACK on error — Locks stay held, blocking other transactions
3. Long-running transactions — Hold locks and block other users
4. Not using transactions for multi-statement operations — Risk of partial failures

Practice Exercise

1. Start a transaction, update a course's price, verify with SELECT, then ROLLBACK
2. Start a transaction, insert 3 courses, use SAVEPOINT after the 2nd, rollback to savepoint, then COMMIT
3. Verify only the first 2 courses were saved
4. Start a transaction, delete all courses, verify they're gone, then ROLLBACK — verify they're back