ACID Transactions and MVCC

Transactions group changes into an atomic unit, while PostgreSQL’s multi-version concurrency control lets statements see a consistent snapshot without readers normally blocking writers. ACID is not a promise that arbitrary business logic is correct: constraints, isolation choice, locking, retry behavior, and short transaction boundaries determine which concurrent states are actually protected.

What will you be able to do?

• Explain atomicity, consistency, isolation, and durability precisely.
• Describe snapshots and row versions at a useful level.
• Use transactions and savepoints safely.
• Identify long-transaction harm.

Prerequisites and mental model

An update creates a new row version. Each transaction sees versions visible to its snapshot. Old versions remain until no required snapshot needs them and vacuum can reclaim them.

How does a transaction protect a workflow?

Invariant: closing a ticket and recording the closing audit event happen together.

Application code must verify the update returned one row before inserting/committing. Atomicity prevents half the transaction from persisting. Consistency means declared invariants hold, not that unstated rules become true. Isolation controls concurrent visibility. Durability concerns committed state surviving failures under the configured guarantees.

What does MVCC change?

Ordinary SELECT reads a snapshot and usually does not block an updater. Two sessions can still race when both read a condition and then write based on it. MVCC reduces contention; it does not eliminate lost updates, write skew, deadlocks, or the need for correct isolation.

Long transactions retain snapshots, delay cleanup, increase bloat and replica conflicts, and hold locks. Never keep a transaction open while waiting for a person or model response.

Why does this matter in AI-native systems?

Model latency is variable and may take seconds. Prepare context outside a write transaction; after the model responds, revalidate versions and permissions in a short transaction before applying a bounded result. Store the model proposal separately when approval is asynchronous.

Prove it worked

Use two psql sessions. Begin a transaction in session A and read a ticket. Update/commit it in B. Read again in A under the default isolation and record behavior. Repeat under repeatable read. Observe snapshots; do not generalize before the isolation chapter.

Production judgment

• Transactions need timeouts and failure retries at safe boundaries.
• External side effects cannot be rolled back by PostgreSQL; use outbox/idempotency patterns.
• “Consistency” includes only encoded and correctly transacted invariants.
• Idle-in-transaction sessions are operational defects.

Common mistakes

• Transaction open during API call: boundary too broad → split proposal and short commit transaction.
• Partial external side effect: message sent before DB rollback → use transactional outbox.
• Reader assumed latest data: snapshot semantics ignored → choose isolation/read path explicitly.

AI Pair-Programmer Prompt

Hands-on exercise

Design an AI reply approval workflow with proposal, human approval, and message publish. Mark transaction boundaries and external effects.

Acceptance criterion: no transaction spans model/human wait, stale approval is detected, and duplicate publishing is prevented.

Knowledge check

1. Does MVCC eliminate write races?
2. What can long transactions prevent?
3. Can PostgreSQL roll back an email already sent?

---

Answers

1. No; it provides versioned visibility but concurrency controls are still required.
2. Timely vacuum cleanup and removal of old row versions; they can also retain locks/resources.
3. No; coordinate external effects with outbox and idempotency patterns.

Completion checklist

• Each transaction protects a named invariant.
• Remote waits are outside transactions.
• Snapshot behavior is tested with two sessions.
• External effects have idempotent coordination.

Primary references

• Transactions
• MVCC introduction
• Transaction isolation