Savepoints

Savepoints are markers placed within a database transaction that allow partial rollback of the transaction’s changes. They enable undoing only the statements executed after the savepoint, without abandoning the entire transaction. Savepoints are part of the SQL standard and are commonly used to manage complex sequences of operations with finer-grained error handling and recovery.

Within a transaction, a savepoint is created with a command such as SAVEPOINT name. If something goes wrong or a decision is made to discard recent changes, the system can revert to that point using ROLLBACK TO SAVEPOINT name. After the savepoint has served its purpose, some systems allow releasing it with RELEASE SAVEPOINT name to free associated resources. Note that savepoints exist only inside an active transaction; once the outer transaction is committed or rolled back, all savepoints within it are discarded.

Dialect differences exist among database systems. PostgreSQL, Oracle, MySQL, and SQLite support SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT. SQL Server uses SAVE TRANSACTION name and ROLBACK TRANSACTION name to achieve similar behavior, but it does not expose a RELEASE SAVEPOINT syntax. Some details, such as nesting limits and exact syntax, vary by system, so consultation of the specific DBMS documentation is advised.

Use cases include error handling in multi-step data manipulations, implementing retries or compensating actions, and simulating nested transactions where a full rollback would be too costly. While savepoints add flexibility, they introduce overhead and complexity, and excessive use can impact performance. They do not substitute proper transaction design and should be used where partial rollback offers a concrete advantage. Savepoints are also a common feature in some video games as markers for progress, but this article focuses on their role in database transactions.