SAVEPOINT in SQL: Syntax and Partial Rollback Example

20
0

Introduction

A transaction may contain several operations, and sometimes only the last few need to be cancelled. For example, you may want to keep a new course enrollment while undoing a separate balance transfer performed afterward.

SAVEPOINT creates a named checkpoint inside an active transaction. ROLLBACK TO SAVEPOINT undoes the changes made after that checkpoint, leaving the earlier work available to commit.

What Does a Savepoint Do?

A savepoint records a position within the current transaction. It does not permanently save any changes. Work performed before the checkpoint remains uncommitted until you run COMMIT.

Statement

Purpose

SAVEPOINT checkpoint_name;

Create a named checkpoint in the current transaction.

ROLLBACK TO SAVEPOINT checkpoint_name;

Undo changes made after that checkpoint and keep the transaction active.

The checkpoint_name is a user-defined identifier. A descriptive name such as sp_before_deduction makes the rollback boundary easier to understand.

Example: Partial Rollback in an Accounts Table

Assume a scenario where multiple updates occur on an accounts table within a single transaction. The goal is to process a valid deposit, attempt a second deduction, and then cancel only the deduction.

The accounts table starts with the following balances:

id

balance

1

1000.00

2

500.00

Run the example on one connection,

START TRANSACTION;
-- Valid operation
UPDATE accounts SET balance = balance + 500 WHERE id = 1;
-- Create a checkpoint
SAVEPOINT sp_before_deduction;
-- Provisional operation to be canceled
UPDATE accounts SET balance = balance - 200 WHERE id = 2;
-- Cancel only the second operation
ROLLBACK TO SAVEPOINT sp_before_deduction;
COMMIT;

What Happens During the Transaction?

The first update increases the balance of account 1 to 1500.00. A savepoint named sp_before_deduction is then created. The second update temporarily reduces the balance of account 2 to 300.00.

Executing ROLLBACK TO SAVEPOINT undoes the second update, restoring account 2 to its original balance. The first update remains intact but uncommitted. The transaction stays active.

Finally, COMMIT permanently saves the first update and ends the transaction.

Savepoint and Partial Rollback

Savepoint and Partial Rollback

Output After COMMIT

Querying the accounts table yields the following result:

id

balance

1

1000.00

2

500.00

The deposit to account 1 is permanently saved, while account 2 retains its original balance.

ROLLBACK, ROLLBACK TO, and RELEASE SAVEPOINT

These commands affect the transaction differently:

Command

Effect on changes

Ends the transaction?

ROLLBACK;

Undoes all uncommitted changes in the current transaction.

Yes

ROLLBACK TO SAVEPOINT checkpoint_name;

Undoes changes made after the checkpoint.

No

RELEASE SAVEPOINT checkpoint_name;

Removes the checkpoint without committing or undoing data changes.

No

COMMIT;

Saves the remaining uncommitted changes.

Yes

In this example, replacing ROLLBACK TO SAVEPOINT with a full ROLLBACK would undo the enrollment as well as the transfer.

Important Rules

  • Start a transaction first: Create the checkpoint after START TRANSACTION, and keep its operations on the same database connection.

  • Earlier work is still pending: A savepoint does not protect earlier changes from a later full ROLLBACK. Only COMMIT makes the remaining changes committed.

  • Savepoints belong to their transaction: COMMIT and a full ROLLBACK remove its savepoints. They cannot be used to undo an already committed transaction.

  • Rolling back can remove later checkpoints: If you create several savepoints, rolling back to an earlier one removes those created after it. The target savepoint remains available.

  • Keep MySQL table-definition statements outside this example: Statements such as CREATE TABLE and ALTER TABLE can implicitly commit a transaction, which removes its savepoints. Create the demo tables before starting the transaction you want to roll back.

Summary

SAVEPOINT creates a named checkpoint inside a transaction, and ROLLBACK TO SAVEPOINT undoes changes made after it without ending the transaction. Earlier changes remain uncommitted until COMMIT. A full ROLLBACK undoes all pending changes, while RELEASE SAVEPOINT removes a checkpoint without saving or undoing the data.

CS Core

Read Similar Blogs

Comments0