O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

ROLLBACK

Synopsis

The ROLLBACK statement undoes a transaction to its beginning or a previously declared SAVEPOINT. It closes open cursors and releases locks in the same way as COMMIT.

Vendor

Command

SQL Server

Supported, with variations

MySQL

Not supported

Oracle

Supported

PostgreSQL

Supported

SQL99 Syntax and Description

ROLLBACK [WORK]
[TO SAVEPOINT savepoint_name]

In addition to finalizing a single or group of data-manipulation operations, the ROLLBACK statement undoes transactions up to the last issued BEGIN or SAVEPOINT statement.

SQL99 offers the new, optional keywords AND CHAIN. None of the four vendors yet support this command. This new syntax is:

ROLLBACK [WORK] [AND [NO] CHAIN]

The AND CHAIN option tells the DBMS to end the current transaction, but to share the common transaction environment (such as transaction isolation level) with the next transaction. The AND NO CHAIN option simply ends the single transaction. The ROLLBACK command is functionally equivalent to the command, ROLLBACK WORK AND NO CHAIN.

Microsoft SQL Server Syntax and Variations

ROLLBACK [TRAN[SACTION] [transaction_name |
 @tran_name_variable |
savepoint_name | @savepoint_variable] ]

ROLLBACK clears all data modifications made to the current open transaction or to a specific, existing savepoint. If ROLLBACK is issued alone, it rolls back the current open transaction. ROLLBACK normally frees locks, but it does not free locks when rolling back to a savepoint. ROLLBACK behaves similarly to COMMIT ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required