COMMIT, ROLLBACK & SAVEPOINT

Introduction

The previous lesson introduced transactions and showed COMMIT saving a transfer between two accounts. But what happens when something goes wrong partway through? You need a way to undo everything — or sometimes just part of it.

That is exactly what COMMIT, ROLLBACK, and SAVEPOINT give you: precise control over whether a transaction’s changes become permanent, get completely undone, or get partially undone back to a specific checkpoint.

COMMIT: Saving a Transaction

COMMIT permanently saves every change made since the transaction began with START TRANSACTION. Once committed, the changes are visible to every other connection and cannot be undone with ROLLBACK.

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

After COMMIT runs, both UPDATE statements are locked in permanently, exactly as if they had never been part of a transaction at all.

ROLLBACK: Undoing a Transaction

ROLLBACK undoes every change made since the transaction began, restoring the data to exactly how it looked before START TRANSACTION. It is your safety net whenever something inside the transaction goes wrong.

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Something went wrong -- undo everything above
ROLLBACK;

After ROLLBACK, it is as if neither UPDATE statement ever ran. Both account balances remain exactly as they were before the transaction started.

SAVEPOINT: Marking a Checkpoint

Sometimes you do not want to undo an entire transaction — only the most recent part of it. SAVEPOINT lets you mark a named checkpoint inside a transaction that you can later roll back to, without losing everything that came before it.

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

SAVEPOINT after_debit;

UPDATE accounts SET balance = balance + 500 WHERE id = 2;

At this point, after_debit marks a checkpoint right after the debit from account 1, but before the credit to account 2.

ROLLBACK TO SAVEPOINT

ROLLBACK TO SAVEPOINT undoes only the changes made after that savepoint was created, leaving everything before it intact and the transaction still open.

ROLLBACK TO SAVEPOINT after_debit;

-- The debit from account 1 is still in place.
-- The credit to account 2 has been undone.
-- The transaction is still open -- you can try again or COMMIT/ROLLBACK.

RELEASE SAVEPOINT

Once you no longer need a savepoint, you can remove it with RELEASE SAVEPOINT. This does not undo any changes — it simply forgets the checkpoint.

RELEASE SAVEPOINT after_debit;

You rarely need to call this explicitly, since all savepoints are automatically released when the transaction ends with COMMIT or ROLLBACK.

Worked Example: A Failed Transfer

Let’s continue the bank transfer scenario from the previous lesson, but this time simulate an error midway through, and recover from it using a savepoint instead of losing the whole transaction.

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10,2)
);

INSERT INTO accounts VALUES
    (1, 'Alice', 5000.00),
    (2, 'Bob', 2000.00);

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000.00 WHERE id = 1;

SAVEPOINT after_debit;

-- Simulated mistake: crediting a nonexistent account id (99)
UPDATE accounts SET balance = balance + 1000.00 WHERE id = 99;

-- Nothing matched id 99, so the credit never happened -- roll back to the checkpoint
ROLLBACK TO SAVEPOINT after_debit;

-- Retry the credit with the correct account id
UPDATE accounts SET balance = balance + 1000.00 WHERE id = 2;

COMMIT;

SELECT * FROM accounts;

+----+-------+----------+
| id | name  | balance  |
+----+-------+----------+
| 1  | Alice | 4000.00  |
| 2  | Bob   | 3000.00  |
+----+-------+----------+

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

COMMIT, ROLLBACK, and SAVEPOINT together give you fine-grained control over a transaction’s outcome — saving it permanently, undoing it entirely, or rewinding just part of it after an error.

In this lesson, you learned how COMMIT saves a transaction, how ROLLBACK undoes one, and how SAVEPOINT and ROLLBACK TO SAVEPOINT let you recover from a partial failure without starting over. Next, you will learn about Normalization — how to design tables that avoid redundant, inconsistent data in the first place.