SQL in a Nutshell, 3rd Edition

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 ...