SQL in a Nutshell, 2nd Edition

Name

SAVEPOINT Statement

Synopsis

This command breaks a transaction into logical breakpoints. Multiple savepoints may be specified within a single transaction. The main benefit of the SAVEPOINT command is that transactions may be partially rolled back to a savepoint marker using the ROLLBACK command.

Platform	Command
DB2	Supported, with variations
MySQL	Not supported
Oracle	Supported
PostgreSQL	Not supported
SQL Server	Supported, with limitations

SQL2003 Syntax

SAVEPOINT savepoint_name

Keywords

SAVEPOINT savepoint_name

Establishes a savepoint named savepoint_name within the current transaction.

Some vendors allow duplicate savepoint names within a transaction, but this is not recommended by the ANSI standard.

SQL2003 supports the statement RELEASE SAVEPOINT savepoint_name, enabling an existing savepoint to be eliminated. Refer to the RELEASE SAVEPOINT Statement section for more information about eliminating an existing savepoint.

Rules at a Glance

Savepoints are established within the scope of the entire transaction in which they are defined. Savepoint names should be unique within their scope. Furthermore, make sure you use BEGIN and COMMIT statements prudently, because accidentally placing a BEGIN statement too early or a COMMIT statement too late can have a dramatic impact on the way transactions are written to the database. Always make sure to provide easy-to-understand names for your savepoints because you’ll be referencing them later in your programs.

Programming Tips ...