A transaction normally gives application code an all-or-nothing boundary: either commit its changes or roll them all back. Some workflows need a smaller recovery point inside that larger unit of work.

SQLite provides that recovery point with savepoints. A savepoint lets code mark a position inside a transaction, perform additional work, and later undo only the changes made after that position. The surrounding transaction can remain active.

Savepoints are useful for batch processing, optional sub-operations, library code that may run inside an existing transaction, and workflows where one recoverable step should not discard earlier valid work.

Why another BEGIN is not the answer

SQLite transactions started with BEGIN do not nest. If a transaction is already active, issuing another BEGIN fails.

For nested transaction-like scopes, SQLite uses three statements:

SAVEPOINT name;
ROLLBACK TO name;
RELEASE name;

A savepoint can be created inside a transaction started with BEGIN. It can also be created when no transaction is active; an outermost savepoint then acts as the transaction boundary.

This distinction matters when reusable code does not control the outer transaction. Starting a new BEGIN is not composable, while a savepoint can create a local recovery boundary within existing work.

Roll back only the later work

Consider a transaction that has already inserted one valid row:

BEGIN;

INSERT INTO items(value) VALUES ('outer');

SAVEPOINT batch;

INSERT INTO items(value) VALUES ('discard');
ROLLBACK TO batch;

INSERT INTO items(value) VALUES ('keep');

RELEASE batch;
COMMIT;

After the commit, outer and keep remain. The discard insert is undone.

The important detail is that ROLLBACK TO batch does not end the surrounding transaction. It restores database state to just after SAVEPOINT batch was established and leaves that savepoint active.

That last point surprises many developers: rolling back to a savepoint is different from removing the savepoint.

RELEASE removes the savepoint

RELEASE batch removes the named savepoint from the transaction stack.

For an inner savepoint, RELEASE does not make its changes independently durable. Those changes still belong to the outer transaction and can be undone by a later outer ROLLBACK.

For example:

BEGIN;

SAVEPOINT part;
INSERT INTO items(value) VALUES ('temporary');
RELEASE part;

ROLLBACK;

The inserted row does not survive. Releasing part only removed that inner recovery point; the outer transaction was still uncommitted.

This is why describing every RELEASE as a commit can be misleading. It is commit-like with respect to the savepoint stack, but durability is determined by the outermost transaction boundary.

ROLLBACK TO keeps the target savepoint active

Suppose a batch step fails and the application runs:

ROLLBACK TO batch;

SQLite cancels savepoints created after batch and undoes database changes made after batch was established. The batch savepoint itself remains on the stack.

Code can therefore continue from that recovery point:

SAVEPOINT batch;

INSERT INTO items(value) VALUES ('attempt-1');
ROLLBACK TO batch;

INSERT INTO items(value) VALUES ('attempt-2');
RELEASE batch;

If the intention is to finish using the savepoint after a rollback, explicitly RELEASE it once recovery work is complete.

Keeping the target active is useful, but it also means error paths must manage savepoint lifetime deliberately rather than assuming ROLLBACK TO closes the scope.

Savepoints form a stack

Savepoints may be nested:

SAVEPOINT outer_step;

INSERT INTO items(value) VALUES ('a');

SAVEPOINT inner_step;
INSERT INTO items(value) VALUES ('b');
RELEASE inner_step;

RELEASE outer_step;

SQLite processes savepoints as a stack. The most recently created scope is the innermost one.

A ROLLBACK TO operation works backward to the most recent savepoint with the matching name. Changes after that savepoint are undone, and savepoints created after it are canceled.

A RELEASE operation also searches backward from the most recent savepoint and removes savepoints through the matching one.

Because matching is based on the most recent savepoint with that name, reusing names in nested scopes is legal but easy to misunderstand. Unique names within a transaction are usually clearer for application code.

An outermost savepoint can be the transaction

SQLite also allows this pattern without BEGIN:

SAVEPOINT operation;

INSERT INTO items(value) VALUES ('one');
INSERT INTO items(value) VALUES ('two');

RELEASE operation;

When operation is the outermost transaction scope, releasing it empties the transaction stack and commits the transaction.

Likewise, a plain COMMIT commits all outstanding transaction scopes, while a plain ROLLBACK rolls back the entire transaction and clears the stack.

This behavior makes savepoints flexible, but applications should still choose a consistent ownership model. If one layer owns the top-level transaction, inner layers should generally avoid unexpectedly committing it.

Use savepoints for recoverable sub-operations

A good savepoint boundary surrounds work that may legitimately fail while the larger operation can still proceed.

For example, an import process might create a savepoint for each independent record:

begin transaction

for each record:
    create savepoint
    try to apply record
    if record is invalid:
        roll back to savepoint
    release savepoint

commit transaction

This can preserve earlier successful work while isolating a rejected record.

Whether that behavior is correct is a business decision. If all records must be mutually consistent, partial recovery is the wrong design; the whole transaction should fail instead.

Savepoints provide a mechanism for partial rollback. They do not decide which failures are safe to ignore.

Keep transaction duration in mind

Rolling back part of a transaction does not end the outer transaction.

If application code opens a transaction, processes thousands of savepoint-protected operations, and commits only at the end, the transaction is still long-lived. Savepoints do not turn it into thousands of independent commits.

Long transactions can affect concurrency, resource use, journal or WAL growth, and how long a write transaction occupies SQLite’s single-writer path.

Choose the outer transaction size according to the atomicity the application needs. Do not use savepoints as a substitute for deciding where durable commit boundaries should be.

Do not confuse recovery with error suppression

A database error does not automatically mean the application should ROLLBACK TO and continue.

Some failures indicate bad input that a local savepoint can safely isolate. Others indicate environmental or transaction-level problems where continuing may be inappropriate.

SQLite documents that errors such as I/O failures, out-of-memory conditions, interruptions, and a full disk can affect transaction state in ways applications should handle carefully. Code that catches every database exception and blindly continues from a savepoint can hide serious failures.

Classify expected recoverable failures separately from infrastructure failures. When transaction state is uncertain, prefer aborting the larger operation over guessing that it remains usable.

Let the database library manage statement parameters

Savepoint logic does not change normal SQL security practices.

Values should still be passed through the database driver’s parameter-binding mechanism rather than interpolated into SQL text. Savepoint names, however, are SQL identifiers rather than ordinary bound values in most APIs.

If application code generates savepoint names dynamically, generate them internally from a constrained scheme instead of inserting untrusted input into transaction-control statements.

A simple counter such as sp_1, sp_2, and sp_3 is easier to reason about than a name derived from user data.

Design reusable transaction helpers carefully

Savepoints are particularly useful when a helper function may be called both inside and outside a larger workflow.

There are two common designs:

The caller owns the transaction

The helper performs SQL statements but never starts, commits, or rolls back a transaction. The caller defines atomicity.

This is the simplest contract when all callers can manage transactions consistently.

The helper owns a savepoint

The helper creates a uniquely named savepoint, performs its work, releases the savepoint on success, and rolls back to it on an expected failure.

This can compose with an existing outer transaction, but the helper must be precise about error propagation and cleanup. It should not silently convert an unrecoverable database failure into apparent success.

Whichever model you choose, document who owns the outer commit. Hidden transaction ownership is a common source of surprising behavior.

Common pitfalls

Starting BEGIN inside BEGIN

SQLite does not support nested BEGIN...COMMIT transactions. Use a savepoint for a nested recovery scope.

Assuming RELEASE makes inner work durable

An inner RELEASE removes a savepoint. A later rollback of the outer transaction can still undo those changes.

Assuming ROLLBACK TO removes the savepoint

The target savepoint remains active. Release it when the scope is finished.

Reusing savepoint names casually

SQLite resolves a matching name from the most recent savepoint backward. Unique names make nested code easier to audit.

Keeping one giant transaction accidentally

Per-item savepoints do not create per-item commits. The outer transaction still determines durability and transaction lifetime.

Continuing after every database error

Not every error is a recoverable record-level failure. Treat serious storage and transaction-state errors separately.

Use savepoints as explicit recovery boundaries

A robust savepoint workflow has a small set of rules:

  1. use BEGIN for the outer transaction when your layer owns it;
  2. use SAVEPOINT for nested recovery scopes;
  3. use ROLLBACK TO to undo work after a savepoint without ending the outer transaction;
  4. use RELEASE when the savepoint scope is complete;
  5. remember that only the outermost commit makes the transaction durable;
  6. keep savepoint names controlled and transaction ownership documented;
  7. abort the larger transaction when a failure is not safely recoverable.

Savepoints are valuable because they make rollback boundaries more precise without weakening transaction atomicity. Used deliberately, they let an application recover from a local failure while preserving earlier work and keeping the final commit decision in one clearly owned place.

Database SQLite SQL Transactions