PostgreSQL / Intermediate: concurrency and maintainable SQL / Lesson 7

Transactions, MVCC and isolation

Reason about committed versions and atomic business work. Use PostgreSQL’s READ COMMITTED default deliberately and distinguish snapshot stability from serializable correctness.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Explain statement snapshots and explicit transaction boundaries.
Handle isolation failures by retrying complete business work.

Readers see versions appropriate to a snapshot

PostgreSQL uses multiversion concurrency control so an ordinary reader can see a committed version while another transaction updates the row. This reduces reader-writer blocking but does not eliminate locks or guarantee every business invariant. An UPDATE creates a new row version rather than immediately removing every trace of the old one. Maintenance later reclaims versions no longer needed. A transaction combines statements into an atomic unit, but the selected isolation level determines how their reads relate to concurrent commits.

Observe your own changes inside a transaction

Assume a disposable accounts fixture has account 10 with balance one hundred. This transaction changes it to eighty-five and reads its own new value before rolling back to one hundred. Other ordinary sessions do not see this uncommitted balance. BEGIN creates an explicit boundary; otherwise a client commonly sends each statement as its own transaction unless its driver changes that behavior. The example teaches visibility and atomicity, not a complete money-transfer procedure with destination validation.

sql Illustrative SQL; expected results are reasoned, not measured.

BEGIN;
UPDATE quay_lab.accounts SET balance = balance - 15
WHERE account_id = 10 RETURNING balance;
SELECT balance FROM quay_lab.accounts WHERE account_id = 10;
ROLLBACK;

Expected output

Expected inside the transaction: balance = 85. After rollback: balance = 100.

The expected values assume one existing account and no other modifications in the isolated fixture.

The default is a snapshot per statement

PostgreSQL defaults to READ COMMITTED, unlike the commonly learned InnoDB REPEATABLE READ default. Successive ordinary SELECT statements in one READ COMMITTED transaction can see different committed data because each statement begins with a fresh snapshot. READ UNCOMMITTED is treated as READ COMMITTED in PostgreSQL, so it does not enable dirty reads. Inspect default_transaction_isolation and the current transaction setting rather than assuming a global or connector default. A snapshot read is not a reservation for a later write.

Request a stable read-only snapshot explicitly

REPEATABLE READ uses a transaction snapshot established by the first non-transaction-control statement and keeps ordinary reads stable. The following pattern compares two report measures from that same snapshot. PostgreSQL’s implementation also prevents phantom reads, but can still allow serialization anomalies. SERIALIZABLE detects dangerous dependency patterns and may abort a transaction with 40001. It is not a promise that every attempt succeeds without waiting or retry. The correct choice follows the business invariant, not a blanket preference for the strongest-sounding name.

sql Illustrative SQL; expected results are reasoned, not measured.

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT COUNT(*) AS bookings FROM quay_lab.bookings;
SELECT COALESCE(SUM(total), 0) AS total FROM quay_lab.bookings;
COMMIT;

Actual totals depend on the fixture. Both reads share one stable snapshot; no measured counts are claimed.

Retry the decision, not only its final statement

After a serialization failure, roll back and retry the complete transaction with bounded attempts and appropriate jitter. Re-read inputs because the original decision may no longer be valid. External side effects should use an idempotency or outbox design rather than happen repeatedly inside a retried database block. Sequence allocation is an important exception to naive rollback expectations: nextval values are not returned to a sequence when a transaction aborts. Long-lived snapshots can also delay cleanup, so stable reports need a duration policy.

Common errors and fixes

Two reads disagree inside one transaction

Why it happens: READ COMMITTED gives each ordinary statement a new snapshot.
How to fix it: Choose a stable snapshot only when the report requires it.
Prevent it next time: Document isolation and transaction boundaries with report expectations.

could not serialize access (40001)

Why it happens: Concurrent history conflicts with the required isolation guarantee.
How to fix it: Rollback and retry the entire bounded idempotent transaction.
Prevent it next time: Test concurrent invariant violations and full-transaction retry behavior.

Identity numbering has gaps after rollback

Why it happens: Sequence allocation is not rolled back with row changes.
How to fix it: Accept surrogate-key gaps and design business numbering separately.
Prevent it next time: Never equate identifier sequence continuity with transaction atomicity.

Check your understanding

1. A READ COMMITTED transaction selects balance, another session commits an update, then the first selects again. Must the values match?

Show worked solution

No. Each ordinary SELECT begins from its own snapshot and can observe the newly committed version. The transaction still sees its own prior changes, but it does not freeze all concurrent history. If a report needs one stable snapshot, request an appropriate read-only REPEATABLE READ transaction before its first query and bound its duration.

2. Two workers each read that another doctor remains on call and both go off call. Why might a stable snapshot alone be insufficient?