ROLLBACK in SQL: Syntax, Example and Limitations

3
0

Introduction

Suppose a student’s status and city are updated incorrectly and the changes should not be saved. If both updates are still part of an open transaction, ROLLBACK can cancel them together.

This is useful when a change is incorrect or a related operation cannot be completed successfully.

How ROLLBACK Works

ROLLBACK ends the current transaction and undoes its uncommitted data changes. A transaction groups related SQL statements into one unit. The changes can be finalized with COMMIT or canceled with ROLLBACK.

Syntax

ROLLBACK;

ROLLBACK affects the work in the current transaction. It does not reverse changes already committed by other transactions. The affected tables must support transactions.

Example: Undoing Two Student Updates

Assume the students table contains these already committed rows. Each example below starts from this same data.

student_id

full_name

status

city

1

Ayaan

ACTIVE

Pune

2

Meera

ACTIVE

Delhi

The following operation changes Ayaan’s status to BANNED and his city to Bengaluru, inspects the pending changes, and then cancels both updates. Run the entire example on the same database connection.

START TRANSACTION;
UPDATE students
SET status = 'BANNED'
WHERE student_id = 1;
UPDATE students
SET city = 'Bengaluru'
WHERE student_id = 1;
-- View the pending changes in this transaction.
SELECT student_id, full_name, status, city
FROM students
ORDER BY student_id;
ROLLBACK;
-- View the values after both updates are undone.
SELECT student_id, full_name, status, city
FROM students
ORDER BY student_id;

Before ROLLBACK

The first SELECT runs inside the transaction and returns:

student_id

full_name

status

city

1

Ayaan

BANNED

Bengaluru

2

Meera

ACTIVE

Delhi

The current transaction can see its own updates. Ayaan’s new status and city are still uncommitted.

After ROLLBACK

The second SELECT returns:

student_id

full_name

status

city

1

Ayaan

ACTIVE

Pune

2

Meera

ACTIVE

Delhi

Both updates have been undone: Ayaan’s status is ACTIVE again, and his city is Pune. Meera’s row remains unchanged because neither UPDATE selected it.

ROLLBACK cancels all the uncommitted changes in this transaction, including both updates.

Rules to Remember

  • Start the transaction before making changes: MySQL enables autocommit by default. Outside an explicit transaction, a successful UPDATE is committed automatically. Running ROLLBACK afterward cannot undo it.

  • Committed changes cannot be rolled back: Once COMMIT completes, a later ROLLBACK cannot reverse that transaction. Correcting those values requires a new operation.

  • Keep the same connection: A transaction belongs to one database connection. With a connection pool, retain one acquired connection until COMMIT or ROLLBACK finishes.

  • MySQL table-definition commands can end the transaction: Commands such as ALTER TABLE and TRUNCATE TABLE cause an implicit commit, which commits the active transaction automatically. Do not expect a later ROLLBACK to undo those commands or the earlier changes they committed.

  • A MySQL error may undo only one statement: Some errors leave earlier changes in the transaction pending. If the whole operation must be abandoned, explicitly issue ROLLBACK instead of assuming every error cancels the entire transaction.

Summary

ROLLBACK ends the current transaction and undoes its uncommitted changes. In the student example, it reverses both updates and restores Ayaan’s original status and city. Start the transaction before making changes, keep its statements on the same connection, and use transactional tables in MySQL. Changes that have already been committed cannot be undone by a later ROLLBACK.

CS Core

Read Similar Blogs

Comments0