Isolation and snapshots

A view of committed data

Imagine a report reading the catalogue while a staff member is changing several prices. We want the report to use completed changes, without having to stop everyone editing the catalogue until it finishes. PostgreSQL can do this by retaining older versions of records. A reader can use a previously committed price while a writer prepares its replacement.

The database needs a way to decide which versions belong in the reader’s view. That visibility information is called a snapshot. It is not an exported copy of every table. If the writer commits after a query begins, the query can finish using its earlier view, even though newer reads may now see the changed price.

Our example uses ordinary SELECT statements: reads without FOR UPDATE or FOR SHARE. That distinction matters. A read that also requests a conflicting lock may have to wait and has different rules, which we’ll return to below.

Statement and transaction views

Consider a report that reads the Blue mug’s catalogue price twice, in two separate queries within one transaction. The price starts at EUR 20. Between those reads, a staff member changes it to EUR 22 and commits in another transaction. The question is whether the report keeps its first view or gets a fresh one for its second query.

PostgreSQL’s default level is Read Committed. Each ordinary SELECT takes a view when that statement starts. The report’s first query begins before the staff change and returns EUR 20. Its second query begins after the change commits and returns EUR 22.

Both answers describe committed data. What changed was the moment being described. Putting the two queries between BEGIN and COMMIT keeps them in one transaction, but at this level it does not give them one lasting snapshot.

With PostgreSQL Repeatable Read, the transaction establishes a snapshot at its first query or data-changing statement. Later ordinary reads keep that view. In our schedule, the first query returns EUR 20 and the second also returns EUR 20, even though the writer has committed EUR 22 in the meantime. These behaviours are specified in PostgreSQL’s isolation documentation.

The repeatable view is useful for a report that reads product counts in one query and product details in another. If another transaction adds a product between those queries, two fresh views can describe different catalogues. A transaction snapshot keeps the report’s parts aligned with one view, provided it uses ordinary reads and does not itself change the facts it is reporting.

A single ordinary SELECT already uses one statement view in Read Committed, including the tables it joins. The distinction becomes useful when we need several statements to agree. It does not require every catalogue request to open a long Repeatable Read transaction.

-- Report connection: run the first SELECT, then pause.
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT current_price
FROM products
WHERE product_id = 'P7';

-- After the writer has committed, run the same query again.
SELECT current_price
FROM products
WHERE product_id = 'P7';
COMMIT;

-- A separate connection, between the report's two reads.
BEGIN;
UPDATE products
SET current_price = 22.00
WHERE product_id = 'P7';
COMMIT;

To run the SQL yourself, start the shared practice setup. It explains opening PostgreSQL, loading the files and choosing a fresh database for this article.

Use a fresh practice database for this article. Load the shop schema and records once; it includes P7 at EUR 20 and the historical orders O12 and O13. Open two PostgreSQL query-editor connections to that same database. Run the first report query, pause that connection, run and commit the writer on the other connection, then finish the report. Repeatable Read returns 20 twice. Reset P7 to 20 before repeating the schedule with READ COMMITTED; it then returns 20 and 22. Merely running these blocks one after another on one connection would not test the overlapping schedule.

READ ONLY prevents this report from changing ordinary application tables. We choose the isolation level at the start, before a query establishes the view. SET TRANSACTION documents these settings and their timing.

The view does not freeze the database

The writer in our experiment succeeds in both runs. A report’s stable view does not prevent other transactions from changing the catalogue. It lets the report keep reading an earlier committed version while those changes happen.

The transaction also sees its own changes. A Repeatable Read transaction that increases the mug price from 20 to 21 will read 21 afterward. “Stable snapshot” means stability against later changes committed by other transactions; it does not hide work already performed inside this transaction.

-- A separate example, starting with P7 at EUR 20.
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE products
SET current_price = current_price + 1.00
WHERE product_id = 'P7'
RETURNING current_price;  -- 21.00

SELECT current_price FROM products WHERE product_id = 'P7';
-- Also 21.00: this transaction can see its own update.
ROLLBACK;                -- Leave the catalogue at 20.00.

Writes introduce another concern. Suppose a Repeatable Read transaction has seen P7 at 20, then another transaction changes P7 and commits. If the first transaction later tries to update that row, selected by its unchanged product ID, PostgreSQL rejects the attempt with a serialization failure: another transaction changed the target row after its snapshot was established. Keeping the old view does not give it permission to overwrite the newer version. To try again, the application must start a new transaction and repeat the work from the beginning. This is the changed-row rule described in PostgreSQL’s Repeatable Read documentation.

This is also why the locking example in Concurrent changes named its isolation level. At Read Committed, a SELECT … FOR UPDATE can wait for a row’s writer and then return the updated row. At Repeatable Read, a conflicting committed update since the transaction’s snapshot can instead cause failure. An ordinary-read snapshot is not a promise that every command returns only the old version.

For a checkout that decrements one stock row only if enough remains, a guarded update at Read Committed gives that row a place to resolve the conflict. For a report, a stable transaction view solves a different problem. We choose according to the reads, writes, and rule, rather than treating a higher level as a universal fix.

A stable view can support conflicting decisions

The shop has a small featured display containing the Blue mug and the Bowl. Staff can hide either product, but at least one must remain offered. This is a display rule, separate from stock: hiding a featured product does not change its inventory or erase its catalogue record.

We store the two flags in separate records:

-- Add to the shop practice schema, once in a fresh database.
CREATE TABLE featured_products (
  product_id text PRIMARY KEY REFERENCES products (product_id),
  enabled boolean NOT NULL
);
INSERT INTO featured_products (product_id, enabled)
VALUES ('P7', true), ('P8', true);

Staff A wants to hide P7. A checks that P8 is enabled, then disables P7. Staff B wants to hide P8. B checks that P7 is enabled, then disables P8. Either transaction, run alone from the initial state, leaves one product enabled.

If their Repeatable Read transactions overlap, each can see both flags still enabled. A writes P7 and B writes P8. These are different rows, so there is no attempt to overwrite the same flag. Both transactions can commit, leaving neither product enabled.

This is called write skew: the transactions base their decisions on related facts, then change different records in a way that breaks the combined rule. No read needs to be inconsistent within its own snapshot for this to happen. The snapshots faithfully preserve a state that each transaction used to justify its own change.

-- Staff A: staff B uses the same transaction with P7/P8 swapped.
BEGIN ISOLATION LEVEL REPEATABLE READ;

UPDATE featured_products
SET enabled = false
WHERE product_id = 'P7'
  AND EXISTS (
    SELECT 1 FROM featured_products
    WHERE product_id = 'P8' AND enabled
  )
RETURNING product_id;

COMMIT;

EXISTS is true when its subquery finds a row. Here AND enabled means that the boolean flag must be true. The UPDATE therefore changes P7 only if the visible P8 row is enabled; RETURNING reports whether it changed P7. Putting that check inside one statement removes a gap between the application’s read and write. It still does not make these two independent writes meet at one record.

A valid overlapping schedule establishes both snapshots while both flags are true. A hides P7, B hides P8 before A commits, then both commit. The conditional statements make the same decisions as the drawing. Guarding a single stock decrement against a value in that same row solved the previous article’s problem; this predicate depends on the other row.

Serializable and the rejected transaction

Serializable requires successfully committed transactions to have an effect compatible with some one-at-a-time order. It need not literally run every transaction in sequence. PostgreSQL tracks conflicting reads and writes and can reject a transaction when letting it commit would break that promise.

For the display, imagine A runs completely before B starts. B then sees P7 disabled, so B cannot hide P8. If B runs completely before A starts, A cannot hide P7. Both disabled cannot result from either serial order of these correctly written transactions. With both transactions using Serializable, PostgreSQL prevents that combined outcome; at least one has to fail.

-- Staff A: staff B uses the same transaction with P7/P8 swapped.
BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE featured_products
SET enabled = false
WHERE product_id = 'P7'
  AND EXISTS (
    SELECT 1 FROM featured_products
    WHERE product_id = 'P8' AND enabled
  )
RETURNING product_id;

COMMIT;

This is the same complete decision, with a different isolation level. A serialization failure can arrive during a statement or at commit. The application must treat its earlier results as provisional until commit succeeds.

Retry the failed transaction with a fresh view and repeat its decision. If A’s successful change hid P7, B’s new attempt now finds P7 disabled and changes no row. That is a successful evaluation of the rule, even though B did not get its original wish. Retrying only “set P8 disabled” would discard the very check that made the transaction correct.

Serializable preserves an outcome consistent with the transaction logic; it cannot repair logic that never checked the rule. Every path that changes these display flags must participate in the chosen protection. A writer that unconditionally hides the last product can still create an invalid display.

Explicit coordination is another option. We could add one display-control record, separate from the two product flags, for all display writers to lock. Its purpose is to give their decisions one shared place to wait. At Read Committed, each transaction first takes a FOR UPDATE lock on that record, then reads the flags in a subsequent statement while still holding the lock.

After A commits, B obtains the common lock and starts its flag-reading statement. That new Read Committed statement sees A’s completed change, so B leaves the remaining product enabled. Every display writer must take the common lock before reading and deciding. Simply locking an unchanged control record inside Repeatable Read would not refresh an already established snapshot of the flags.

Choose the view and the rule together

Start by asking whether several reads must describe one committed view. A report assembled from several ordinary queries can benefit from Repeatable Read. A live request using one query may already have the view it needs at Read Committed.

Then ask what concurrent decisions could invalidate the write. A guarded update can coordinate a rule confined to its stock row. A rule over several changing rows may need a coordinated locking protocol or Serializable transactions with whole-transaction retry. A stable snapshot supplies a dependable reading; it does not, on its own, supply every coordination guarantee.

That leaves a practical question. A transaction may wait, fail, or commit without its reply reaching us. Handling waits, deadlocks, and retries follows those different endings and explains how an application can continue without turning one checkout into two.