Unit of Work and Identity Map

The two concepts behind “session.add + session.commit just works” magic. Without them, you’d write INSERT/UPDATE/DELETE per attribute change. The price: instance lifecycle states you need to understand.

Identity Map — one row, one object

Within a Session, every loaded row maps to one Python instance, keyed by primary key:

with Session(engine) as session:
    a = session.get(User, 42)
    b = session.get(User, 42)
    assert a is b      # same object

The first get() fetches from DB; the second returns the cached instance. No second query.

Same applies to query results:

a = session.scalars(select(User).where(User.id == 42)).one()
b = session.get(User, 42)
assert a is b      # same instance from the identity map

The identity map is per session. Two sessions have two separate maps and two separate instances.

with Session(engine) as s1, Session(engine) as s2:
    a = s1.get(User, 42)
    b = s2.get(User, 42)
    assert a is not b

Unit of Work — batch changes

You don’t write INSERTs/UPDATEs/DELETEs manually. You mutate objects; the Session tracks them and emits SQL on flush()/commit().

with Session(engine) as session:
    user = User(name="alice")
    session.add(user)              # marks user as pending

    other = session.get(User, 7)
    other.email = "x@y.com"        # marks other as dirty

    session.delete(other)           # marks other as deleted (instead of dirty)

    session.commit()
    # SQL flushed in dependency order:
    # INSERT INTO users (name) VALUES ('alice')
    # DELETE FROM users WHERE id = 7

The Session figures out:

• Which objects are new (INSERT).
• Which are modified (UPDATE).
• Which are deleted (DELETE).
• The right order (parent before child, etc.).
• One SQL per change (or fewer, via batching).

Object states

Every mapped instance is in one of these states:

user = User(name="alice")                # transient
session.add(user); # pending
session.flush()                          # persistent
session.commit()                         # still persistent (committed)
session.close()                          # detached

session.expunge(user) removes from session without deleting — useful for “send this object back to caller after closing the session.”

inspect(user).persistent, .pending, .detached, .transient, .deleted lets you check programmatically.

flush vs commit

flush: send pending changes to the DB as SQL statements. No COMMIT yet — DB rolls back if the transaction errors.

commit: flush (if not already flushed) + COMMIT the DB transaction. Changes are durable.

Why distinguish? flush lets you see DB-side effects (auto IDs, server defaults) without committing:

user = User(name="alice")
session.add(user)
session.flush()             # INSERT issued; user.id is now populated
print(user.id)              # e.g. 42
# but: not committed yet — visible only inside this transaction
session.commit()

autoflush=True (the default) automatically flushes before queries, so session.query sees your pending changes. You rarely call flush() manually unless you need the auto-generated PK before commit.

What gets tracked as “dirty”?

By default SQLAlchemy uses attribute-level instrumentation to detect mutations:

user = session.get(User, 1)
user.name = "new name"     # ← attribute access setter records the change
# user is now "dirty"; commit will UPDATE

This works for direct attribute writes. It does NOT track:

• In-place mutations of mutable column values (user.tags.append("x") on a JSON list column).
• Mutations to dict/list-typed columns (Postgres JSON, ARRAY).

For mutable JSON/ARRAY columns:

from sqlalchemy.ext.mutable import MutableDict, MutableList
from sqlalchemy.dialects.postgresql import JSONB

class Thing(Base):
    metadata_: Mapped[dict] = mapped_column("metadata", MutableDict.as_mutable(JSONB))

Now thing.metadata_["k"] = "v" is tracked. Without Mutable*.as_mutable, in-place changes are invisible — the next commit doesn’t UPDATE.

expire_on_commit — what happens to objects after commit

Default behavior: after commit(), all loaded attributes are expired (marked stale). Next access re-fetches from DB.

session.commit()
print(user.name)    # ← issues SELECT to refresh

Why: the data may have changed (other transactions committed); SQLAlchemy is being conservative.

Cost: an N+1 of SELECTs if you access many users after commit. Disable per-session:

session = Session(engine, expire_on_commit=False)
# or globally via sessionmaker
SessionLocal = sessionmaker(bind=engine, expire_on_commit=False)

Now objects keep their attribute values after commit. Read-mostly apps and “serialize the user then return” patterns benefit.

Trade-off: if another transaction updates the row, your in-memory copy is stale until you session.refresh(user).

refresh and expire

session.refresh(user)              # SELECT and update in-memory attrs
session.expire(user)               # mark expired; next access SELECTs
session.expire(user, ["name"])     # only expire specific attrs
session.expire_all()               # expire every object in session

refresh is “load now”; expire is “load on next access.” Use refresh when you specifically want fresh data (e.g. after another component may have committed); use expire when you want the future-load behavior.

merge — re-attach an instance

detached_user = ...     # from another session, or constructed manually
merged = session.merge(detached_user)
# merged is now persistent in this session
# detached_user is unchanged

merge:

1. Looks up the instance in the identity map by PK.
2. If present: copies the detached instance’s attributes onto the persistent one.
3. If not: loads from DB (or creates pending if PK doesn’t exist).
4. Returns the session-resident instance.

Use case: caching, deserialization, or anywhere you have an object from outside the session.

When two sessions touch the same row

Each session has its own identity map and its own transaction. Two sessions both reading row 42:

session1.get(User, 42)   → SELECT, instance A in s1's map
session2.get(User, 42)   → SELECT, instance B in s2's map

session1: a.name = "alice2"; commit()
session2: print(b.name)     # still "alice" (s2's snapshot)

This is correct behavior — each session sees a consistent view (at least at READ COMMITTED). To see s1’s change in s2: refresh or end and re-open the s2 transaction.

session.add() vs merge() vs update()

For 99% of write workflows: load with session.get or query, mutate attributes, commit().

Common pitfalls

• Modifying a JSON column in-place without MutableDict.as_mutable — change isn’t detected, no UPDATE issued.
• Accessing attributes after commit() with expire_on_commit=True — re-issues SELECT per attribute (sometimes per object). Disable expire_on_commit or refresh once.
• Using one detached instance across multiple sessions — lazy loads fail; merge into the new session first.
• session.add() on an already-persistent instance from another session — error, or unexpected behavior. Use merge().
• Holding references to instances after session.close() — they’re detached; any unloaded attribute access fails with DetachedInstanceError.

Common interview confusions

• “Identity map is shared across sessions.” — per-session. Two sessions = two instances for the same row.
• “commit always issues all SQL.” — it flushes (if not done) and COMMITs. The flushing happens during commit (or earlier on autoflush).
• “flush() is the same as commit().” — flush sends SQL, commit also commits the transaction. After flush you can still rollback; after commit it’s durable.

Interview angle

• “What’s the identity map?” — per-session cache mapping primary key to instance. Within a session, loading the same row twice returns the same Python object.
• “What’s Unit of Work?” — the session batches pending/dirty/deleted instances; on flush/commit it emits the right INSERT/UPDATE/DELETE statements in the right order. You mutate objects, the session handles SQL.
• “Difference between flush() and commit()?” — flush sends pending SQL to the DB (so auto-generated columns are populated); commit additionally commits the transaction. autoflush makes flush happen automatically before queries.
• “What’s expire_on_commit and when should you disable it?” — default True; after commit all attributes are expired and re-fetched on access. Disable for read-after-write serialization patterns (“return the user then exit”) to avoid extra SELECTs.
• “What are the object states?” — transient (new, no session), pending (added, not flushed), persistent (in session + DB), detached (was persistent, session closed), deleted (marked for DELETE).
• “In-place changes to a JSON column aren’t being saved — why?” — SQLAlchemy attribute tracking is at the assignment level; mutating a dict/list in place doesn’t trigger it. Use MutableDict.as_mutable(JSONB) (or MutableList) for change tracking.
• “What’s session.merge() for?” — copies attributes from a detached/external instance onto the session-resident one (looked up by PK). Used for caching, deserialization, or any cross-session object handoff.