Two correct reads can make an incorrect report

We know that a transaction can group changes together. Does that automatically make every report consistent? Consider the shop’s two internal credit accounts, A and B. Each starts with 100 credits. A transfer moves 10 from A to B, so the total should remain 200.

The transfer is one atomic transaction: it commits A = 90 and B = 110 together. Other transactions do not see a half-finished transfer. Our report, however, reads the accounts using two separate statements.

Let the transfer happen between the reads

Assume each report statement reads the latest committed state available when that statement begins. The report has not requested one fixed view for both statements.

1. Report reads A

   The first statement returns 100. The report remembers that number.

2. Transfer commits

   The database now contains A = 90 and B = 110. Its total is still 200.

3. Report reads B

   The second statement returns 110. The report adds 100 + 110 and displays 210.

There was no instant at which the database contained the pair 100 and 110 as its committed balances. The report assembled that pair across time. Atomicity protected the transfer, but the report also needed a rule about how its reads relate to one another.

Give both reads the same view

One possible solution is to prevent the transfer while the report reads the relevant accounts. Another is to let the transfer proceed while retaining the earlier values for the report.

A snapshot specifies a view of committed data that the report can use consistently. With a view from before the transfer, the report reads 100 and 100. With a view from after it, the report reads 90 and 110. Either pair adds to 200.

Keeping old versions is one way an engine can provide the earlier view after a newer change commits. It creates a new problem: the engine must choose which version each reader is allowed to see. That is the subject of the MVCC lesson that follows.

State the boundary precisely

This example uses separate statements. It does not claim that a single ordinary PostgreSQL SELECT sees half of a committed transfer. PostgreSQL’s Read Committed behavior can provide a fresh view for each statement; Repeatable Read provides a transaction-level view for ordinary reads, established at its first non-transaction-control statement. PostgreSQL isolation documentation

The names are product behavior we will revisit. The underlying question is already understandable: do these reads belong to one view, or can the view advance between them?

Reverse the timing

The report first reads B = 100. Then the same transfer commits. Finally it reads A = 90. What total does it produce? What does that tell us?

Combine the actual observations

It produces 190. Changing the read order changes the error’s direction, but not its cause: the report still combines values from different committed states. The real total remains 200 before and after the transfer.

Checking that each individual read uses committed data does not establish that the combined report uses one consistent state.

We now have a reason to preserve a reader’s view. The next lesson uses a smaller one-price example to explain how version selection makes that possible.

Further reading

PostgreSQL’s MVCC introduction introduces versioned reads. The linked isolation documentation specifies when views are established; the simplified timestamp model on the next page is a teaching model rather than PostgreSQL’s exact metadata layout.