SQL ROLLBACK

What does SQL ROLLBACK do?

ROLLBACK cancels the current transaction or reverts to a savepoint, undoing all changes made since the transaction began or since the savepoint was set.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ROLLBACK: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, IBM Db2, Snowflake, Redshift

SQL ROLLBACK Full Explanation

ROLLBACK is a transaction control statement that reverses data modifications performed during the current transaction. If issued without options, it undoes every INSERT, UPDATE, DELETE, or DDL statement executed since the last BEGIN/START TRANSACTION or implicit transaction start. The database restores rows, indexes, and constraints to their prior state and releases any locks acquired during the rolled-back operations.You can also roll back to a specific savepoint if your database supports SAVEPOINT. In that case, only the work done after the savepoint is undone, and the transaction remains active, letting you continue processing or eventually COMMIT.ROLLBACK guarantees atomicity and consistency, two properties of ACID. It is often used in error-handling routines, long-running scripts, and applications where data integrity must be preserved if any step fails.Caveats:- ROLLBACK has no effect outside an active transaction.- Some DDL statements auto-commit in certain dialects, making them non-reversible.- Long transactions may hold locks until rollback completes, impacting concurrency.

SQL ROLLBACK Syntax

ROLLBACK;

-- Optional variants
ROLLBACK WORK;
ROLLBACK TRANSACTION;

-- Roll back to a savepoint
ROLLBACK TO SAVEPOINT savepoint_name;

SQL ROLLBACK Parameters

- WORK (TRANSACTION) - Optional synonyms for readability; no functional difference.
- TO SAVEPOINT savepoint_name - String. Name of a previously declared savepoint to which the transaction should revert.

Example Queries Using SQL ROLLBACK

-- Example 1: Abort entire transaction
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- Something goes wrong
ROLLBACK;

-- Example 2: Roll back to savepoint
BEGIN;
SAVEPOINT before_discount;
UPDATE orders SET total = total * 0.9;
-- Business rule fails
ROLLBACK TO SAVEPOINT before_discount;
COMMIT;

Expected Output Using SQL ROLLBACK

All changes made in the transaction (or after the savepoint) are undone
A message such as "ROLLBACK" or "ROLLBACK completed" is returned, and locks acquired by the rolled-back statements are released

Use Cases with SQL ROLLBACK

Cancel an entire multi-statement transaction when a business rule fails
Undo part of a transaction using SAVEPOINT after catching an application error
Maintain data integrity in ETL jobs or batch scripts
Revert changes during manual maintenance if verification queries detect anomalies

Common Mistakes with SQL ROLLBACK

Forgetting to start a transaction, leaving no work to roll back
Assuming ROLLBACK can undo auto-committed DDL in dialects that implicitly commit
Misspelling the savepoint name, which raises an error
Expecting row counts to be returned after ROLLBACK; most systems only send a confirmation message

First Introduced In

SQL-92

Frequently Asked Questions

What is the difference between ROLLBACK and COMMIT?

COMMIT makes all changes in the current transaction permanent, while ROLLBACK undoes them. Both also release locks and end the transaction.

Can I undo a ROLLBACK?

No. Once a rollback completes, the discarded changes are gone. You must rerun the original statements if you still need them.

How does ROLLBACK interact with auto-commit mode?

In auto-commit mode, every statement is implicitly committed. ROLLBACK has no effect unless you explicitly disable auto-commit or start a manual transaction.

Why does my DDL not roll back?

Some databases auto-commit DDL statements or treat them as implicit commits. Check your dialect's documentation to see if ROLLBACK can reverse DDL operations.