Transactions, isolation anomalies and ORM boundaries

You can now

Explain commit, rollback, savepoints and the PostgreSQL isolation anomaly table, including where PostgreSQL exceeds the standard.
Demonstrate one isolation anomaly or prevented anomaly on the live database at a named isolation level with the interleaving written out.
Draw the ORM transaction boundary with Session, identity map and eager loading, and fix one \(N+1\) with selectinload.

Covers

Transactions, isolation anomalies and concurrency-sensitive invariants
ORM identity maps, lazy loading and transaction boundaries

Study, in this order

10 steps

What Is a Transaction
AlgoMaster SQL - selected route

BEGIN, COMMIT, and ROLLBACK; Auto-Commit Mode; SAVEPOINT and Partial Rollback; Common Mistakes (Forgetting to COMMIT or ROLLBACK, Long-Running Transactions)

Done
ACID Properties
AlgoMaster SQL - selected route

Atomicity; Consistency; Isolation; Durability with Write-Ahead Logging (WAL) and What Durability Does Not Cover

Done
PostgreSQL 3.4 Transactions
PostgreSQL transactions and EXPLAIN

The bank transfer wrapped in BEGIN and COMMIT, then the SAVEPOINT and ROLLBACK TO example

Done
Isolation Levels
AlgoMaster SQL - selected route

Read Phenomena; The Four Isolation Levels; PostgreSQL vs MySQL Defaults; Choosing the Right Level

Done
PostgreSQL 13.2 Transaction Isolation
PostgreSQL transactions and EXPLAIN

Table 13.1 Transaction Isolation Levels (READ UNCOMMITTED behaves as READ COMMITTED, and REPEATABLE READ allows no phantom reads); 13.2.2 Repeatable Read Isolation Level (the snapshot taken at the first non-transaction-control statement, and could not serialize access due to concurrent update)

Done
Locking Mechanisms
AlgoMaster SQL - selected route

Lock Granularity: Row-Level vs Table-Level; Explicit Row Locking: SELECT FOR UPDATE; NOWAIT and SKIP LOCKED; Pessimistic vs Optimistic Locking

Done
Deadlocks
AlgoMaster SQL - selected route

How Deadlocks Form; Deadlock Detection (PostgreSQL); Prevention: Consistent Lock Ordering

Done
MVCC
AlgoMaster SQL - selected route

PostgreSQL's Approach: Heap-Based MVCC (xmin and xmax, Row Visibility Rules, Dead Tuples and VACUUM); Snapshots and Isolation Levels; Why MVCC Doesn't Prevent Lost Updates

Done
SQLAlchemy `Session` Basics
SQLAlchemy 2.1 documentation - select, Session and eager loading

What does the Session do?; Framing out a begin / commit / rollback block; Flushing; Expiring / Refreshing; Is the Session a cache?

Done
SQLAlchemy Relationship Loading Techniques
SQLAlchemy 2.1 documentation - select, Session and eager loading

Lazy Loading and Preventing unwanted lazy loads using raiseload; Joined Eager Loading; Select IN loading; What Kind of Loading to Use?

Done

Focus on

PostgreSQL has three real levels: READ UNCOMMITTED behaves as READ COMMITTED, and REPEATABLE READ blocks phantom reads, which is stronger than the standard requires.
REPEATABLE READ takes its snapshot at the first non-transaction-control statement. An UPDATE of a row that another transaction changed and committed since then raises could not serialize access (SQLSTATE 40001), and the application retries the whole transaction.
The Session is the ORM transaction boundary plus an identity map: one object per primary key. Touching lazy relationships after commit issues new SELECTs or fails outside a session.
SAVEPOINT discards part of a transaction while keeping the rest. Forgetting COMMIT leaves an idle transaction that holds locks and bloats the database.

Reproduce closed-book

Closed-book, run the two-session interleaving on PostgreSQL 18: session A begins REPEATABLE READ and reads balance 100; session B updates to 200 and commits; session A re-reads (still 100), commits, and reads 200. State the isolation level, the anomaly prevented, and the retry rule.

Traps

REPEATABLE READ equals serializable. It does not: serialization anomalies are still possible, and only SERIALIZABLE plus retry logic on SQLSTATE 40001 prevents them.
No retry loop on serialization failures. Concurrent writers make these routine, not exceptional; abort and rerun the whole transaction.
Touching relationships after session close is free. Lazy loading fires one SELECT per parent (\(N+1\)) inside a session and fails outside it; use selectinload or joinedload and guard with raiseload.

Practice

PostgreSQL tutorial transactions: bank transfer with savepoints

Section 3.4: the transfer with BEGIN, COMMIT and ROLLBACK plus the savepoint example

Replay on a scratch PostgreSQL 18 database (a throwaway postgres:18 container works): wrap a three-statement transfer with one bad statement, roll back, and show balances unchanged. Then run the reproduce interleaving, record both reads, and have session A update the row to see the 40001 error.

Self-test

In the reproduce interleaving, what does session A read second, and why?
Which cells of Table 13.1 differ between the SQL standard and PostgreSQL?
A REPEATABLE READ update raises could not serialize access due to concurrent update. What does the application do?
Loading \(N\) parents then touching children issues how many statements under lazy loading, and after selectinload?

Leads to

4.6 Indexes, selectivity and query plans Measure the plans behind the invariant checks.
4.8 HTTP APIs, pagination, streaming and idempotency Retried requests need transaction boundaries that make replay safe.

Comes after

4.4 Relational modeling, joins, aggregation and window functionsGuard the new schema with transactions and a retry policy.

Resources for this unit

AlgoMaster SQL - selected route
AlgoMasterrequiredcourse
Study SQL Foundations, Querying Data, Joins, Subqueries and CTEs, Window Functions, DDL, DML and Views, Performance Optimization, Transactions and Concurrency, and Data Modeling. Stop when the service queries and transaction tests pass.
PostgreSQL transactions and EXPLAIN
PostgreSQLrequiredreference
Use the transaction tutorial and EXPLAIN guide to demonstrate commit, rollback, one isolation anomaly or prevented anomaly, and a measured plan improvement on the database actually used.
SQLAlchemy 2.1 documentation - select, Session and eager loading
SQLAlchemyreferencereference
Use the select() tutorial for 2.x query style, Session basics for the transaction boundary and identity map, and relationship loading for lazy versus selectinload versus joinedload plus raiseload. Use select() style and Session.execute or Session.scalars only, never the legacy Query API.

Also in this section

Official docs typed-service build path
Python typing, Pydantic, FastAPI and PostgreSQLprimarycourse
Build one record service in this order: structural protocols and generics; Pydantic models, validators, strict mode, serialization and settings; FastAPI request bodies, dependencies, errors and tests; then PostgreSQL transactions and EXPLAIN. Replay valid, malformed, duplicate and previous-schema requests through CLI and HTTP.
FastAPI tutorial
FastAPIsupportimplementation
Use Request Body, Dependencies, Handling Errors and Testing to expose the same service layer through HTTP. Keep framework models outside the domain core and preserve the same error semantics as the CLI.
AlgoMaster Low-Level Design - one rehearsal
AlgoMastersupportpractice
Use one fitting design prompt to rehearse interface contracts, dependency direction and failure ownership. Treat the review as feedback, then verify behavior with your own tests.
Stas' Python Cookbook - data and interfaces
Stas Bekmansupportreference
Use Serialization and Deserialization plus Environment and Arguments for focused implementation help. Compare JSON, JSONL, CSV and compressed data, and never use pickle for untrusted interchange.
AlgoMaster Python - interfaces, generics and data files
AlgoMastersupportcourse
Use Interfaces and Protocols plus Generic Types for structural typing, and Working with JSON plus Working with CSV for JSONL, error handling, newline and DictReader behavior. Lesson quizzes check recall; implementation is verified with your own scripts.
Alembic tutorial - migration scripts and downgrading
SQLAlchemysupportreference
Use Create a Migration Script, Running our First and Second Migration, Getting Information and Downgrading to run a paired upgrade and downgrade round trip on a scratch database.
PyArrow Parquet read and write docs
Apache Arrowsupportreference
Use Reading and Writing Single Files plus Table from_pydict and from_pylist to convert normalized records to Parquet and test types, nulls, column projection and schema.
Stripe idempotent requests
Stripesupportreference
Use the Idempotency-Key header contract, stored-response replay, parameter-mismatch errors and POST-only scope to implement keyed replay for one endpoint.