SQLite Basics: Commit, Roll Back, and Use Savepoints

A transaction groups changes into a unit you can accept or undo. It is useful whenever a task needs several related writes, such as updating stock and recording why it changed. These examples use toy inventory in memory, so they never touch a real stock database.

The runner below leaves transaction control to the SQL. Each invocation opens a fresh connection; tables and rows exist only until that invocation finishes. A successful commit is visible on that connection, but an in-memory database is not a durable file.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. No packages are needed. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this small runner as run-sql.py. It executes SQL against a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. Put each statement’s ending semicolon at the end of its own line, as below; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Commit a complete stock adjustment

Save as commit-stock.sql:

CREATE TABLE stock (sku TEXT PRIMARY KEY, quantity INTEGER);
CREATE TABLE movements (sku TEXT, delta INTEGER);
INSERT INTO stock VALUES ('pen', 10);
BEGIN;
UPDATE stock SET quantity = quantity - 2 WHERE sku = 'pen';
INSERT INTO movements VALUES ('pen', -2);
COMMIT;
SELECT sku, quantity FROM stock ORDER BY sku;
SELECT sku, delta FROM movements ORDER BY sku;

Run:

python3 run-sql.py commit-stock.sql

Output:

pen|8
pen|-2

The quantity change and movement record sit inside the same transaction. COMMIT accepts both. In an application, also check that the update matched the intended row and that the quantity is allowed. A transaction provides an all-or-nothing boundary; it does not invent business validation.

Keep transactions short. User interaction or slow network requests inside a write transaction can unnecessarily delay other writers.

2. Undo a trial change

Save as rollback-stock.sql:

CREATE TABLE stock (sku TEXT PRIMARY KEY, quantity INTEGER);
INSERT INTO stock VALUES ('pen', 10);
BEGIN;
UPDATE stock SET quantity = 0 WHERE sku = 'pen';
SELECT 'preview', quantity FROM stock WHERE sku = 'pen';
ROLLBACK;
SELECT 'restored', quantity FROM stock WHERE sku = 'pen';

Run:

python3 run-sql.py rollback-stock.sql

Output:

preview|0
restored|10

The first query sees the trial quantity on the same connection. ROLLBACK cancels the transaction, and the second query shows the original quantity again. This is useful for understanding a proposed update before accepting it in a controlled practice database.

Do not assume every SQL error automatically rolls back an entire transaction. Error handling depends on the error and conflict policy. Application code should handle failures explicitly and avoid continuing to commit after a failed business operation.

3. Undo only an optional step

Save as savepoint-stock.sql:

CREATE TABLE stock (sku TEXT PRIMARY KEY, quantity INTEGER);
INSERT INTO stock VALUES ('pen', 10);
BEGIN;
UPDATE stock SET quantity = quantity + 5 WHERE sku = 'pen';
SAVEPOINT optional_adjustment;
UPDATE stock SET quantity = quantity - 3 WHERE sku = 'pen';
ROLLBACK TO optional_adjustment;
RELEASE optional_adjustment;
COMMIT;
SELECT sku, quantity FROM stock ORDER BY sku;

Run:

python3 run-sql.py savepoint-stock.sql

Output:

pen|15

The delivery adds five units before the savepoint. Rolling back to the savepoint undoes only the later subtraction. ROLLBACK TO leaves that savepoint active, so RELEASE removes it before the surrounding transaction is committed.

An inner release is not an independent durable commit. A later rollback of the surrounding transaction could still undo the delivery. Use savepoints for nested units of work rather than trying to nest BEGIN statements.

SQLite’s transaction reference explains transaction boundaries and writer contention; its savepoint reference covers partial rollback. These single-connection examples do not test concurrent writers or recovery after a crash.

Clean up

In the same terminal, remove the example files and the empty scratch directory:

rm -- run-sql.py commit-stock.sql rollback-stock.sql savepoint-stock.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.