SQL › All or Nothing

Transactions: BEGIN, COMMIT and ROLLBACK

A transaction groups statements so they take effect together or not at all. Money leaving one wallet must arrive in the other; an order can't be cancelled without its payment.

Syntax

Order of execution: BEGIN opens the transaction → each statement runs and sees the changes made before it → COMMIT keeps every change at once, or ROLLBACK undoes them all. Other users see the changes only after COMMIT.

Code
BEGIN;
UPDATE ...;
INSERT ...;
COMMIT;            -- or ROLLBACK;

SQL Server writes BEGIN TRANSACTION. Oracle has no BEGIN: a transaction starts with the first change and lasts until COMMIT or ROLLBACK, so nothing is kept until you commit.

In the lab, every run is undone when it ends. COMMIT still works as taught inside the run, but nothing is kept afterwards.

Examples

Keep changes together

Two price changes that belong together are committed as one.

Code
BEGIN;
UPDATE products SET price = price * 1.10 WHERE category_id = 7;     -- Grocery 10% up
UPDATE products SET price = price * 0.90 WHERE category_id = 6;     -- Beauty 10% down
COMMIT;
SELECT product_name, price FROM products WHERE category_id IN (6, 7) ORDER BY product_id;

Undo with ROLLBACK

Everything since BEGIN is undone, however many statements ran.

Code
BEGIN;
DELETE FROM reviews WHERE rating = 5;
UPDATE products SET rating = NULL;
ROLLBACK;
SELECT COUNT(*) AS reviews FROM reviews;      -- every review is back

A transaction sees its own changes

Inside the transaction, later statements already see the earlier changes.

Code
BEGIN;
UPDATE customers SET city = 'Pune' WHERE customer_id = 3;
SELECT customer_id, city FROM customers WHERE city = 'Pune' ORDER BY customer_id;   -- customer 3 is included
COMMIT;

When a statement fails

In PostgreSQL, an error spoils the whole transaction: it refuses every further statement until ROLLBACK, and COMMIT then undoes everything too. MySQL, SQL Server and Oracle usually undo only the failed statement and keep the transaction open. In the lab, the run stops at the error.

Code
BEGIN;
UPDATE accounts SET balance = balance + 20000 WHERE customer_id = 2;
-- Fails: a balance can't go below 0, so neither change is kept
UPDATE accounts SET balance = balance - 20000 WHERE customer_id = 1;
COMMIT;

Without BEGIN

Each statement is kept on its own, so there's nothing left to roll back.

Code
UPDATE customers SET loyalty_points = loyalty_points + 50 WHERE customer_id = 3;
-- Fails in the lab (PostgreSQL only warns): no transaction is open, and the UPDATE is already kept
ROLLBACK;

Remember

• Changes between BEGIN and COMMIT are kept together or not at all.
• ROLLBACK undoes everything since BEGIN, not just the last statement.
• In PostgreSQL, after an error inside a transaction, nothing more runs until ROLLBACK.
• Without BEGIN, every statement is kept as soon as it runs (except in Oracle, which waits for COMMIT).

Practice

1. Move Money Between Wallets (Easy)
2. Undo a Wrong Delete (Medium)
3. Cancel an Order Completely (Hard)