What do COMMIT, ROLLBACK and SAVEPOINT do?
SHORT ANSWER
COMMIT makes a transaction's changes permanent. ROLLBACK undoes everything since the transaction began. SAVEPOINT marks a point inside a transaction so you can roll back only part of it.
Explain it simply
Editing a document: COMMIT is pressing Save, ROLLBACK is closing without saving, and SAVEPOINT is a checkpoint you can return to without losing the earlier edits.
Example
sql
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 500 WHERE id = 99;
-- oops, wrong account
ROLLBACK TO after_debit; -- undoes only the second update
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- debit and the correct credit become permanent- Before COMMIT, other sessions do not see these changes (depending on isolation level).
- After COMMIT, the changes survive a crash because of durability.
- After a full ROLLBACK, the database is as it was at BEGIN.
START TRANSACTIONin MySQL andBEGIN TRANSACTIONin SQL Server start a transaction.
DDL such as CREATE or DROP commits implicitly in MySQL and Oracle, so it cannot be rolled back there. PostgreSQL supports transactional DDL.
What to say in the interview
"COMMIT saves, ROLLBACK undoes the whole transaction, and SAVEPOINT allows a partial rollback. Together they let a multi-step operation like a money transfer succeed or fail as one unit."
Common mistakes
- Forgetting to COMMIT and leaving locks open for other sessions.