Commit & Rollback

Transaction control commands that decide whether to save or undo changes made during a transaction.

COMMIT

Make all changes permanent. Once committed, changes are saved to disk, visible to other transactions, and cannot be undone.

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- Both changes are now permanent

ROLLBACK

Undo all changes since the last commit. If something goes wrong, ROLLBACK brings the DB back to the last stable state.

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
  -- Something went wrong!
ROLLBACK; -- All changes undone

How It Works Internally

• BEGIN → changes written to UNDO LOG (temp buffer)
• COMMIT → changes written to disk permanently; undo log cleared
• ROLLBACK → undo log is used to reverse all changes; DB restored to pre-transaction state

SAVEPOINT — Partial Rollback

Roll back to a specific point, not the entire transaction.

BEGIN TRANSACTION;
  INSERT INTO orders VALUES (101, 'Laptop');
  SAVEPOINT sp1; -- checkpoint saved
  INSERT INTO orders VALUES (102, 'Phone');
  -- Phone insert went wrong
  ROLLBACK TO SAVEPOINT sp1; -- only undoes Phone insert
COMMIT; -- Only Laptop insert is saved

Command Summary

Command	Action	Reversible?
BEGIN	Start a transaction	—
COMMIT	Save all changes permanently	No
ROLLBACK	Undo all changes	Yes (goes back to last commit)
SAVEPOINT	Set a partial checkpoint	—
ROLLBACK TO SAVEPOINT	Undo only up to checkpoint	Yes

Interview Tip: If asked "what happens if a system crashes mid-transaction?" → The DB uses the undo log / WAL (Write Ahead Log) to automatically rollback any uncommitted transaction on recovery.