Lesson 8.5

SQL Transactions and ROLLBACK

A transaction is an undo button. BEGIN, make changes, then COMMIT to keep them or ROLLBACK to discard them.

Sometimes the safest way to make a risky change is to give yourself an undo button. A transaction does exactly that: BEGIN opens it, you make your changes, and then you decide.

COMMIT makes everything since the BEGIN permanent; ROLLBACK discards all of it, as if it never happened. It's the ultimate "measure twice" tool. You can even undo a full-table delete.

Pattern

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

Schema · Garden ShopTable · orders6 columns · 24 rows

Table · orders

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

6 columns · 24 rows

order_id intcustomer_id intorder_date dateshipped_date datestatus textcoupon_code text

Your task

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

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer