What are COMMIT, ROLLBACK, and SAVEPOINT? | Interview Question

Q: What are COMMIT, ROLLBACK, and SAVEPOINT?

**Target Audience:** Beginners who have seen `BEGIN` and `COMMIT` and want to know exactly what each command does, and candidates preparing for the savepoint and error-recovery follow-ups. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. `SAVEPOINT`, `ROLLBACK TO SAVEPOINT` and `RELEASE SAVEPOINT` are standard SQL and also exist in MySQL InnoDB; SQL Server spells it `SAVE TRANSACTION` / `ROLLBACK TRANSACTION name`. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You are writing a long essay in a word processor. Three tools save you: - **Save** writes the document to disk.

The problem

Question presented to candidate: "Walk me through COMMIT, ROLLBACK and SAVEPOINT. What exactly does each one do, and when would you use a savepoint instead of rolling back the whole transaction — for example, importing a batch of customers where a few rows are duplicates?"

What a strong answer should cover:

COMMIT makes every change of the transaction permanent and visible to others; ROLLBACK discards all of them and ends the transaction
SAVEPOINT name marks a point inside a transaction; ROLLBACK TO SAVEPOINT name undoes only the work after it and keeps the transaction (and the savepoint) alive; RELEASE SAVEPOINT name forgets the mark but keeps the work
Nothing is permanent until the outer COMMIT: rolling back the outer transaction also discards released savepoints
PostgreSQL aborts the whole transaction after any error ("current transaction is aborted"); a savepoint before the risky statement is how you recover and continue; PL/pgSQL EXCEPTION blocks and many ORMs' "nested transactions" are savepoints under the hood
Engine differences: SQLite allows SAVEPOINT outside BEGIN (it starts a transaction), PostgreSQL rejects it; COMMIT with no open transaction is an error in SQLite and a warning in PostgreSQL

Code / implementation expected: Yes — a transaction with a savepoint that rolls back one step and commits the rest.

Schema used by the examples:

transactions

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have seen BEGIN and COMMIT and want to know exactly what each command does, and candidates preparing for the savepoint and error-recovery follow-ups. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. SAVEPOINT, ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT are standard SQL and also exist in MySQL InnoDB; SQL Server spells it SAVE TRANSACTION / ROLLBACK TRANSACTION name.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You are writi

The solution is hidden so you can try first

13 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLCOMMIT keeps a transaction, ROLLBACK throws one away

SQLSAVEPOINT + ROLLBACK TO undoes one step, the rest is committed

SQLSQLite: nested savepoints, rolling back to an outer one, RELEASE of the outermost commits

PostgreSQL: a PL/pgSQL EXCEPTION block (an implicit savepoint) skips duplicate rows in an import

DO $$
DECLARE
  r record;
BEGIN
  FOR r IN
    SELECT * FROM (VALUES
      (4, 'Dara',    'dara@example.com'),
      (5, 'Alice B', 'alice@example.com'),
      (6, 'Farid',   'farid@example.com')
    ) AS v(id, name, email)
    ORDER BY id
  LOOP
    BEGIN
      INSERT INTO customers (id, name, email) VALUES (r.id, r.name, r.email);
    EXCEPTION WHEN unique_violation THEN
      RAISE NOTICE 'skipped %', r.email;
    END;
  END LOOP;
END
$$;
SELECT id, name, email FROM customers ORDER BY id;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.