Read Committed and the changing result of a second SELECT

Your endpoint reads twelve notebooks in stock, does some application work, then runs the same query and gets seven. Both reads happened between one BEGIN and one COMMIT. That can be normal PostgreSQL behavior: Read Committed gives each statement a new view of committed data, rather than freezing the view for the whole transaction.

If you know basic SELECT and transaction syntax, two database sessions are enough to reproduce the distinction. The examples use PostgreSQL 17 and one ordinary inventory table. Session A reads; session B updates. This is an isolation lesson, not a complete reservation service. Run it in a disposable database, execute one stage at a time, and keep both sessions open where instructed.

Give the experiment one changing value

Run the setup once, outside an open transaction:

CREATE TABLE inventory (
  sku text PRIMARY KEY,
  available integer NOT NULL CHECK (available >= 0)
);
INSERT INTO inventory VALUES ('notebook', 12);

The initial quantity is twelve. A successful five-item deduction will leave seven. The primary key removes ambiguity about which row the query means, and the check prevents a negative stored count. Neither constraint promises that two separate reads return identical values.

PostgreSQL’s isolation documentation states that Read Committed is its default isolation level. Specify it explicitly here so a local configuration change cannot silently alter the experiment. “Committed” describes which concurrent changes a statement can see. It does not mean that all statements in the surrounding transaction share the same snapshot.

A snapshot is the database view used to determine which row versions are visible. For the ordinary reads below, the relevant boundary is the start of each query. Keep that boundary separate from when your application receives and uses the result.

Separate an uncommitted update from a committed one

In session A, begin and read:

BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY;
SELECT available FROM inventory WHERE sku = 'notebook';

The result is twelve. Leave A’s transaction open. In B, run:

BEGIN;
UPDATE inventory
SET available = available - 5
WHERE sku = 'notebook' AND available >= 5
RETURNING available;

B’s RETURNING result is seven. Do not commit B yet. Back in A, run this exact query:

SELECT available FROM inventory WHERE sku = 'notebook';

A still sees twelve. B has changed its own transactional state, but has not made that change visible to A. This is not a dirty read. A’s ordinary SELECT also completes while B holds its row update open; it reads the visible older version rather than waiting to read B’s tentative value.

Now run this in B:

COMMIT;

Repeat A’s query. It returns seven, even though A never ended its own transaction. This is a non-repeatable read: the same row and query have produced different committed values inside one transaction. Finish A with COMMIT before moving to the next experiment.

View full-size image ↗

The event that changes the later result is B’s commit before the next statement starts. A’s transaction boundary does not freeze its Read Committed snapshots.

If you repeat the experiment but roll B back, A continues to see twelve. Waiting longer cannot reveal an update that was discarded. PostgreSQL also treats READ UNCOMMITTED as Read Committed, so changing to that name does not expose B’s uncommitted seven in this database. Other database engines need their own documentation and tests.

Dan’s 14 March 2025 guide to PostgreSQL isolation anomalies uses a small repeated booking read to make the same non-repeatable-read distinction. Its dirty-read and snapshot sections are useful companions to this stock example. The quantities here come from the local fixture, and the conclusions are limited to the stated PostgreSQL behavior.

Read-only is a different choice from repeatable

A deliberately uses READ ONLY. That setting prohibits updating this ordinary inventory table; it does not freeze what A can observe. Both the initial twelve and later seven are compatible with a read-only transaction. SET TRANSACTION documents access mode and isolation level as separate transaction characteristics.

The distinction affects application structure. Suppose one helper reads availability for a response, while a second helper independently reads it to choose a message. Wrapping both helpers in Read Committed does not force their answers to agree. If they need the same observed value, pass the first result between them. That avoids a second observation; it does not make the first one current forever.

Also check the actual connection boundary. Two queries sent through independently acquired pool connections do not become one transaction because they occur inside the same HTTP handler. This SQL exercise uses a single session for A so that connection management cannot explain the changed result.

Choose Repeatable Read when several reads need one view

After both previous transactions are closed, restore the fixture in B:

UPDATE inventory SET available = 12 WHERE sku = 'notebook';

In A, start a new transaction with a different isolation level:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT available FROM inventory WHERE sku = 'notebook';

A reads twelve. In B, repeat the earlier deduction block and commit it. Run A’s ordinary read again: it still returns twelve. A’s snapshot predates B’s commit. A new independent session sees seven, and A sees seven after ending its transaction and issuing a new query.

The quantity twelve is not a reservation. B has already completed its deduction while A’s read transaction remains open. Repeatable Read supplies a stable view for these repeated reads, not ownership of the inventory. PostgreSQL’s locking reference distinguishes ordinary reads from operations that acquire conflicting row locks.

This stable view is useful when several queries must describe one database state. It is a poor substitute for fresh availability at the moment of a later purchase. Keep such transactions bounded: every later result in this read-only experiment can remain twelve while newer committed quantities move on.

BEGIN alone does not establish this snapshot

Close A, reset the quantity to twelve in B again, then run only this statement in A:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;

Before A runs any query, let B deduct five and commit. A’s first inventory query now returns seven. Repeatable Read uses a snapshot established by the first query or data-modification statement, not by the arrival of BEGIN alone. Even a seemingly harmless diagnostic query can move that boundary earlier, so do not insert one while reproducing this ordering.

If B then deducts another five and commits, A’s subsequent read remains seven. After A commits and starts a new read, it sees two. This order distinguishes “the transaction has begun” from “the transaction has acquired the snapshot used by its reads.” Choose the isolation level before the first query; attempting to change it afterward is rejected.

View full-size image ↗

The first query is the meaningful snapshot boundary in these read-only transactions. The earlier BEGIN in the second row does not preserve twelve.

A result can already be older when it arrives

For a separate observation, close open transactions and reset to twelve. Let B prepare its five-item deduction without committing. On a third session in autocommit mode, run:

SELECT available, pg_sleep(2)
FROM inventory WHERE sku = 'notebook';

Commit B while that query is sleeping. The delayed statement still returns twelve, and its next ordinary query returns seven. The sleep is only a teaching aid to expose the ordering; it is not a performance measurement or a proposed production query. The local check observed the query’s actual sleep before allowing B to commit.

This follows the same statement snapshot rule. Read Committed does not promise the latest value at response delivery. Network transit and application work can make the observed value older still. A stock label in a browser should therefore be treated as an observation, not proof that a later write will succeed.

Decide what the operation actually requires

For a current observation at each statement, Read Committed can be appropriate. For several ordinary reads that must share a database view, explicitly choose a suitable transaction isolation level and keep the same connection. For a deduction, put its acceptance condition in the write path. The displayed UPDATE requires at least five items and returns no row when that condition fails; code using it must check that outcome.

The SELECT reference documents row-locking clauses as additional behavior. Adding FOR UPDATE changes contention and transaction responsibilities, so it is not a neutral way to make a reporting query “more consistent.” Likewise, Repeatable Read is not a complete concurrent booking protocol. This example records no order, expiration, customer identity or retry key.

To check your understanding, write down A’s expected result at three points: while B’s deduction is open, after B commits, and after A ends its transaction. Run that sequence under Read Committed and Repeatable Read. Then delay A’s first query until after B commits. Explain each number by its snapshot boundary before changing the isolation setting in an application.