A transaction begins with BEGIN (or START TRANSACTION), followed by any number of SQL statements, and ends with COMMIT to make every change permanent, or ROLLBACK to undo every change since the transaction started — as if none of it had ever happened. Most engines auto-commit each individual statement by default when no explicit transaction is open, so transactions are opted into deliberately for operations that must succeed or fail as a single unit.
The classic example: a bank transfer
Without a transaction, a crash between the two UPDATEs leaves money debited from one account and never credited to the other — a real, permanent inconsistency. Wrapped in a transaction, either both updates happen or neither does.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- both updates become permanent together
-- or: ROLLBACK; would undo BOTH updates, as if neither had runROLLBACK on error
Application code typically wraps a transaction in error handling: if any statement fails, ROLLBACK is called explicitly to undo everything attempted so far in that transaction.
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5;
-- suppose this next statement fails (e.g., a constraint violation):
INSERT INTO orders (product_id, customer_id) VALUES (5, 999);
ROLLBACK; -- undoes the inventory decrement too, since the order failedSavepoints: partial rollback within a transaction
A SAVEPOINT marks a point inside a transaction that a later ROLLBACK TO can return to, without discarding the entire transaction — useful when only part of a multi-step operation needs to be retried or abandoned.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE id = 999; -- id 999 doesn't exist, 0 rows affected
ROLLBACK TO before_credit; -- undoes just the failed credit attempt
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- retry with correct id
COMMIT;