savepoint

A savepoint is a named marker inside a database transaction that allows rolling back part of the work while preserving earlier operations. Savepoints provide fine-grained error handling within a single transaction and can help implement multi-step procedures without abandoning the entire transaction.

Saving a point is done with SAVEPOINT name. If subsequent statements fail or produce an unwanted result, the transaction can be rolled back to that savepoint using ROLLBACK TO SAVEPOINT name. After rollback, execution resumes from that point, continuing within the same transaction until a final ROLLBACK or COMMIT.

Released savepoints: RELEASE SAVEPOINT name frees resources but does not commit or roll back. Not all databases implement RELEASE; some systems automatically release at transaction end. Savepoints must be created within an active transaction; autocommit mode typically starts a transaction implicitly.

Compatibility and limitations: Savepoints are supported by many relational databases, such as PostgreSQL, MySQL, Oracle, and SQLite. They do not create nested transactions; they are points within a single transaction. DDL statements and certain constraints may trigger implicit commits in some systems, which can complicate savepoint usage. Performance overhead is generally small but exists.

Example usage: START TRANSACTION; SAVEPOINT sp; -- perform a set of operations; if a later step fails, ROLLBACK TO SAVEPOINT sp; -- optionally continue with other steps; COMMIT;