Transactions and Savepoints

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Transactions and Savepoints
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

A transaction groups commands into one atomic unit. Commit only when every step succeeds; otherwise roll back. A savepoint lets you return to an intermediate point without abandoning the whole transaction. Long-running transactions can harm locking, dead-row cleanup, and connection availability.

Connect it to a real scenario

Create an enrollment and its initial progress rows inside one transaction. If duplicate enrollment fails, no partial progress rows should remain. A database rollback cannot undo an external email or API call, so real systems may need outbox and idempotency patterns.

BEGIN → work → COMMIT
ROLLBACK on failure
External side effects live outside the database transaction

Try the working example

sql

CREATE TABLE IF NOT EXISTS app.lesson_progress (
  enrollment_id bigint NOT NULL REFERENCES app.enrollments(enrollment_id) ON DELETE CASCADE,
  lesson_id bigint NOT NULL REFERENCES app.lessons(lesson_id) ON DELETE CASCADE,
  completed boolean NOT NULL DEFAULT false,
  completed_at timestamptz,
  PRIMARY KEY (enrollment_id, lesson_id)
);

BEGIN;

WITH new_enrollment AS (
  INSERT INTO app.enrollments (user_id, tutorial_id)
  VALUES (1, 1)
  RETURNING enrollment_id, tutorial_id
)
INSERT INTO app.lesson_progress (enrollment_id, lesson_id)
SELECT e.enrollment_id, l.lesson_id
FROM new_enrollment e
JOIN app.lessons l USING (tutorial_id);

COMMIT;
-- On any unrecoverable error: ROLLBACK;

You should see

Enrollment and progress setup either both succeed or both disappear on rollback.

5-minute try-it

Write a transaction that transfers a balance between two rows, inject an error midway, and verify rollback.

One important caution

Opening a transaction before waiting for user input or a network call needlessly extends lock time.

PostgreSQL — Transactions — PostgreSQL Global Development Group

Easy traps

Opening a transaction before waiting for user input or a network call needlessly extends lock time.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Write a transaction that transfers a balance between two rows, inject an error midway, and verify rollback.

You'll know it worked when: Enrollment and progress setup either both succeed or both disappear on rollback.