Database Transactions and ACID with Python and SQLite

By Engr. Henry Akpan

All or Nothing

A student pays ₦50,000 school fees by transfer. The bank must take the money from the student's account and add it to the school's account. If the server crashes after the first step but before the second, ₦50,000 simply vanishes. Databases prevent this with transactions: a group of operations that either all succeed together or all fail together. This tutorial explains database transactions and ACID (atomicity, consistency, isolation and durability), how to commit and roll back with Python's built-in sqlite3 module, how constraints protect consistency, what isolation means when two connections work at once, and savepoints, with outputs from running the code.

What Is a Transaction?

A database transaction is a sequence of operations treated as a single unit of work. It starts (explicitly with BEGIN, or implicitly), runs its statements, and then ends in one of two ways:

  • COMMIT: make all the changes permanent and visible to others.
  • ROLLBACK: undo every change made since the transaction began, as if it never happened.

ACID: The Four Guarantees

  • Atomicity: all of the transaction's changes happen, or none do. There is no half-finished transfer.
  • Consistency: a transaction moves the database from one valid state to another. Rules such as constraints (no negative balances, unique emails, valid foreign keys) are never left broken.
  • Isolation: transactions running at the same time don't see each other's unfinished work; the result is as if they ran one after another.
  • Durability: once a commit succeeds, the changes survive crashes and power failures, because they have been written safely to disk (SQLite uses a journal or write-ahead log for this).

Setting Up

We create a small bank database in a file (a file is needed later to show two connections). The CHECK constraint enforces a business rule: balances can never go negative.

import sqlite3

conn = sqlite3.connect("bank.db")
conn.executescript("""
    DROP TABLE IF EXISTS accounts;
    CREATE TABLE accounts (
        name TEXT PRIMARY KEY,
        balance INTEGER NOT NULL CHECK (balance >= 0)
    );
    INSERT INTO accounts VALUES ('Student', 80000), ('School', 1000000);
""")

def balances(c=conn):
    return dict(c.execute("SELECT name, balance FROM accounts ORDER BY name"))

print(balances())

Output:

{'School': 1000000, 'Student': 80000}

(executescript commits any pending transaction before running, so the setup is saved.)

Atomicity: A Transfer That Either Fully Happens or Doesn't

In Python's sqlite3, using the connection as a context manager (with conn:) wraps the block in a transaction: if the block finishes normally it commits; if an exception is raised it rolls back.

def transfer(frm, to, amount):
    try:
        with conn:
            conn.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", (amount, to))
            conn.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, frm))
        print(f"Transferred {amount}")
    except sqlite3.IntegrityError as e:
        print(f"Failed ({e}); rolled back")

transfer("Student", "School", 50000)
print(balances())
transfer("Student", "School", 50000)      # only 30,000 left: must fail
print(balances())

Output:

Transferred 50000
{'School': 1050000, 'Student': 30000}
Failed (CHECK constraint failed: balance >= 0); rolled back
{'School': 1050000, 'Student': 30000}

Look carefully at the second transfer. The school's account was credited first, and only then did the debit fail the CHECK constraint. Without a transaction, the school would have kept the extra ₦50,000 that the student never paid. Because both updates were in one transaction, the rollback undid the credit too: the balances are exactly as they were.

Rolling Back on Purpose

Transactions also let a program back out when it discovers a problem halfway through, not only on database errors:

class InsufficientFunds(Exception):
    pass

def pay_fees(student, amount, fee_due):
    try:
        with conn:
            conn.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, student))
            conn.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'School'", (amount,))
            if amount < fee_due:
                raise InsufficientFunds(f"{amount} is less than the {fee_due} due")
        return "paid"
    except InsufficientFunds as e:
        return f"cancelled: {e}"

print(pay_fees("Student", 20000, fee_due=25000))
print(balances())

Output:

cancelled: 20000 is less than the 25000 due
{'School': 1050000, 'Student': 30000}

Raising any exception inside the with block undoes both updates. Without the context manager you would call conn.commit() and conn.rollback() yourself, and forgetting the rollback in an error path is a common bug.

Isolation: Uncommitted Changes Are Invisible

A second connection plays the part of another user. While the first connection's transaction is open, the second sees only committed data:

other = sqlite3.connect("bank.db")

conn.execute("BEGIN")
conn.execute("UPDATE accounts SET balance = balance + 5000 WHERE name = 'Student'")
print("inside the transaction:", balances(conn)["Student"])
print("another connection sees:", balances(other)["Student"])
conn.commit()
print("after commit, other sees:", balances(other)["Student"])

Output:

inside the transaction: 35000
another connection sees: 30000
after commit, other sees: 35000

Isolation prevents "dirty reads", where one user acts on data that might still be rolled back. SQLite allows many readers at once but only one writer at a time; while one connection is writing, another that tries to write waits (up to a timeout) and then gets a "database is locked" error. Larger databases such as PostgreSQL and MySQL allow many simultaneous writers and offer several isolation levels (read committed, repeatable read, serialisable) that trade strictness for speed.

Savepoints: Partial Rollback

Inside a long transaction, a savepoint marks a point you can roll back to without abandoning the whole transaction. Here a batch of payments keeps the good ones and skips the one that breaks the rules:

payments = [("Student", 10000), ("Student", 90000), ("Student", 5000)]
done = []
with conn:
    for i, (who, amt) in enumerate(payments):
        conn.execute(f"SAVEPOINT p{i}")
        try:
            conn.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amt, who))
            conn.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'School'", (amt,))
            conn.execute(f"RELEASE p{i}")
            done.append(amt)
        except sqlite3.IntegrityError:
            conn.execute(f"ROLLBACK TO p{i}")
            conn.execute(f"RELEASE p{i}")
print("accepted:", done)
print(balances())
other.close(); conn.close()

Output:

accepted: [10000, 5000]
{'School': 1065000, 'Student': 20000}

The ₦90,000 payment would have made the balance negative, so only that step was undone; the other two were committed together at the end of the with block.

Good Habits with Database Transactions

  • Group related changes (debit and credit, order and stock update) into one transaction.
  • Keep transactions short. Don't wait for user input or slow network calls while a transaction is open; other users may be blocked.
  • Put rules in the database (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) so that consistency is enforced even if application code has a bug. In SQLite, foreign keys must be switched on with PRAGMA foreign_keys = ON.
  • Many small separate inserts are slow because each commit waits for the disk; wrapping thousands of inserts in one transaction (or using executemany inside one) can be hundreds of times faster.
  • Remember that money should be stored as whole kobo or naira in integers, never as floating-point numbers.

Summary

  • A database transaction groups operations so they all commit or all roll back.
  • ACID: atomicity, consistency, isolation, durability.
  • In Python's sqlite3, with conn: commits on success and rolls back on any exception.
  • Constraints like CHECK protect consistency; a failed statement plus rollback undoes earlier steps too.
  • Other connections never see uncommitted changes; savepoints allow partial rollback inside a transaction.

Related Tutorials