Part 122: Transaction Control Commands: BEGIN, COMMIT & ROLLBACK

Transaction Control Language (TCL) defines the boundaries of atomic work.

1. The Classic Bank Transfer

BEGIN; -- Or START TRANSACTION;

-- 1. Deduct $200 from Sender:
UPDATE accounts 
SET balance = balance - 200.00 
WHERE id = 101 AND balance >= 200.00;

-- 2. Credit $200 to Receiver:
UPDATE accounts 
SET balance = balance + 200.00 
WHERE id = 202;

-- 3. Permanently save changes:
COMMIT;

If any statement errors out, abort everything:

ROLLBACK;

Key Takeaways

• Wrap multi-statement mutations in BEGIN and COMMIT.
• Use ROLLBACK to revert in catch blocks.

In Part 123, we explore Partial Rollbacks with SAVEPOINT!