START TRANSACTION / COMMIT / ROLLBACK group changes atomically—InnoDB is MVCC with row locks.
Basic transaction
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Practice: Run read-only EXPLAIN on practice tables where possible.
Rollback
START TRANSACTION;
DELETE FROM staging_orders WHERE created_at < NOW() - INTERVAL 7 DAY;
-- ROLLBACK; -- if row count surprising
COMMIT;Autocommit
Default autocommit=1 wraps each statement—explicit transactions for multi-step business rules.
Important interview questions and answers
- Q: MyISAM transactions?
A: No—another reason to use InnoDB. - Q: Deadlock?
A: InnoDB detects and rolls back one transaction—retry in app code.
Self-check
- When COMMIT vs autocommit?
- What happens on deadlock?
Tip: Wrap multi-step money moves in explicit transactions.
Interview prep
- COMMIT?
Makes transaction changes durable.
- Deadlock?
InnoDB rolls back one txn—retry app logic.