Rehearse PostgreSQL Transaction Anomalies Before Choosing Isolation

Name the invariant that concurrency can break

Isolation-level discussions become vague when they begin with labels. Start with a business statement that must remain true: at least one doctor remains on call, a coupon has no more than one redemption, reserved seats never exceed capacity, or an account cannot approve its own second control. Describe the rows read, decision calculated, rows written, and final state that would violate the rule. This turns “we need stronger consistency” into a reviewable scenario.

Use synthetic identifiers and a small schema that preserves the real decision shape. Record PostgreSQL version, table and constraint definitions, indexes, transaction defaults, connection-pool settings, ORM behavior, and every SQL statement. An ORM method name such as transaction does not establish the effective isolation level or when queries run. Capture SHOW transaction_isolation from inside each session and keep server-side observations beside application logs.

Build a synchronized two-session fixture

Open two independent database sessions against a disposable dataset. Use barriers so both transactions read the same starting state before either performs its decisive write. Label each step A1, B1, A2, B2 and record statement start, result, lock wait, commit result, and final rows. Ordinary sequential tests miss the anomaly because the second operation sees the first commit. A sleep may appear to coordinate locally but becomes flaky; explicit test barriers make the interleaving intentional.

Run the fixture first under the application’s current setting. For a write-skew example, each transaction may update a different row after reading the same multi-row condition, so row locks on the updated records do not necessarily protect the predicate. For a lost update, the critical shape may be a read-compute-write replacement. Do not generalize from one anomaly to all workloads. Preserve exactly which interleaving passed or failed.

Compare Read Committed and Repeatable Read accurately

PostgreSQL Read Committed gives each statement a snapshot that can differ from the previous statement in the same transaction. That may be correct for short independent operations, but a decision spanning several statements can observe change. Repeatable Read holds a transaction snapshot and prevents several anomalies, yet it does not mean every application invariant is safe. PostgreSQL documents its own behavior, which should not be inferred from another database with the same SQL label.

Repeat the synchronized fixture at each candidate level and capture both the visible reads and commit outcomes. Check whether an update waits, reevaluates a predicate, aborts, or completes. Add a third unrelated transaction so the test does not mistake general contention for protection of the invariant. The goal is not to declare one level universally best; it is to show which mechanism guards this decision and what operational behavior it introduces.

Treat Serializable as detection plus retry

PostgreSQL Serializable can abort a transaction when concurrent execution cannot be ordered safely. That abort is a successful safety outcome, not a database malfunction. The application must recognize the serialization-failure code, discard the entire attempted transaction, and retry from the beginning with a fresh decision. Retrying only the last statement can reuse assumptions from the failed snapshot and is not equivalent.

Set a bounded attempt count and elapsed-time budget. Apply jitter where many callers may collide, preserve one logical operation identifier, and ensure external effects do not occur before the transaction is durably accepted. Email, webhooks, or payments invoked inside an attempt can duplicate when the database asks for a retry. Use an outbox or another reviewed boundary when an external effect must follow commit. Return an actionable result when the retry budget is exhausted instead of spinning indefinitely.

Compare constraints and explicit locking

A unique, exclusion, check, or foreign-key constraint may express some invariants closer to the data and reject every violating writer. Other rules span aggregates, time windows, or several rows and cannot be captured by a simple constraint. SELECT FOR UPDATE can serialize access to known rows, but it cannot lock a row that does not exist unless the design provides a stable parent or advisory boundary. Document what object carries contention.

Compare candidate designs using correctness, hot-row pressure, deadlock behavior, retry rate, query plans, migration risk, and clarity. Advisory locks add naming and connection considerations. Table locks may protect broadly at unacceptable cost. Serializable avoids inventing locks but asks the application to retry. The database owner chooses the operational tradeoff with the product owner who defines the invariant; the developer supplies measurements and a focused implementation.

Exercise uncomfortable transaction endings

Add timeout, cancelled request, deadlock, connection loss before commit response, process restart, and pool reuse. After each case, reconcile the durable state using the logical operation identifier. A client that did not receive a commit response cannot assume rollback; the server may have committed before the connection failed. The recovery path should query durable state rather than blindly repeating an irreversible effect.

Observe active transactions, lock waits, deadlocks, serialization failures, attempt counts, transaction duration, and exhausted retries. Long idle transactions can retain snapshots and make an otherwise sound design operationally harmful. Keep diagnostic queries bounded and approved. Production remediation, termination of sessions, and parameter changes remain with the database owner, not with a distributed contributor investigating from a handoff.

Deliver the evidence as a decision packet

Include the invariant, schema, fixtures, exact SQL, barriers, isolation evidence, timelines, final rows, candidate mechanisms, retry implementation, external-effect boundary, failure cases, measurements, limitations, and recommended owner decision. Link code and migration revisions. State whether evidence came from local PostgreSQL, CI, or a staging-like environment and identify material differences such as version, topology, load, or proxying.

The next working window should know whether to refine the fixture, review a constraint, assess contention, approve a design, or stop for product clarification. A green serial test is not completion. Completion means the dangerous concurrent sequence has an expected outcome, retries are safe, and accountable owners accept the remaining tradeoff. Developer Offshore can help staff this bounded engineering lane when the client brings the invariant, stack, approved environment, and reviewer.

Questions about assessing Philippine developers