COMMIT & ROLLBACK

Introduction

COMMIT finalizes a transaction; ROLLBACK undoes it. SAVEPOINTs let you roll back partway without aborting the entire transaction.

Understanding the topic

Core concepts to understand:

• COMMIT; — durable, locks released.
• ROLLBACK; — undo all changes since BEGIN.
• SAVEPOINT s1; ROLLBACK TO s1; — partial undo.
• Application errors should trigger ROLLBACK in finally blocks.
• Auto-commit must be off for explicit transactions.

Syntax reference

Visual workflow / architecture:

BEGIN;
  INSERT ... ;
  SAVEPOINT step1;
  UPDATE ... ; (fails)
  ROLLBACK TO step1;   -- partial rollback
  INSERT alt ... ;
COMMIT;

Real-world use

Complex order workflows use savepoints to retry sub-steps without aborting the whole order — common in payment retries.

Best practices

• Always wrap with try/commit/rollback in app code.
• Use savepoints for retry logic.
• Keep transactions short.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What does ROLLBACK undo?
• Q2. SAVEPOINT use case.
• Q3. Auto-commit mode.
• Q4. App-side error → DB-side rollback pattern.
• Q5. Long-running open transactions — risk.