Quarantine SQLite rows with savepoints

AI assisted the preparation of this article, and its Python example was tested locally with synthetic fixtures.

A batch import sometimes has an explicit policy to keep valid rows and record invalid ones. SQLite savepoints let a failed row roll back inside a larger transaction, while the final decision to commit or roll back remains with the outer transaction.

Flow: Outer transaction, then Row savepoint, then Accept or reject, then Commit or rollback.

Give each row a rollback boundary

This local fixture uses an in-memory database and a unique integer key. The connection is opened with isolation_level=None, and the function explicitly starts its outer transaction. It records only the rejected row ID and a generic reason, not the supplied value.

import sqlite3

def setup(db):
    db.execute("CREATE TABLE accepted(id INTEGER PRIMARY KEY, value TEXT NOT NULL)")
    db.execute("CREATE TABLE rejected(row_id INTEGER, reason TEXT NOT NULL)")

def ingest(db, rows, fail_after=False):
    db.execute("BEGIN")
    try:
        for row_id, value in rows:
            db.execute("SAVEPOINT one_row")
            try:
                db.execute("INSERT INTO accepted VALUES (?, ?)", (row_id, value))
            except sqlite3.IntegrityError:
                db.execute("ROLLBACK TO one_row")
                db.execute("RELEASE one_row")
                db.execute("INSERT INTO rejected VALUES (?, ?)", (row_id, "integrity_error"))
            else:
                db.execute("RELEASE one_row")
        if fail_after:
            raise RuntimeError("synthetic outer failure")
        db.execute("COMMIT")
    except Exception:
        db.execute("ROLLBACK")
        raise

Try a valid row, a duplicate ID, and a second valid row. After commit, the two valid rows and one rejection record exist. A rollback to a savepoint leaves that savepoint active, so the example releases it before continuing. Releasing an inner savepoint does not commit the outer transaction.

The error record is inserted in the outer transaction. If a later unexpected failure causes that transaction to roll back, both the accepted rows and the new error records roll back. The fail_after argument exists to demonstrate that boundary in a fixture; an application should handle unexpected errors according to its own reporting policy.

Make partial acceptance a product choice

An all-or-nothing import is often easier to explain. Use this pattern only when the product explicitly permits partial acceptance and can tell the user which records were rejected. A generic integrity reason avoids logging the entire input, but a production design still needs an appropriate privacy and retention policy.

The fixture checks duplicate-key handling and an injected outer failure on CPython 3.13.5 with SQLite 3.49.1. It does not establish performance under load or concurrent writer behavior. The sample catches integrity errors as row rejections; other exceptions abort the batch. A real import must decide which specific constraint failures are expected and whether an error record itself can be stored safely.

External side effects cannot be rolled back by this SQLite transaction. Sending messages inside the loop needs a separate durable handoff and duplicate-safe processing. For the database semantics, see SQLite savepoints and Python transaction control.