Transactions, ACID & Savepoints — SQL Course

What you'll learn

Group changes atomically, understand ACID guarantees, recover with savepoints, and keep transaction boundaries intentional. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply BEGIN COMMIT ROLLBACK to a realistic data question
Apply Atomicity and durability to a realistic data question
Apply Consistency to a realistic data question
Apply SAVEPOINT to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Atomicity	All transaction changes commit or none do	Use when partial completion violates a business invariant
Durability	Committed changes survive expected failures	Understand the database and infrastructure durability configuration
Savepoint	A named position for partial rollback	Use to recover a sub-operation without abandoning the entire transaction

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the atomic transaction question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Transfer stock atomically

Both row updates and the audit record commit together; any failure rolls them all back.

transfer_stock.sql

BEGIN;
UPDATE inventory.stock SET quantity = quantity - 5
WHERE warehouse_id = 1 AND product_id = 99 AND quantity >= 5;
UPDATE inventory.stock SET quantity = quantity + 5
WHERE warehouse_id = 2 AND product_id = 99;
INSERT INTO inventory.stock_moves(product_id, from_warehouse_id, to_warehouse_id, quantity)
VALUES (99, 1, 2, 5);
COMMIT;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A transaction that runs successfully can still be logically wrong if affected-row counts and invariants are not checked.

Independent workshop

Build a review-ready atomic transaction lab against the course commerce dataset.

Your finished workshop must include:

BEGIN COMMIT ROLLBACK
Atomicity and durability
Consistency
SAVEPOINT
Error handling
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Atomicity: Use when partial completion violates a business invariant
Durability: Understand the database and infrastructure durability configuration
Savepoint: Use to recover a sub-operation without abandoning the entire transaction

Quick check

1. Which rule best applies to Atomicity?

2. Which rule best applies to Durability?

3. Which rule best applies to Savepoint?

Next: Isolation, Locks & Concurrency Control