Savepoints for Partial Rollbacks in MySQL

1. What Savepoints Solve: Partial Rollback Instead of All or Nothing

A standard transaction in MySQL only knows two final states: a full COMMIT or a full ROLLBACK. In a transaction that contains several independent steps, for example inserting ten records in a batch, an error in the eighth step without savepoints means either the entire transaction is rolled back, including the seven successful steps, or the error is ignored and the transaction commits anyway, risking data inconsistency.

Savepoints solve exactly this problem by setting named marker points inside a transaction. A ROLLBACK TO SAVEPOINT only undoes the changes made since that marker point, the transaction itself stays open, and every change made before it is preserved. This enables fine-grained error handling inside a single transaction without giving up the benefits of atomicity for the overall operation.

2. Syntax: SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT

The syntax of savepoints in MySQL consists of three commands. SAVEPOINT name sets a named marker point inside the current transaction. ROLLBACK TO SAVEPOINT name undoes all changes made since that point without ending the transaction, so further statements can be executed afterward. RELEASE SAVEPOINT name removes the savepoint without performing a rollback, typically once the marked code path has completed successfully and the savepoint is no longer needed.

Important: a plain COMMIT or ROLLBACK without TO SAVEPOINT ends the entire transaction and automatically deletes every savepoint set within it. If a savepoint with the same name is set a second time, it implicitly replaces the previous savepoint of the same name without raising an error, which matters in loops that reuse the same name.

START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (101, 49.90);

SAVEPOINT before_items;

INSERT INTO order_items (order_id, sku, qty) VALUES (LAST_INSERT_ID(), 'SKU-001', 2);
-- Suppose the next statement fails due to a constraint violation
INSERT INTO order_items (order_id, sku, qty) VALUES (LAST_INSERT_ID(), 'SKU-999', 1);

-- Undo only the item inserts, keep the order itself
ROLLBACK TO SAVEPOINT before_items;

-- Retry with corrected data
INSERT INTO order_items (order_id, sku, qty) VALUES (LAST_INSERT_ID(), 'SKU-002', 1);

RELEASE SAVEPOINT before_items;
COMMIT;

3. Savepoints and the InnoDB Storage Engine in Detail

InnoDB implements savepoints through its internal undo log system, the same mechanism also used for MVCC and normal rollbacks. When a savepoint is set, InnoDB remembers the current position in the transaction's undo log. A ROLLBACK TO SAVEPOINT replays the undo log entries back to that position and thereby undoes exactly the changes made since the savepoint, while every lock acquired since the savepoint is released as well.

One important detail: locks acquired before the savepoint remain in place after a ROLLBACK TO SAVEPOINT, because they belong to changes that are not being rolled back. Only locks acquired for rows changed after the savepoint are released. This means a partial rollback does not fully free the transaction of its existing locks, an important difference from a full rollback, which releases all locks held by the transaction.

4. Practical Example: Batch Import with a Savepoint per Row

A classic use case for savepoints is importing a CSV file with several hundred rows, where individual rows may contain invalid data, but the overall import should still run in a single transaction for performance reasons. Without savepoints, a single invalid record would ruin the entire import. With a savepoint before every individual row, an error can roll back and skip only that row, while the rest of the import continues within the same transaction.

This technique also reduces overhead compared to a separate transaction per row, because COMMIT operations in InnoDB with innodb_flush_log_at_trx_commit enabled can trigger an fsync call that costs noticeable time across thousands of individual transactions. A savepoint per row inside a larger transaction avoids this overhead entirely, because no actual commit happens until the whole batch has been processed.

-- Batch import with per-row savepoints inside one transaction
START TRANSACTION;

SAVEPOINT row_1;
INSERT INTO product_import (sku, name, price) VALUES ('A-100', 'Widget', 9.99);
-- If this row is invalid, the application issues:
-- ROLLBACK TO SAVEPOINT row_1;

SAVEPOINT row_2;
INSERT INTO product_import (sku, name, price) VALUES ('A-101', 'Gadget', 19.99);

SAVEPOINT row_3;
INSERT INTO product_import (sku, name, price) VALUES (NULL, 'Invalid Row', -5.00);
-- Constraint violation detected by application logic
ROLLBACK TO SAVEPOINT row_3;
-- Log the skipped row, continue with the next one

COMMIT; -- rows 1 and 2 persisted, row 3 skipped

5. Using Savepoints in PHP with PDO

PDO does not offer a dedicated method for savepoints, they are set through PDO::exec() with raw SQL, since the concept is not abstracted in the PDO API. The following class encapsulates the savepoint logic for a robust batch import that skips individual invalid rows without aborting the entire import or opening a separate transaction with full commit overhead for every row.

An important point in the PHP implementation: the savepoint name must be a valid SQL identifier and must never be assembled directly from user input, in order to avoid SQL injection through the name. In practice, a programmatically generated, strictly alphanumeric name like sp_1, sp_2, and so on is entirely sufficient.

<?php
declare(strict_types=1);

/**
 * Imports rows one by one, skipping invalid rows via a savepoint
 * rollback instead of aborting the whole transaction.
 */
final class BatchImporter
{
    public function __construct(private readonly PDO $pdo)
    {
    }

    public function importRows(array $rows): array
    {
        $skipped = [];
        $this->pdo->beginTransaction();

        foreach ($rows as $index => $row) {
            $savepoint = "sp_{$index}"; // safe, generated identifier
            $this->pdo->exec("SAVEPOINT {$savepoint}");

            try {
                $stmt = $this->pdo->prepare(
                    'INSERT INTO product_import (sku, name, price) VALUES (:sku, :name, :price)'
                );
                $stmt->execute($row);
            } catch (PDOException $e) {
                // Undo only this row, keep everything imported so far
                $this->pdo->exec("ROLLBACK TO SAVEPOINT {$savepoint}");
                $skipped[] = ['row' => $row, 'error' => $e->getMessage()];
                continue;
            }

            $this->pdo->exec("RELEASE SAVEPOINT {$savepoint}");
        }

        $this->pdo->commit();
        return $skipped;
    }
}

6. Nested Savepoints and Name Collisions

Savepoints can be nested as many times as needed within the same transaction, simply by setting several savepoints one after another. A ROLLBACK TO SAVEPOINT on an outer savepoint automatically invalidates every inner savepoint set after that point as well, they do not need to be cleaned up manually. This property allows multi-level error handling with different granularities to be expressed in a single transaction, for example one savepoint per batch and another per row inside the batch.

Regarding name collisions: if a savepoint is set again with an already existing name, the marker for that name moves to the new position, and the older savepoint with the same name can no longer be reached through that name afterward. In loops that reuse the same savepoint name, for example always SAVEPOINT loop_iteration, this is the desired behavior. When dynamic names are needed, as in the PHP example above, this prevents name collisions entirely.

-- Same savepoint name reused inside a loop-like pattern
START TRANSACTION;

SAVEPOINT loop_iteration;
INSERT INTO log_entries (message) VALUES ('Step 1');

-- Re-declaring the same name moves the marker forward
SAVEPOINT loop_iteration;
INSERT INTO log_entries (message) VALUES ('Step 2');

-- Only "Step 2" is undone, "Step 1" remains committed within the transaction
ROLLBACK TO SAVEPOINT loop_iteration;

COMMIT;

7. Savepoints vs. True Nesting: What MySQL Does Not Offer

A common misconception is confusing savepoints with true nested transactions. MySQL, like most relational databases, does not support true nested transactions, in which an inner transaction could commit independently of the outer one. A START TRANSACTION inside an already running transaction implicitly commits the previous transaction in MySQL, instead of opening a genuine inner transaction, which leads to unexpected behavior if used carelessly.

Savepoints are the substitute MySQL offers for this missing feature, with one important limitation: a savepoint cannot commit independently. Every change, even those made after a RELEASE SAVEPOINT, only becomes permanent with the final COMMIT of the outer transaction. Anyone who needs true independence between partial operations, for example so that a partial result survives even if the main operation is later rolled back, must use separate, standalone transactions, not savepoints.

8. Performance Aspects and Limits of Savepoints

Setting a savepoint itself is a very cheap operation, since InnoDB only records the current position in the undo log without writing additional data or triggering a disk sync. The actual performance advantage over individual transactions per row comes from the fact that a ROLLBACK TO SAVEPOINT does not require an fsync, while a real COMMIT quite possibly does, depending on the innodb_flush_log_at_trx_commit setting.

The limit of savepoints lies in transaction duration: since all savepoints are part of the same, potentially long transaction, the same rules apply to them as to any other long open transaction, for example regarding undo log growth and held locks. A batch import with thousands of savepoints inside a single transaction can itself become a long-running transaction if the overall batch is chosen too large. A sensible batch size of a few hundred to a few thousand rows per transaction balances commit overhead and transaction duration.

9. Savepoint, Full Rollback, and Separate Transaction Compared

The following table compares the three common strategies for error handling within multi-step database operations.

For batch processing with many independent rows, savepoints are, as a rule, the best choice, because they preserve work already done without causing the commit overhead of separate transactions. For a few strongly interdependent steps, where an error in one step invalidates the entire operation, a full rollback remains the correct and simpler choice.

10. Summary

Savepoints extend the binary commit-or-rollback model of MySQL with fine-grained checkpoints inside a transaction. With SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT, only the faulty part of a multi-step operation can be rolled back with precision, while already successful steps remain intact. InnoDB implements this efficiently through its existing undo log system, without the overhead of a real commit per savepoint.

In practice, savepoints are particularly well suited to batch imports with potentially invalid individual rows, where they replace separate transactions per row and their fsync overhead. What remains important is that savepoints are no substitute for true nested transactions, which MySQL fundamentally does not support, and that every change ultimately depends on the final commit of the outer transaction.

11. FAQ: Savepoints for Partial Rollbacks