Lesson 8.5

SQL Transactions and ROLLBACK

A transaction lets you group changes and either commit them together or roll them back before they are committed.

Sometimes the safest way to make a risky change is to keep it reversible until you have checked the result. A transaction gives you that boundary: BEGIN opens it, you make your changes, and then you decide.

COMMIT makes the transaction's changes permanent; ROLLBACK discards the transaction's uncommitted changes. In DuckDB, that includes the full-table delete used in this exercise. Transaction and recovery behavior varies across database systems and statement types, so don't treat rollback as a substitute for backups or change controls.

Pattern

begin;
update products set price = 0;   -- oops
rollback;                        -- undo it all
-- prices are unchanged

Syntax reference

Need syntax details? See the safe UPDATE and DELETE reference

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Prove that ROLLBACK is a real safety net. In one script: BEGIN a transaction, delete every order, then ROLLBACK. Finally, SELECT count(*) of the orders as order_count. It should still be 24, because the rollback undid the delete.