LEARN. PRACTICE. GROW.Write an observable rule
Start with a fictional service holding 100 units and receiving no replenishment during the experiment. The rule is simple: accepted consumption plus remaining balance must still total 100. Two clients read 100 before either writes; each computes 20 and stores that value while also recording 80 accepted. Both write transactions commit. The local result is 160 accepted and 20 remaining, violating the rule. A negative balance is unnecessary to detect the error. The laboratory deliberately reproduces this sequence and considers the test passed when it observes the expected anomaly, without claiming that the design is correct.
Read the snapshot sequence
The first pair of experiments keeps transaction A open while B commits a change from 100 to 60. Under Read Committed, A observes 100 and then 60. In the Repeatable Read variant, A observes 100 in both reads and sees 60 only after ending the transaction. Observations were collected in PostgreSQL 18.6 through two sessions with controlled ordering. There were no replicas, caches, or failover. Before choosing an isolation level, ask which set of reads must support the decision. A stable read can help that decision, but does not by itself establish that every concurrent change respects a rule spanning rows.
Combine condition and update
The budget variant executes UPDATE allowance SET units=units-80 WHERE id=1 AND units>=80 RETURNING units. Code records acceptance only when it receives a row and keeps that record in the same transaction. The first attempt returns one row and leaves 20; the second returns zero rows. In this scenario, zero means there was no eligible reservation, not a syntax error or an acceptance that should be reported as success. The result is 80 accepted and 20 remaining. The demonstrated protection is local and covers one budget row. It does not prove that a debit in another service or an external message participates atomically in the same operation.
Observe write skew across rows
The second problem requires retaining at least one available element. There are two rows, both available. Two Repeatable Read transactions read count two and each withdraws a different element. Because the writes affect different rows, the laboratory sequence allows both to commit and ends with zero available. The rule was violated despite each transaction using a stable snapshot. Record reads, changed identifiers, and commits; without that sequence, a final-state capture does not explain the decision. This example should prompt review of the mechanism protecting the global rule, not a conclusion that every use of Repeatable Read is wrong.
Recompute after rejection
In the Serializable variant, both sessions initially read two available elements. One commits withdrawal and the other receives SQLSTATE 40001 at commit in this particular sequence. The rejected client starts a fresh transaction, reads again, and finds only one available. The recomputed decision declines the second withdrawal; final state retains one element. Resending only the old UPDATE would defeat the purpose of retry. Service code also needs attempt limits and handling of the business outcome. Do not attach irreversible external effects to an attempt that can be rejected without designing their coordination. The laboratory executes no messages, payments, or remote effects.
Deliver a decision with clear limits
Finish the workshop with a decision record: business rule, violating sequence, proposed variant, observed result, and outstanding work. The experiment uses PostgreSQL 18.6 built from official source, a temporary cluster, private Unix socket, and libpq through Python. The script creates and stops only its own cluster. Retain version, hashes, and controlled sequence so discussion can address exactly what was observed. A fictional production team must still review contention, retry limits, compatibility with other writers, and recovery. Do not transfer local evidence into claims about production latency, replication, distributed transactions, or cross-region resilience.
python3 content/labs/design-evidence/run.py --postgres-prefix /path/to/postgresql-18.6
# Requires PostgreSQL 18.6 binaries and libpq; private disposable cluster
# No TCP listener; local Unix socket only
# Six actual PostgreSQL groups; three synthetic arithmetic groups
# Output includes runtime versions, hashes, observations and scope.Two fictional clients read 100, accept 80 each, and leave balance 20. Ledger and balance violate conservation despite successful commits.
Common pitfalls
Confusing SQL success with business acceptance, stable snapshots with every invariant being preserved, or retry with blindly resending the last write.
Related topics: SQL · Microservices · L3 Support
Define the rule, observe the sequence, and decide over current state. A serialization error requires reassessing the whole transaction and may change the decision.
Reference: Transaction isolation · System design patterns; PostgreSQL18 scoped examples; primary guidance consulted 2026-09-30