A savepoint is a named marker within an open database transaction to which the transaction can later be partially rolled back, undoing only the changes made after the marker while keeping earlier work and the transaction itself alive. In transactions on relational databases, savepoints provide fine-grained error recovery inside a single atomic unit of work.
Savepoints are defined in the SQL standard and supported by all major relational systems, including PostgreSQL, Oracle, MySQL with InnoDB, SQL Server and SQLite. They do not change the ACID guarantees of the outer transaction: nothing becomes durable or visible to other sessions until the final commit.
How a savepoint works inside a transaction
When a savepoint is created, the database records a position in the transaction’s undo history. Later, a rollback to that savepoint reverses every change made after the position, using the same undo information that a full rollback would use, and the transaction continues from that point as if the later statements had never run.
Internally, many engines implement a savepoint as the start of a subtransaction. Changes made after it are tagged so they can be discarded separately, and releasing the savepoint merges them into the parent transaction. Savepoints can be nested: rolling back to an earlier savepoint also destroys all savepoints created after it.
The standard commands and their effects are summarized below.
| Command | Effect | Transaction state afterward |
|---|---|---|
SAVEPOINT name | Creates a named marker | Open, marker available |
ROLLBACK TO SAVEPOINT name | Undoes changes after the marker | Open, marker still exists |
RELEASE SAVEPOINT name | Removes the marker, keeps the changes | Open, changes merged into parent |
COMMIT | Makes all surviving changes durable | Closed |
ROLLBACK | Undoes the entire transaction | Closed, all savepoints discarded |
Differences between database systems
The core behavior is consistent, but syntax and side effects differ, especially regarding locks and error handling. These differences matter when code must run on several engines or when a framework hides the actual commands.
| System | Syntax notes | Locks after partial rollback | Notable behavior |
|---|---|---|---|
| PostgreSQL | Standard syntax with RELEASE | Locks taken after the savepoint are released | An error aborts the transaction unless a savepoint is used |
| Oracle | SAVEPOINT and ROLLBACK TO, no RELEASE | Locks taken after the savepoint are released | Reusing a name moves the marker |
| MySQL InnoDB | Standard syntax with RELEASE | Row locks are generally kept | A commit or rollback deletes all savepoints |
| SQL Server | SAVE TRANSACTION and ROLLBACK TRANSACTION name | Locks are kept until the transaction ends | Not allowed in distributed transactions |
| SQLite | Standard syntax, can also start a transaction | Database-level locking applies | RELEASE of the outermost savepoint commits |
PostgreSQL deserves particular attention. After any error, the whole transaction enters an aborted state and rejects further statements until it ends. Tools such as interactive clients and ORMs therefore wrap risky statements in savepoints so that one failed statement does not doom the transaction.
Savepoints compared with related transaction concepts
A savepoint is often confused with neighboring mechanisms because all of them deal with undoing or protecting work. The key distinction is scope: a savepoint affects only part of one transaction in one database session.
| Concept | Scope | Purpose |
|---|---|---|
| Savepoint | Part of one local transaction | Partial rollback and local error recovery |
| Rollback | Entire transaction | Discard all uncommitted changes |
| Commit | Entire transaction | Make changes durable and visible |
| Two-phase commit | Several participants | Atomic outcome across resources |
| Database checkpoint | Whole database instance | Flush data pages to shorten crash recovery |
Despite the similar name, a savepoint has nothing to do with the write-ahead log checkpoint used for crash recovery. A savepoint never makes data durable by itself; if the server crashes before commit, all work since the transaction began is lost, regardless of how many savepoints exist.
Practical uses of savepoints
Savepoints are most valuable when a transaction contains steps whose failure is expected and recoverable. Instead of abandoning a long transaction, the application rolls back the failed step and continues with an alternative.
- Batch imports that skip invalid records while keeping the valid ones in the same transaction.
- Insert-or-fallback logic, where a unique-key violation is caught and replaced by an update.
- Nested transaction emulation in frameworks, where an inner block runs inside a savepoint and can fail independently.
- Interactive sessions in which a client automatically protects each statement so a typing error does not abort the session.
Application frameworks rely heavily on this mechanism. Nested atomic blocks in several ORMs and the nested propagation mode of transaction managers are implemented with savepoints, so developers often use them without writing the commands directly.
A good design rule is to set a savepoint immediately before the risky statement and to release it as soon as that statement succeeds. Releasing promptly frees bookkeeping resources, keeps the nesting shallow and makes the intended recovery boundary obvious to anyone reading the code later.
Limitations and pitfalls
Savepoints are not free. Each one adds bookkeeping and undo tracking, and very large numbers per transaction can degrade performance. In PostgreSQL, a backend caches only a limited number of subtransaction identifiers, around 64, and exceeding it can cause noticeable slowdowns on busy systems.
| Problem | Cause | Remedy |
|---|---|---|
| Locks still held after partial rollback | Engine keeps locks until commit | Keep transactions short, retry at transaction level |
| Savepoint per row in large batches | Framework wraps every statement | Validate data first, use savepoints per chunk |
| Savepoint not found error | Earlier rollback destroyed later savepoints | Track nesting order explicitly |
| False sense of durability | Confusing savepoints with checkpoints | Commit to persist work |
The short version
Imagine filling out a long online form in one sitting. A savepoint is like a bookmark you set halfway through. If you make a mistake on a later page, you can return to the bookmark and redo only that part instead of starting the whole form again.
Nothing is actually submitted until you press the final button. Until the transaction is committed, every bookmarked step remains a draft, and abandoning the whole form still erases everything.
Where it makes a difference
A logistics company imports a nightly file of about 50,000 shipment updates into its database. Early versions wrapped the import in one transaction, and a single malformed record rolled back the entire batch, leaving the morning dispatch team with stale data.
The developers change the job to set a savepoint before each chunk of 500 records. When a chunk fails, the job rolls back to its savepoint, retries the records one at a time to isolate the bad ones, logs them for review and continues. The import now completes with only the faulty records rejected, and the whole batch still commits atomically at the end.