Transactions and Isolation Levels

1. First screen — every statement can be valid while the concurrent outcome is wrong

Assume one unit of an inventory item remains. Transaction T1 reads remaining = 1. At nearly the same time T2 reads the same state. In a single-session test, both pieces of code can look reasonable. Under concurrency, however, the system can violate its business invariant if the transaction boundary, snapshot visibility, and retry rule are implicit.

This lesson is not a vocabulary tour of isolation labels. The completion artifact is Transaction Isolation Evidence Record v1. For one bounded scenario, you must record the BEGIN to COMMIT/ROLLBACK boundary, isolation level, prediction made before execution, real observation from each session, any failure such as SQLSTATE 40001, the retry decision, the protected invariant, and one residual limitation.

The scope is deliberately narrow. Detailed lock modes, deadlock handling, and optimistic concurrency belong to the next lesson. A database rollback is also not treated as a magical rollback of an email, message, file write, or other external side effect.

2. Route orientation — support changes, the evidence bar does not

The Foundation route receives an annotated two-session timeline and a guided first probe. The Repair route reruns only the concurrency misconception that failed. The Experienced route may compress syntax review, but it may not remove the prediction, deliberate failure, retry rationale, or independent transfer.

Before each probe, write three statements: the invariant, what T1 and T2 are expected to observe at each step, and the observation that would falsify the prediction. Keeping only a successful run is insufficient evidence. A controlled failure is part of the learning record.

3. Mental model — transaction boundary, snapshot, invariant, and retry are separate decisions

A transaction boundary says which database operations are committed or rolled back as one unit. The boundary should correspond to the business operation being protected, not merely to convenient code structure.

A snapshot/visibility rule determines which committed versions a statement or transaction can observe. Visibility is not the same thing as atomicity.

An invariant is the condition that must remain true, such as reserved_qty <= capacity. Without an invariant, choosing an isolation level is only choosing a label.

A retry boundary defines what is rerun after a concurrency failure. PostgreSQL serialization failures require the transaction logic to be rerun from the beginning with fresh observations. Retrying only the final failed statement can preserve a decision that was made from stale assumptions. External side effects need their own idempotency or recovery design.

The visual at asset://DAT-003/DAT-003-A1 uses the same reasoning chain: T1/T2 → BEGIN → snapshot point → read/write → concurrent COMMIT → second observation → COMMIT or serialization failure → whole-transaction retry.

4. Foundation layer — five contracts that must stay precise

A transaction is a database unit, not an ACID incantation

BEGIN and COMMIT group database statements into a unit; ROLLBACK discards the database changes of that transaction. This alone does not prove that the right business invariant was protected, nor does it reverse arbitrary side effects outside the database.

PostgreSQL Read Committed is not one transaction-wide snapshot for ordinary SELECTs

Frozen source PG18-ISO documents Read Committed as the default PostgreSQL 18 isolation level. An ordinary SELECT sees a snapshot as of the start of that statement. Consequently, if T2 commits between two SELECT statements in T1, the second statement can observe newer committed data. This claim is bounded to PostgreSQL 18 and must not be generalized to every database product.

PostgreSQL Repeatable Read keeps a stable transaction snapshot

Under PostgreSQL 18 Repeatable Read, the transaction reasons from a stable snapshot. In the bounded schedule, T1 continues to see a view consistent with its earlier snapshot after T2 commits. If T1 then attempts to update a row that changed after its snapshot, a restart can be required. Do not assume another database implements Repeatable Read with identical semantics.

Serializable protects serializability, not first-attempt success

PostgreSQL Serializable aims for committed effects that are consistent with some serial execution. To maintain that guarantee, it may abort a transaction. A SQLSTATE 40001 serialization failure is therefore expected evidence in some schedules, not proof that the database malfunctioned. PG18-SERFAIL is the frozen authority for the whole-transaction retry boundary used here.

Do not infer PostgreSQL behavior from the READ UNCOMMITTED label

PG18-SETTX records that PostgreSQL treats READ UNCOMMITTED with Read Committed behavior. The lab verifies behavior rather than accepting an SQL-standard label as evidence.

5. Deep explanation — solve a two-session schedule before running it

Start with capacity = 1 and reserved_qty = 0.

Under Read Committed:

-- T1
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT capacity - reserved_qty AS remaining FROM inventory WHERE item_id = 1;
-- T2 updates and COMMITs here
SELECT capacity - reserved_qty AS remaining FROM inventory WHERE item_id = 1;
COMMIT;

If T2 changes reserved_qty to 1 and commits between the two SELECT statements, the bounded prediction is that T1 can see 1 first and 0 second. The point is not that Read Committed is inherently wrong. The point is that two observations inside one transaction are not automatically one stable transaction snapshot.

Repeat the schedule under Repeatable Read. The second read should remain consistent with T1's transaction snapshot. Then have T1 attempt a conflicting update against the row changed by T2. Capture the actual transaction result and SQLSTATE instead of narrating an expected error from memory.

For Serializable, the fixture adds a cross-row invariant: at most one active reservation may exist. T1 and T2 both read that no active reservation exists and then attempt different reservation rows through the same capacity guard. A world in which both original transactions commit would violate the invariant and cannot be explained as the intended serial decision rule. The fixture is designed so that one original transaction should need a restart under PostgreSQL Serializable. Crucially, retry means rerunning the whole transaction from BEGIN and re-evaluating the guard; after the competing transaction commits, the retry must observe the new state and decline a second reservation rather than blindly repeating the final INSERT. That statement is a test oracle, not authoring-build runtime evidence; the learner must execute the schedule and record what actually occurs.

6. Worked example — move from a single-session inventory flow to a defensible policy

lab://DAT-003/DAT003-LAB1 contains three bounded probes. First, a single-transaction baseline confirms the fixture. Second, the visibility probe is executed under Read Committed and Repeatable Read. Third, the Serializable conflict probe preserves the controlled failure and then performs a whole-transaction retry.

Use this evidence sequence:

1. State the invariant before writing concurrency SQL.
2. Mark the transaction boundary. Reads that drive a write decision must have a defensible relationship to that write.
3. Record the T1/T2 prediction before execution.
4. Save real session output. If no PostgreSQL runtime is available, leave runtime fields unfilled.
5. If 40001 occurs, retain it as evidence and retry the whole failed transaction from BEGIN; do not retry only the final statement.
6. Confirm that the retry re-evaluates the capacity guard, then recheck active reservations <= capacity after retry.
7. State a residual limitation, for example: “This fixture demonstrates bounded PostgreSQL 18 behavior; it does not establish production throughput or universal DBMS semantics.”

The objective is not “always choose the strongest level.” If the invariant can be protected with a simpler atomic statement or a database constraint, that alternative belongs in the decision record. Detailed locking design is deferred to DAT-004.

7. Failure modes — five plausible shortcuts that are still wrong

Failure A: “Serializable means retries are no longer necessary”

Serializable can preserve correctness by aborting a transaction. A design with no retry path is incomplete when serialization failure is possible.

Failure B: “Repeatable Read means the same thing in every database”

This lesson claims PostgreSQL 18 behavior only. Do not copy the implementation semantics to another DBMS from the label alone.

Failure C: “Read Committed gives all SELECTs in my transaction one snapshot”

PostgreSQL ordinary SELECT statements under Read Committed use statement-level snapshots. The two-SELECT schedule exists specifically to falsify this misconception.

Failure D: “ROLLBACK undoes the email or message I already sent”

Database rollback controls database state in that transaction. External effects require separate idempotency, outbox, or recovery design. Those patterns are not completed in this lesson.

Failure E: “A stronger isolation level replaces a business invariant”

If you cannot state what must remain true and what observation would violate it, Serializable is a configuration change rather than an engineering argument.

8. Experienced bridge — compress syntax, never compress evidence

If ACID and PostgreSQL transaction syntax are already familiar, skip the BEGIN/COMMIT walkthrough. Four outputs remain mandatory: a two-session timeline, prediction before execution, one real conflict/failure, and a policy decision with residual risk.

Use the compact matrix directly: Session | Transaction Boundary | Isolation Level | Statement | Predicted Visibility | Observed Visibility or SQLSTATE | Retry Decision | Protected Invariant | Residual Risk. Any cell that requires runtime evidence must be populated from actual output, not from this lesson's explanation.

9. Alternatives and trade-offs — start from the invariant, not the label

Read Committed is appropriate for many workloads when the sensitive decision is protected by an atomic statement or another explicit mechanism. It usually produces fewer serialization restarts, but application code must not assume transaction-wide snapshot stability that PostgreSQL does not provide at this level.

Repeatable Read gives a stable PostgreSQL transaction snapshot and is useful when repeated reads must remain consistent. The application still needs a restart path for serialization failures, and the semantics are product-specific.

Serializable provides the strongest isolation contract among these levels for preventing serialization anomalies, but transaction restart is a normal part of the programming model. Performance and contention remain empirical questions rather than conclusions from the isolation label.

Explicit locking or optimistic concurrency are legitimate alternatives, but exact lock modes, deadlock reasoning, and version-column policies are owned by DAT-004. Here, record when such a mechanism may be required and why; do not expand the lesson into its implementation.

10. Guided and independent practice

Guided pass

Complete the Read Committed prediction table before execution: T1 first read, T2 update and commit, T1 second read. Run the lab and place the observation beside the prediction. If the result differs, inspect the actual session ordering and PostgreSQL version before changing the theory.

Repeat the schedule with Repeatable Read. Then force T1 to write a row that T2 changed after T1's snapshot. Record where the transaction fails and what must be rerun. Memorizing an error string is not sufficient evidence.

Independent pass

Run the Serializable reservation schedule without the guided prediction hints. State the invariant before the second session starts. Record both session outcomes, SQLSTATE if a failure occurs, the whole-transaction retry, and the post-retry invariant check. If both conflicting transactions commit, do not mark the lab as passed; investigate the schedule because the observation conflicts with the bounded oracle.

11. Verification and evidence — keep prediction, observation, inference, and decision distinct

Transaction Isolation Evidence Record v1 has five critical dimensions: transaction_boundary, read_committed_snapshot, repeatable_read_snapshot, serializable_retry, and isolation_decision.

For each dimension, classify statements correctly:

• Prediction: what you expect before execution and why.
• Observation: the actual row value, stdout/stderr, or SQLSTATE.
• Inference: what the observation supports about the bounded contract.
• Decision: the isolation/retry policy chosen for the stated invariant.

A complete record includes at least one positive observation, one deliberate failure, a repair or retry result, independent changed-context transfer, and one residual limitation. The authoring build does not have a PostgreSQL runtime, so fields marked TO_FILL_FROM_REAL_RUNTIME intentionally remain empty of fabricated evidence.

12. Assessment and transfer — solve seat allocation without copying the inventory SQL

The transfer domain is a seat-allocation service that reads remaining capacity and writes a reservation. Define exactly where the transaction starts and ends, state the invariant, choose an isolation/retry policy, and identify the concurrent observation that would falsify your choice.

“Serializable because it is stronger” is not sufficient. “Read Committed because it is faster” is also unsupported without evidence. The decision must trace from invariant to schedule to observed behavior to retry boundary.

Three failures are blocking: an application task with no real concurrent evidence, a diagnosis with no deliberate failure, and a transfer answer whose decisive policy is copied from scaffold rather than reasoned independently.

13. References and freshness boundary

This candidate uses only the frozen source boundary:

• PG18-ISO — PostgreSQL 18 transaction isolation, snapshots, and implemented isolation behavior; retrieved 2026-09-02, review due 2026-12-31.
• PG18-SERFAIL — PostgreSQL 18 serialization/deadlock retry guidance and SQLSTATE identifiers; retrieved 2026-09-02, review due 2026-12-31.
• PG18-SETTX — current SET TRANSACTION syntax and PostgreSQL handling of READ UNCOMMITTED; retrieved 2026-09-02, review due 2026-10-17.

As of authoring on 2026-09-15, no review date has been reached, so the source scope is not expanded. The lesson describes bounded PostgreSQL 18 behavior, not a universal rule for all databases. Learner evidence demonstrates only the disposable fixture that was actually run; it does not prove production correctness or performance across workloads.