Exercises

Objectives

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

• Perform a real account transfer as a transaction
• Roll back a transaction on purpose
• Use a savepoint to recover from a partial failure without losing earlier work

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    owner VARCHAR(100) NOT NULL,
    balance NUMERIC(10,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (owner, balance) VALUES ('Maya Fischer', 800.00), ('Sam Whitfield', 150.00);

Exercise 1: A Successful Transfer

Transfer 200 from Maya Fischer to Sam Whitfield, as one transaction: BEGIN, subtract 200 from Maya’s balance, add 200 to Sam’s balance, COMMIT. Expected result: Maya 600.00, Sam 350.00.

Exercise 2: A Voluntary ROLLBACK

a) Start a transaction and subtract 500 from Maya Fischer’s balance, then query accounts to confirm the change is visible mid-transaction. Expected result: Maya shows 100.00.

b) Run ROLLBACK instead of COMMIT, then query accounts again. Expected result: Maya is back to 600.00, unchanged from Exercise 1.

Exercise 3: A Failed Statement Forces a Full Rollback

a) Start a transaction, subtract 100 from Maya Fischer’s balance (this succeeds), then attempt to subtract 99999 from Sam Whitfield’s balance (this violates the CHECK constraint, since Sam only has 350.00). Record the exact error.

b) Try running a SELECT on accounts immediately after the failed statement, before rolling back. Record the exact error.

c) Run ROLLBACK, then query accounts. Expected result: Maya is still 600.00, the earlier, otherwise-valid update was undone along with the failed one.

Exercise 4: Recovering with SAVEPOINT

a) Start a transaction, subtract 100 from Maya Fischer’s balance, then create a savepoint named after_maya_update.

b) Attempt to subtract 99999 from Sam Whitfield’s balance (this fails the same way as Exercise 3).

c) Run ROLLBACK TO SAVEPOINT after_maya_update, then COMMIT. Expected result: Maya’s update is preserved (500.00), Sam’s balance is untouched (350.00), unlike Exercise 3, the failed statement didn’t cost the earlier valid work.

d) Explain, in your own words, the exact difference in outcome between Exercise 3 (plain ROLLBACK) and Exercise 4 (ROLLBACK TO SAVEPOINT), given that both started from an identical failed statement.

Recap

This module covered every core transaction tool: the ACID properties transactions guarantee, BEGIN and COMMIT for grouping and finalizing changes, ROLLBACK for undoing a transaction (voluntarily or after a failure), and SAVEPOINT for protecting earlier work from a later failure. Together, these are what make a multi-step change like an account transfer actually safe.

Next module: the capstone project, combining every module in this course into one complete, production-style database.