✏️ Explanatory Question

What are COMMIT, ROLLBACK, and SAVEPOINT?

👁 9 Views
📘 Detailed Answer
🟢 Easy
← Previous What is a transaction and what are the ACID properties?
💡

Answer with Explanation

60

What are COMMIT, ROLLBACK, and SAVEPOINT?

Level: Advanced — These are the core Transaction Control Language (TCL) commands.

These three commands control the outcome of a transaction — whether its changes are saved, undone, or partially rolled back.

  • COMMIT: Permanently saves all changes made during the current transaction. After a commit, changes cannot be undone.
  • ROLLBACK: Undoes all changes made since the transaction began (or since a savepoint), restoring the previous state.
  • SAVEPOINT: Creates a named checkpoint within a transaction, so you can roll back partially to that point instead of undoing everything.
Key insight: SAVEPOINT gives you fine-grained control. You can undo just part of a transaction with ROLLBACK TO savepoint_name, while keeping the earlier work intact.

TCL Commands Summary

Command Action Reversible?
COMMIT Save all changes permanently No
ROLLBACK Undo all changes N/A
SAVEPOINT Set a checkpoint N/A
ROLLBACK TO sp Undo to a savepoint Partial undo
RELEASE SAVEPOINT Delete a savepoint N/A

Quick Example — Using SAVEPOINT

START TRANSACTION;

INSERT INTO accounts (acc_id, balance) VALUES (3, 5000);
SAVEPOINT after_insert;         -- checkpoint 1

UPDATE accounts SET balance = balance - 2000 WHERE acc_id = 3;
SAVEPOINT after_update;         -- checkpoint 2

DELETE FROM accounts WHERE acc_id = 3;   -- oops, mistake!

-- Undo ONLY the delete, keep the insert & update
ROLLBACK TO after_update;

-- Save the remaining valid changes permanently
COMMIT;

Full ROLLBACK Example

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE acc_id = 999; -- invalid acc

-- Since the second update affected nothing / failed logic,
-- undo the entire transaction
ROLLBACK;   -- account 1 is restored, no money lost
Important: DDL statements (like CREATE, ALTER, DROP, TRUNCATE) cause an implicit commit — they cannot be rolled back. Only DML operations within a transaction are reversible.
Interviewer tip: The one-liner they want — "COMMIT permanently saves a transaction's changes, ROLLBACK undoes them, and SAVEPOINT sets a checkpoint so you can partially roll back to a specific point without undoing the whole transaction."
← Previous What is a transaction and what are the ACID properties?