Transactions & COMMIT / ROLLBACK
Advanced⏱️ 12 mins read
What You'll Learn
A transaction is a unit of work that either completes fully or not at all. Use BEGIN/START TRANSACTION to start, COMMIT to save permanently, ROLLBACK to undo all changes. SAVEPOINT creates a checkpoint for partial rollbacks. Transactions are essential for any multi-step operation (bank transfers, inventory updates, order placement).
Syntax
BEGIN TRANSACTION;
-- SQL statements
COMMIT;
-- or
ROLLBACK;
SAVEPOINT sp_name;
ROLLBACK TO sp_name;Example
-- Bank transfer — must be atomic
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;
-- Both succeed → save permanently
COMMIT;
-- If any step failed → undo everything
-- ROLLBACK;
-- Savepoint for partial rollback
SAVEPOINT before_update;
-- If only this part needs undoing:
ROLLBACK TO before_update;Common Mistakes
Forgetting to COMMIT — changes sit in an open transaction and lock rows, blocking other queries. Long-running transactions cause lock contention. Always handle COMMIT/ROLLBACK in application error handling.
Interview Tips
Explain isolation levels: READ UNCOMMITTED (dirty reads), READ COMMITTED (default in most DBs), REPEATABLE READ (MySQL default), SERIALIZABLE (strictest). Higher isolation = more consistent but more locking and lower throughput.
Practice
Write a transaction that places an order: inserts an order row, deducts inventory count, and inserts a payment record. Roll back the entire transaction if any step fails.