Transaction Control Language (TCL)

Overview

This lesson covers how PostgreSQL handles transactions. Students learn the ACID properties, how to explicitly control transaction boundaries, use savepoints for partial rollback, and understand isolation levels and their practical implications.

Lesson Plan

Time	Activity
0–10 min	Intro: what is a transaction? Why do we need them?
10–25 min	BEGIN, COMMIT, ROLLBACK — basic transaction control
25–40 min	ACID properties: Atomicity, Consistency, Isolation, Durability
40–55 min	SAVEPOINT and ROLLBACK TO SAVEPOINT
55–70 min	Isolation levels: Read Committed, Repeatable Read, Serializable
70–80 min	Wrap-up: real-world transaction patterns

Key Concepts

BEGIN starts a transaction block; COMMIT saves, ROLLBACK undoes
Atomicity: all-or-nothing — partial failure rolls back everything
Consistency: DB moves from one valid state to another
Isolation: transactions don't interfere with each other
Durability: committed data survives crashes
SAVEPOINT sp1 and ROLLBACK TO sp1 for nested checkpoints
Default isolation in PostgreSQL: Read Committed

✏️Practice

Short in-class exercises:

Demonstrate a transaction that transfers money between two accounts — rollback on error.
Use a savepoint to partially undo part of a multi-step insert operation.
Observe the effect of different isolation levels on concurrent reads.