04.2 Transactions, COMMIT and ROLLBACK

01 | WHY A GROUP OF CHANGES MATTERS

Suppose a returned copy is immediately borrowed by another member. The application must close the old loan and create a new one. If only the first change succeeds, the recorded state does not match the intended handover.

A transaction groups work into a unit that can be committed together or rolled back. Its boundary should match the business operation, not an arbitrary number of SQL lines.

Use this section’s fresh library practice database. Members stores borrowers, Books stores physical copies , and Loans stores borrowing events. Two copies may share a title but have different IDs and barcodes.

import sqlite3

# Explicit SQL transaction control; fresh data on every run.
connection = sqlite3.connect(":memory:", isolation_level=None)
connection.execute("PRAGMA foreign_keys = ON")
setup_sql = """

CREATE TABLE IF NOT EXISTS Members (
    member_id INTEGER PRIMARY KEY,
    member_name TEXT NOT NULL CHECK (length(trim(member_name)) > 0)
);
CREATE TABLE IF NOT EXISTS Books (
    book_id INTEGER PRIMARY KEY,
    barcode TEXT NOT NULL UNIQUE CHECK (length(trim(barcode)) > 0),
    title TEXT NOT NULL CHECK (length(trim(title)) > 0)
);
CREATE TABLE IF NOT EXISTS Loans (
    loan_id INTEGER PRIMARY KEY,
    member_id INTEGER NOT NULL REFERENCES Members(member_id),
    book_id INTEGER NOT NULL REFERENCES Books(book_id),
    borrowed_on TEXT NOT NULL,
    due_on TEXT NOT NULL,
    returned_on TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS one_active_loan_per_book
ON Loans(book_id) WHERE returned_on IS NULL;

INSERT INTO Members VALUES (1, 'Maya'), (2, 'Leo'), (3, 'Amina');
INSERT INTO Books VALUES (10, 'LIB-010', 'The Code Book'),
    (11, 'LIB-011', 'The Code Book'), (12, 'LIB-012', 'Python Adventures');
INSERT INTO Loans VALUES
    (100, 1, 10, '2026-10-01', '2026-10-15', NULL),
    (101, 2, 11, '2026-09-01', '2026-09-15', '2026-09-14');

"""
changes = """
-- Put this exercise's INSERT, UPDATE, DELETE or transaction here.
"""
query = """
SELECT * FROM Loans ORDER BY loan_id;
"""

try:
    connection.executescript(setup_sql)
    connection.executescript(changes)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    if connection.in_transaction:
        connection.execute("ROLLBACK")
    print("Database error:", error)
finally:
    connection.close()

02 | UNDERSTAND THE THREE COMMANDS

Committing an in-memory database does not make it survive program exit. Persistence also requires a file-backed database or server storage. Conversely, ROLLBACK is not a way to undo any already committed change at any later time.

03 | WATCH A CHANGE BE UNDONE

BEGIN;
UPDATE Members SET member_name = 'Temporary name' WHERE member_id = 1;
ROLLBACK;

The original name Maya remains. The temporary value would be visible to this connection inside the transaction, but the rollback discards it.

COMMIT in place of ROLLBACK would retain the new value for the lifetime of the practice database.

04 | COMMIT A COMPLETE HANDOVER

BEGIN IMMEDIATE;
UPDATE Loans SET returned_on = '2026-10-12'
WHERE loan_id = 100 AND returned_on IS NULL;
INSERT INTO Loans (loan_id, member_id, book_id, borrowed_on, due_on)
VALUES (102, 3, 10, '2026-10-12', '2026-10-26');
COMMIT;

The old event is retained as returned and the new active event is recorded. BEGIN IMMEDIATE acquires write access at the start; it can fail if another writer already holds it.

The unique active-loan rule prevents two active loans for copy 10. In application code, also verify that the intended old loan actually changed before declaring a successful handover.

05 | A STATEMENT ERROR DOES NOT ALWAYS UNDO EVERYTHING

In SQLite’s usual constraint handling, a failed statement can leave earlier successful statements in the transaction pending. Do not assume every error automatically rolls back the whole business operation.

For example, closing loan 100 and then inserting a loan for missing member 999 causes a foreign-key error. Catch the failure and roll back the transaction so the first change is undone too.

The supplied teaching runner rolls back an active transaction in its error handler. A failed script will print an error rather than execute its final inspection query; use the complete Python demonstration below to inspect the restored state.

06 | RUN THE FAILURE AND SEE THE RESTORATION

import sqlite3

connection = sqlite3.connect(":memory:", isolation_level=None)
connection.execute("PRAGMA foreign_keys = ON")
connection.executescript("""
CREATE TABLE Members (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE Loans (
    loan_id INTEGER PRIMARY KEY,
    member_id INTEGER NOT NULL REFERENCES Members(member_id),
    returned_on TEXT
);
INSERT INTO Members VALUES (1, 'Maya');
INSERT INTO Loans VALUES (100, 1, NULL);
""")

try:
    connection.execute("BEGIN IMMEDIATE")
    connection.execute("UPDATE Loans SET returned_on = ? WHERE loan_id = ?",
                       ('2026-10-12', 100))
    connection.execute("INSERT INTO Loans VALUES (?, ?, ?)", (101, 999, None))
    connection.execute("COMMIT")
except sqlite3.Error as error:
    if connection.in_transaction:
        connection.execute("ROLLBACK")
    print("Not completed:", error)
finally:
    print(connection.execute("SELECT * FROM Loans ORDER BY loan_id").fetchall())
    connection.close()

Save this as a separate Python file and Run. After the error, the final list is [(100, 1, None)]: the original loan is still active. The question marks bind values; the next lesson explains them.

07 | ATOMICITY AND THE OTHER ACID IDEAS

Atomicity means the grouped operation is committed as a unit or undone. Consistency concerns preserving the database’s defined rules. Isolation concerns interactions between concurrent work. Durability concerns retaining committed changes through the storage system’s guarantees.

A transaction does not make incorrect SQL correct. It cannot enforce business rules you never defined, and an in-memory example cannot demonstrate persistence after the program ends.

08 | PICK ONE TRANSACTION CONTROL STYLE

Python’s sqlite3 supports several transaction-control modes. These lessons deliberately use isolation_level=None and explicit SQL BEGIN, COMMIT and ROLLBACK, leaving the newer autocommit argument unset. This makes our teaching boundary visible.

Do not copy this pattern into a connection configured differently without checking its behaviour. In particular, Python connection commit()/rollback() behaviour depends on mode; here we consistently issue the SQL transaction commands.

Use execute for statements within the application’s active transaction. Executescript has additional transaction behaviour and is not a drop-in replacement for a sequence of parameterised execute calls.

09 | KEEP TRANSACTIONS FOCUSED

Do not leave a write transaction open while waiting for someone to type an answer or make tea. Validate basic input first, then acquire write access, check current database facts, perform the operation and finish promptly.

SQLite permits only one simultaneous writer. Short transactions reduce avoidable contention. More advanced systems and workloads have different concurrency behaviour, so do not assume this example describes all databases.

BEGIN transactions do not nest in SQLite. SAVEPOINT supports more advanced partial-work patterns, which are beyond this foundation exercise.

10 | PRACTISE A CONTROLLED CHANGE

BEGIN;
UPDATE Members SET member_name = 'Trial name' WHERE member_id = 2;
ROLLBACK;

Stretch: modify the failure demonstration to use existing member 1 instead of 999. Predict how the final list changes when both statements succeed.

KEEP LEARNING

Previous page 04.3 Parameterised queries and database permissions

References: Python SQLite parameters and transactions · SQLite transactions · Partial indexes