SQL · Pattern 14 — Database Design & Performance
Transactions (COMMIT / ROLLBACK)
A transaction is a sequence of SQL statements executed as a single unit.
Source data — Pattern 14: Database Design & Performance
All queries run against the shared employees table (see schema.sql). This pattern introduces performance optimization and transaction management. Topic 85 introduces an accounts table to demonstrate COMMIT and ROLLBACK using a money transfer transaction.
What it does
A transaction is a sequence of SQL statements executed as a single unit. COMMIT permanently saves changes, while ROLLBACK undoes them if an error occurs.
Problem
Transfer salary between accounts using a transaction.
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 101;
UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 102;
COMMIT;
-- Use ROLLBACK instead of COMMIT if an error occurs.
Result
