Database Transactions in Python

A database transaction is a sequence of SQL operations that either all succeed together or all fail together—there is no middle ground. Transactions are the foundation of reliable systems: if your code transfers money from one account to another, both operations must succeed, or both must fail. If the network dies mid-transfer, the database rolls back both operations automatically, preventing the loss of money. Without transactions, application code would need to manually undo failed operations—a nightmare in distributed systems.

This tutorial explains ACID properties, how to write transactional code in Python, and how isolation levels control consistency under concurrent access.

What Is a Transaction?

A transaction is a logical unit of work. In SQLite3 and PostgreSQL, this is called an implicit transaction:

import sqlite3

conn = sqlite3.connect('bank.db')
cursor = conn.cursor()

# These two operations form a transaction
cursor.execute('UPDATE accounts SET balance = balance - 100 WHERE id = 1')
cursor.execute('UPDATE accounts SET balance = balance + 100 WHERE id = 2')

# Commit both at once — either both succeed or both fail
conn.commit()

If the second UPDATE fails (e.g., account 2 doesn't exist), the first UPDATE is automatically rolled back. No money is lost.

ACID is the acronym for transaction guarantees:

• Atomicity: All-or-nothing. The transaction either fully completes or fully rolls back. Partial results never persist.
• Consistency: Data integrity rules are maintained. Foreign keys, unique constraints, and CHECK clauses are enforced before commit.
• Isolation: Concurrent transactions don't interfere. One transaction's uncommitted data is invisible to others (with nuances based on isolation level).
• Durability: Once committed, data survives crashes, power failures, and disk errors.

A single SQL statement (like INSERT INTO users VALUES (...)) is automatically wrapped in a transaction. For multi-statement transactions, explicitly call conn.commit() or conn.rollback().

Explicit Commit and Rollback

Here's a realistic example—transferring money between bank accounts:

import sqlite3

conn = sqlite3.connect('bank.db')
cursor = conn.cursor()

try:
    # Start the transaction (implicit, no explicit BEGIN needed)
    from_account_id = 1
    to_account_id = 2
    amount = 100.00
    
    # Check if from_account has sufficient balance
    cursor.execute('SELECT balance FROM accounts WHERE id = ?', (from_account_id,))
    from_balance = cursor.fetchone()[0]
    
    if from_balance < amount:
        raise ValueError(f"Insufficient funds: ${from_balance} < ${amount}")
    
    # Withdraw from source account
    cursor.execute(
        'UPDATE accounts SET balance = balance - ? WHERE id = ?',
        (amount, from_account_id)
    )
    
    # Deposit to destination account
    cursor.execute(
        'UPDATE accounts SET balance = balance + ? WHERE id = ?',
        (amount, to_account_id)
    )
    
    # Both operations succeeded; commit the transaction
    conn.commit()
    print(f"Transferred ${amount} successfully")

except Exception as e:
    # If anything failed, roll back both operations
    conn.rollback()
    print(f"Transfer failed: {e}")

finally:
    conn.close()

If the second UPDATE fails, or if we raise an exception, rollback() undoes both updates. From the database's perspective, neither operation happened.

Autocommit Mode: When Transactions Are Not Implicit

By default, SQLite3 and PostgreSQL operate in implicit transaction mode—you must call commit() to persist changes. Some applications require autocommit mode, where each statement is committed immediately:

import sqlite3

conn = sqlite3.connect('app.db')
conn.isolation_level = None  # Enable autocommit mode

cursor = conn.cursor()

# Each statement is committed immediately; no rollback possible
cursor.execute('INSERT INTO logs (message) VALUES (?)', ('User logged in',))
cursor.execute('UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = ?', (123,))

# Changes are already persisted; commit() has no effect
# conn.commit()

conn.close()

Autocommit is dangerous for multi-statement operations because there's no atomicity—if the second statement fails, the first has already persisted. Use autocommit only for operations that must not be rolled back (logging, analytics).

For PostgreSQL, autocommit is set differently:

import psycopg

conn = psycopg.connect(
    'postgresql://user:pass@localhost/myapp',
    autocommit=True  # Each statement is committed immediately
)

cursor = conn.cursor()
cursor.execute('INSERT INTO logs (message) VALUES (%s)', ('User logged in',))
# No conn.commit() needed; insert is already persisted

Savepoints: Nested Transactions

PostgreSQL (and SQLite3 in newer versions) support savepoints—nested transactions that roll back partially:

import psycopg

conn = psycopg.connect('postgresql://user:pass@localhost/myapp')
cursor = conn.cursor()

try:
    cursor.execute('INSERT INTO accounts (balance) VALUES (1000)')
    
    # Create a savepoint
    cursor.execute('SAVEPOINT sp1')
    
    try:
        # This operation fails
        cursor.execute('UPDATE accounts SET balance = balance - 50000 WHERE id = 1')
        cursor.execute('UPDATE accounts SET balance = balance + 50000 WHERE id = 2')
    except Exception as e:
        # Roll back only to sp1, not the entire transaction
        cursor.execute('ROLLBACK TO SAVEPOINT sp1')
        print(f"Operation failed, rolling back to savepoint: {e}")
    
    # The INSERT still persists; only the failed UPDATE was rolled back
    cursor.execute('INSERT INTO logs (message) VALUES (%s)', ('Transfer attempt logged',))
    
    conn.commit()

except Exception as e:
    conn.rollback()
    print(f"Transaction failed: {e}")

finally:
    conn.close()

Savepoints are useful for optional operations or cleanup logic that shouldn't fail the entire transaction.

Isolation Levels and Concurrency

When multiple clients access the database simultaneously, isolation levels determine how much they can interfere. PostgreSQL supports four:

Level	Dirty Reads	Non-repeatable Reads	Phantom Reads
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED (default)	No	Possible	Possible
REPEATABLE READ	No	No	Possible
SERIALIZABLE	No	No	No

Higher isolation means stricter consistency but lower concurrency. Here's what each anomaly means:

• Dirty Reads: Transaction A sees uncommitted changes from Transaction B.
• Non-repeatable Reads: Transaction A queries a row, Transaction B updates it, Transaction A queries the same row and gets a different result within the same transaction.
• Phantom Reads: Transaction A queries rows matching a condition, Transaction B inserts new matching rows, Transaction A re-queries and finds new rows.

Set isolation level on the connection:

import psycopg

conn = psycopg.connect('postgresql://user:pass@localhost/myapp')

# Set to SERIALIZABLE for the strictest guarantee
cursor = conn.cursor()
cursor.execute('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE')

# Now all queries in this transaction are serializable
cursor.execute('SELECT COUNT(*) FROM orders')
count = cursor.fetchone()[0]

cursor.execute('INSERT INTO orders (customer_id, total) VALUES (%s, %s)', (123, 99.99))
conn.commit()

For most applications, READ COMMITTED (the default) is sufficient. SERIALIZABLE is used for critical financial operations or when you need absolute consistency guarantees.

Context Managers: Safe Transaction Handling

Python's with statement ensures transactions are committed or rolled back properly:

import sqlite3

conn = sqlite3.connect('app.db')

try:
    with conn:  # Automatically commits on success, rolls back on exception
        cursor = conn.cursor()
        cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)',
                       ('Alice', '[email protected]'))
        cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)',
                       ('Bob', '[email protected]'))
    print("Both users inserted")

except sqlite3.IntegrityError as e:
    print(f"Insert failed (constraint violation): {e}")

finally:
    conn.close()

The with conn: block automatically commits on success and rolls back if an exception is raised. This is the safest pattern for Python database code.

Comparison: Transaction Patterns

Pattern	Safety	Readability	Use Case
Manual commit/rollback	Medium	Medium	Legacy code, fine-grained control
with statement	High	High	Most applications
Autocommit	Low	Medium	Logging, write-once operations
ORM (SQLAlchemy)	High	High	Web frameworks, complex queries

Key Takeaways

• A transaction is all-or-nothing: all statements succeed or all roll back.
• Always wrap multi-statement operations in a transaction and call commit().
• Use rollback() if an error occurs or a condition is not met.
• Isolation levels control how concurrent transactions interfere; READ COMMITTED is the default.
• Use with statements or context managers to ensure transactions are handled safely.
• Savepoints allow rolling back parts of a transaction without losing the whole transaction.

Frequently Asked Questions

Does a single INSERT statement need a commit?

Yes. Even a single INSERT is a transaction, and you must call commit() to persist it. However, some ORMs (like SQLAlchemy) handle this automatically.

What is the difference between commit and rollback?

commit() persists all changes from the transaction to disk. rollback() discards all changes, as if the transaction never happened. Once you call commit(), you cannot rollback.

Can I commit in the middle of a transaction?

No. A transaction is atomic—it either fully commits or fully rolls back. If you need to commit some changes and continue with new operations, start a new transaction after the commit.

What happens if my program crashes before calling commit?

All changes are rolled back. The database is left in the state it was before the transaction started. This is the "Durability" in ACID—committed data survives crashes; uncommitted data does not.

Should I use SERIALIZABLE isolation level for all transactions?

No, it has performance costs. Use it only for critical operations (financial transfers, stock trades). For most web applications, READ COMMITTED is sufficient. Test your application under realistic load to choose the right level.

Can I set isolation level per-transaction or only per-connection?

Both. Most adapters let you set it per-transaction after opening the connection. PostgreSQL lets you set it with SET TRANSACTION ISOLATION LEVEL ... before the transaction starts.

Further Reading

• PostgreSQL Transaction Documentation
• SQLite Transactions
• ACID Properties Explained
• Isolation Levels and Anomalies