SQL, PostgreSQL Transactions, Isolation Levels & MVCC

Data persistence in enterprise Python systems relies heavily on relational databases (PostgreSQL). Mastering database architecture requires understanding ACID Transaction Guarantees, ANSI SQL Isolation Levels, read anomalies, PostgreSQL MVCC (Multi-Version Concurrency Control), and connection pool sizing (PgBouncer, SQLAlchemy QueuePool).

This chapter details ACID properties, Transaction Isolation levels (Read Committed, Repeatable Read, Serializable), PostgreSQL tuple visibility (xmin/xmax), and connection pooling models.

---

1. ANSI SQL Transaction Isolation Levels & Read Anomalies

Transactions guarantee ACID properties (Atomicity, Consistency, Isolation, Durability). Isolation defines how concurrent transactions view uncommitted or concurrently committed modifications.

Read Anomalies Definitions:

1. Dirty Read:              Transaction A reads uncommitted changes made by Transaction B. (B later rolls back!)
2. Non-Repeatable Read:   Transaction A reads row X. Transaction B mutates row X and commits. A reads X again and sees DIFFERENT values!
3. Phantom Read:           Transaction A queries rows matching a WHERE clause. Transaction B INSERTS new rows matching the clause and commits. A queries again and sees NEW "phantom" rows!
4. Serialization Anomaly:  The result of executing concurrent committed transactions differs from any possible sequential execution order.

ANSI SQL Isolation Level Matrix:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Serialization Anomaly
Read Uncommitted	Allowed	Allowed	Allowed	Allowed
Read Committed (PG Default)	❌ Prevented	Allowed	Allowed	Allowed
Repeatable Read	❌ Prevented	❌ Prevented	❌ Prevented (in PG)	Allowed
Serializable	❌ Prevented	❌ Prevented	❌ Prevented	❌ Prevented

PostgreSQL Detail: In PostgreSQL, Repeatable Read prevents Phantom Reads as well as Non-Repeatable Reads! PostgreSQL uses Serializable Snapshot Isolation (SSI) to enforce true Serializable execution.

---

2. PostgreSQL MVCC (Multi-Version Concurrency Control)

PostgreSQL implements isolation without heavy table locking using MVCC.

Instead of overwriting existing table rows in-place during an UPDATE, PostgreSQL writes a new tuple version to disk and assigns transaction ID metadata (xmin and xmax system columns):

PostgreSQL MVCC Tuple Metadata Architecture:

Heap Page Disk Tuple:
┌────────────────────────────────────────────────────────┐
│ Tuple V1: xmin=100, xmax=105, data={"balance": 100}   │ <-- Superseded by Tx 105
├────────────────────────────────────────────────────────┤
│ Tuple V2: xmin=105, xmax=0,   data={"balance": 150}   │ <-- Active Tuple for Tx > 105!
└────────────────────────────────────────────────────────┘

• xmin: The Transaction ID that inserted/created the tuple version.
• xmax: The Transaction ID that updated or deleted the tuple version (0 if active/un-deleted).

Invariant: Readers Never Block Writers, Writers Never Block Readers!

When Transaction A updates a row, it creates a new tuple version with xmin = A. Transaction B running concurrently under Read Committed reads the older tuple version where xmax is uncommitted or higher than B’s snapshot, continuing without waiting for locks!

---

3. Database Connection Pooling (PgBouncer vs. SQLAlchemy)

Opening a new PostgreSQL connection requires spawning a dedicated OS backend process on the database server, consuming ~2MB-10MB RAM per connection.

Connection Pooling Hierarchy:

[ Python Application Workers (Gunicorn / FastAPI) ] (100 Threads)
                        |
                        v (SQLAlchemy QueuePool: Keeps local pool of TCP handles)
           [ PgBouncer (Transaction Pooling) ]
                        |
                        v (Multiplexes 100 app connections into 10 real DB backend processes!)
             [ PostgreSQL Database Server ]

PgBouncer Pooling Modes:

• Session Pooling: Assigns a server connection for the duration of the client connection.
• Transaction Pooling (Recommended): Assigns a server connection only for the duration of a single transaction! Once committed, the connection returns to the pool instantly.

# ❌ ANTI-PATTERN: Explicit Transaction Isolation Level Missing in Critical Financial Logic
from sqlalchemy.orm import Session

# BAD: Runs under default Read Committed isolation!
# Concurrently executed transactions can read modified balances between statements!
def transfer_funds(session: Session, from_id: int, to_id: int, amount: float):
    acc1 = session.query(Account).filter_by(id=from_id).one()
    acc1.balance -= amount
    acc2 = session.query(Account).filter_by(id=to_id).one()
    acc2.balance += amount
    session.commit()

# ✅ PRODUCTION PATTERN: SELECT FOR UPDATE (Pessimistic Locking)
def transfer_funds_safe(session: Session, from_id: int, to_id: int, amount: float):
    # SAFE: Acquires row-level exclusive write lock (FOR UPDATE), blocking concurrent transfers!
    acc1 = session.query(Account).with_for_update().filter_by(id=from_id).one()
    acc2 = session.query(Account).with_for_update().filter_by(id=to_id).one()
    acc1.balance -= amount
    acc2.balance += amount
    session.commit()