Managing Sessions: SQLAlchemy Unit-of-Work & Transactions

A SQLAlchemy session is an object that manages a conversation with the database and implements the unit-of-work pattern. It tracks changes to objects, issues INSERT/UPDATE/DELETE statements in batches, and supports ACID transactions (atomicity, consistency, isolation, durability). Mastering sessions is critical for data consistency and preventing common bugs.

What is a Session?

A session is a gateway between your Python objects and the database. It tracks every object you create, modify, or delete, and it batches these changes into efficient SQL statements. Sessions are not thread-safe; create one per thread or async task:

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from models import Author, Base

engine = create_engine("sqlite:///library.db")
Base.metadata.create_all(engine)

# Create a new session
session = Session(engine)

# Add, modify, query objects
author = Author(name="Jane Austen")
session.add(author)

# Commit changes to the database
session.commit()

# Close the session
session.close()

The session is a context manager; use with statements to ensure it's closed:

with Session(engine) as session:
    author = Author(name="Jane Austen")
    session.add(author)
    session.commit()
    # session.close() is called automatically

The Unit-of-Work Pattern

The unit-of-work pattern batches all changes and executes them in a single transaction. SQLAlchemy tracks every object and only issues SQL when you call .commit():

with Session(engine) as session:
    # Create multiple objects
    author1 = Author(name="Jane Austen")
    author2 = Author(name="Mary Shelley")
    
    session.add(author1)
    session.add(author2)
    
    # No SQL executed yet—objects are in the session's identity map
    
    session.commit()  # All INSERT statements execute atomically
    
    print(f"Author 1 ID: {author1.id}")  # ID populated after commit
    print(f"Author 2 ID: {author2.id}")

SQLAlchemy doesn't execute SQL until .commit() is called. This batching is efficient and ensures atomicity: all changes succeed or all fail. The identity map is SQLAlchemy's internal registry of objects; each object maps to exactly one database record.

Object States

Every object in a session has a state: transient, pending, persistent, or detached:

with Session(engine) as session:
    # TRANSIENT: created but not added to a session
    author = Author(name="Unknown")  # Not in any session
    
    # PENDING: added to session but not yet committed
    session.add(author)  # Now pending
    
    # PERSISTENT: committed to the database
    session.commit()  # Now persistent, with id assigned
    
# DETACHED: outside the session
print(author.name)  # Still accessible (object in memory)
# But loading lazy relationships will fail (author is detached)

Most bugs in ORMs stem from detached objects. After a session closes, objects become detached. Accessing lazy-loaded relationships on detached objects raises a "DetachedInstanceError". Reattach objects using session.merge():

with Session(engine) as session1:
    author = session1.query(Author).filter_by(id=1).first()
    session1.commit()

# author is now detached
# Accessing a lazy relationship raises an error:
# author.books  # DetachedInstanceError!

with Session(engine) as session2:
    author = session2.merge(author)  # Reattach to session2
    print(author.books)  # Works now

Transactions and ACID Properties

A transaction is a unit of work that's either fully committed or fully rolled back. SQLAlchemy sessions use database transactions to ensure ACID guarantees:

Atomicity: All changes in a transaction succeed or all fail.
Consistency: The database moves from one valid state to another.
Isolation: Concurrent transactions don't interfere.
Durability: Committed changes survive system failures.

with Session(engine) as session:
    try:
        author = Author(name="Jane Austen")
        book = Book(title="Pride and Prejudice", author_id=999)  # Invalid author_id
        
        session.add_all([author, book])
        session.commit()  # Fails due to foreign key constraint
    except Exception as e:
        session.rollback()  # Undo all changes
        print(f"Transaction failed: {e}")

In this example, the foreign key constraint violation causes the transaction to fail. The session.rollback() reverts all changes (including the valid author insertion), and the database returns to its original state.

Implicit vs. Explicit Transactions

By default, SQLAlchemy auto-commits after .commit() and starts a new transaction. For more control, use explicit transactions:

from sqlalchemy import begin

# Implicit transaction (default)
with Session(engine) as session:
    author = Author(name="Jane Austen")
    session.add(author)
    session.commit()  # Auto-commits; new transaction starts on next query

# Explicit transaction (recommended for complex logic)
with Session(engine) as session:
    with session.begin():
        author = Author(name="Jane Austen")
        session.add(author)
        # Committed automatically when exiting the with block

The explicit with session.begin() context manager ensures a transaction is active and commits automatically on successful exit. Use this pattern for complex operations requiring multiple queries.

Flushing vs. Committing

Flushing sends SQL to the database but doesn't commit the transaction. Committing saves the transaction permanently:

with Session(engine) as session:
    author = Author(name="Jane Austen")
    session.add(author)
    
    session.flush()  # SQL is sent; author.id is assigned
    print(f"Author ID: {author.id}")  # ID is now available
    
    # Can still rollback
    session.rollback()  # Reverts the flush
    # Author is still in the session but reverted

.flush() is useful when you need the database to generate an ID (for relationships) but might still roll back. Most of the time, use .commit().

Savepoints (Nested Transactions)

Some databases support savepoints, which are intermediate rollback points within a transaction:

with Session(engine) as session:
    author = Author(name="Jane Austen")
    session.add(author)
    session.commit()
    
    with session.begin_nested():  # Savepoint
        book = Book(title="Pride", author_id=author.id)
        session.add(book)
        session.flush()
        
        if book.title == "Pride":
            session.rollback()  # Rollback only the book, not the author
    
    # Author is committed; book was rolled back

Savepoints (nested transactions) allow partial rollbacks without losing the entire transaction. Not all databases support this.

Session Lifecycle Best Practices

# GOOD: Session per request pattern (web applications)
from fastapi import FastAPI
from sqlalchemy.orm import Session as SessionLocal

app = FastAPI()

def get_db():
    db = SessionLocal()
    try:
        yield db
        db.commit()  # Commit on success
    except Exception:
        db.rollback()  # Rollback on error
    finally:
        db.close()  # Always close

@app.post("/authors")
def create_author(name: str, db: Session = Depends(get_db)):
    author = Author(name=name)
    db.add(author)
    # Session manager commits automatically

# AVOID: Sharing sessions across threads
# Each thread needs its own session

Best practice: one session per request (web applications) or per async task. Create a dependency injection function that creates and manages sessions.

Key Takeaways

Session is a connection pool and object tracker that implements the unit-of-work pattern.
Unit-of-work: Batch changes and execute them atomically in a single transaction on .commit().
Object states: TRANSIENT, PENDING, PERSISTENT, and DETACHED. Detached objects can't access lazy relationships.
Transactions ensure ACID guarantees; use with session.begin() for explicit transaction control.
Flush vs. commit: .flush() sends SQL but doesn't commit; .commit() saves permanently.
One session per thread/task: Sessions are not thread-safe; create isolated sessions for concurrent work.

Frequently Asked Questions

Why does accessing a lazy relationship on a detached object fail?

The session is closed, so SQLAlchemy can't query the database to load the relationship. Reattach using .merge() or eagerly load relationships before detaching.

What's the difference between `.flush()` and `.commit()`?

.flush() sends SQL to the database but doesn't commit the transaction. You can still .rollback(). .commit() finalizes the transaction permanently.

Can I use one session for multiple threads?

No. Sessions are not thread-safe. Each thread needs its own session to avoid race conditions and data corruption.

How do I handle long-running transactions?

Long transactions hold locks and block other users. If you have a long operation, commit intermediate batches:

for batch in batches:
    for obj in batch:
        session.add(obj)
    session.commit()  # Commit after each batch

What happens if I close a session without committing?

Changes are rolled back. The session's identity map is cleared, and objects become detached.

What is a Session?​

The Unit-of-Work Pattern​

Object States​

Transactions and ACID Properties​

Implicit vs. Explicit Transactions​

Flushing vs. Committing​

Savepoints (Nested Transactions)​

Session Lifecycle Best Practices​

Key Takeaways​

Frequently Asked Questions​

Why does accessing a lazy relationship on a detached object fail?​

What's the difference between .flush() and .commit()?​

Can I use one session for multiple threads?​

How do I handle long-running transactions?​

What happens if I close a session without committing?​

Further Reading​