SQLAlchemy Transactions & Session Management

 

Introduction

When working with databases, data safety and consistency are critical.That’s where transactions and session management come in.

In this blog, you’ll learn:

• What transactions are
• How SQLAlchemy sessions work
• Commit, rollback, flush
• Real-world best practices
• Common production mistakes

What is a Transaction?

A transaction is a group of operations executed as a single unit.It follows ACID principles:

• Atomicity → All or nothing
• Consistency → Valid state
• Isolation → Independent execution
• Durability → Changes are permanent

Example

session.add(user1)
session.add(user2)
session.commit()

Either both inserts succeed OR none.

What is a Session in SQLAlchemy?

A Session is:

• A workspace for interacting with the database
• Responsible for:
  • Managing transactions
  • Tracking object changes
  • Executing queries

Session Lifecycle

Create Session → Perform Operations → Commit/Rollback → Close Session

Create Session
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine("sqlite:///example.db")

Session = sessionmaker(bind=engine)
session = Session()

Core Transaction Operations
1. Commit
session.add(user)
session.commit()
Saves changes permanently
2. Rollback
session.rollback()
Reverts changes if error occurs
3. Flush
session.add(user)
session.flush()
Sends data to DB without committing
Why Flush?
Get auto-generated IDs before commit
Validate constraints early
session.add(user)
session.flush()

print(user.id)  # ID available before commit

Transaction Handling
Safe Pattern
try:
    session.add(user)
    session.commit()
except Exception as e:
    session.rollback()
    print("Error:", e)
finally:
    session.close()
Cleaner Approach (Context Manager)
from contextlib import contextmanager

@contextmanager
def get_session():
    session = Session()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


Session States
SQLAlchemy tracks object states:
State
Meaning
Transient
          Not in DB
Pending
          Added but not committed
Persistent
          Saved in DB
Detached
          Session closed
Example
user = User(name="John")  # Transient
session.add(user)         # Pending
session.commit()          # Persistent
session.close()
# user becomes Detached

Autoflush & Autocommit
Autoflush
Session = sessionmaker(bind=engine, autoflush=True)
Automatically flushes before queries
Autocommit
Avoid using — always manage transactions manually
Scoped Sessions
Problem
In web apps, multiple users → multiple sessions needed
Solution: scoped_session
from sqlalchemy.orm import scoped_session

Session = scoped_session(sessionmaker(bind=engine))
session = Session()
Each request gets its own session
FastAPI Integration (Real-World)
from fastapi import Depends
def get_db():
    db = Session()
    try:
        yield db
    finally:
        db.close()
@app.get("/users")
def get_users(db: Session = Depends(get_db)):
    return db.query(User).all()


Common Mistakes
Forgetting rollback() on error
Keeping sessions open too long
Using global session in web apps
Not closing sessions
Ignoring flush behavior
Real-World Scenario
Banking Example
try:
    account1.balance -= 100
    account2.balance += 100
    
    session.commit()
except:
    session.rollback()
Prevents partial updates (very critical!)
Production Best Practices
Use short-lived sessions
Always handle exceptions
Use context managers
Avoid global sessions
Use scoped_session in web apps
Session = DB interaction layer
Transactions ensure data integrity
Commit saves, rollback reverts
Flush sends data without commit
Proper session handling is critical in production