Name

ROLLBACK Statement

The ROLLBACK statement undoes a transaction to its beginning or to a previously declared savepoint. ROLLBACK also closes any open cursors.

Platform

Command

MySQL

Supported, with limitations

Oracle

Supported, with variations

PostgreSQL

Supported, with limitations

SQL Server

Supported, with variations

SQL2003 Syntax

ROLLBACK [WORK]
[AND [NO] CHAIN]
[TO SAVEPOINT savepoint_name]

Keywords

WORK

An optional keyword, but basically just noise.

AND [NO] CHAIN

AND CHAIN tells the DBMS to end the current transaction, but to share the common transaction environment (such as transaction isolation level) with the next transaction. AND NO CHAIN simply ends the transaction (and is effectively the same as not including the clause at all).

TO SAVEPOINT savepoint_name

Allows the transaction to be rolled back to a named savepoint (that is, a partial rollback) rather than rolling back the entire transaction. The savepoint_name may be a literal expression or a variable. If no savepoint of savepoint_name is active, the statement will return an error. When the TO SAVEPOINT clause is omitted, all cursors are closed. When the TO SAVEPOINT clause is included, only the cursors that were open within the savepoint are closed.

In addition to undoing a single data-manipulation operation such as an INSERT, UPDATE, or DELETE statement (or a batch of them), the ROLLBACK statement undoes transactions up to the last issued START TRANSACTION, SET TRANSACTION, or SAVEPOINT statement.

Rules at a Glance

ROLLBACK is used ...

Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.