A transaction groups statements into one atomic unit—ACID guarantees prevent partial updates when transferring money, reserving inventory, or syncing related rows.
BEGIN / COMMIT
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Practice: Advanced features vary by engine—SQLite 3.25+ supports window functions; test your version with a simple query.
SQLite defaults to autocommit; explicit BEGIN starts a transaction block.
ROLLBACK
BEGIN;
DELETE FROM orders WHERE id = 999;
-- Oops wrong id
ROLLBACK;ROLLBACK undoes changes since BEGIN. Production apps use transactions around related DML in Django atomic() blocks and PDO transactions in PHP.
ACID recap
- Atomicity — all or nothing
- Consistency — constraints hold after commit
- Isolation — concurrent sessions see controlled visibility
- Durability — committed data survives crashes
Important interview questions and answers
- Q: Why transactions for transfers?
A: Debit and credit must succeed together—partial update corrupts balances. - Q: Autocommit?
A: Each statement commits alone unless wrapped in explicit transaction.
Self-check
- Which keyword undoes a transaction?
- Name two ACID properties.
Tip: Wrap related DML in transactions—Django atomic() and PDO transactions do the same.
Interview prep
- ACID atomicity?
All statements commit together or none do.
- ROLLBACK?
Undoes changes since BEGIN.