Name

SAVEPOINT Statement

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

MySQL

Supported

Oracle

Supported

PostgreSQL

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 theyare defined, and savepoint names should be unique within their scope. Always make sure toprovide easy-to-understand names for your savepoints, because you’ll be referencing them later in your programs. 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.

Programming Tips and Gotchas

Generally, reusing a savepoint name won’t produce an error ...

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.