Non-Repeatable Reads, Explained

A product has a committed price of $20. A checkout transaction reads that price, while a pricing transaction updates the product to $25 and commits. When the checkout transaction reads the price again, it gets $25. Both reads returned committed data, but they returned different values.

That is a non-repeatable read: the same transaction reads the same row twice and gets different committed values.

The initial state

Before either transaction starts, the database contains one committed row:

Transaction A is the checkout service. It reads the price to calculate the order total. Transaction B is the pricing service. It updates the price to $25 and commits the change. Transaction A uses READ COMMITTED, so each statement gets a view of data committed before that statement begins. The transaction does not automatically pin one snapshot for all of its statements.

The two-transaction sequence

The first and second reads both returned committed values. The second read did not reproduce the first result because another transaction committed an update between the statements.

A small SQL example

READ COMMITTED is available in both MySQL and PostgreSQL, but it is not their same default: PostgreSQL uses it by default, while InnoDB defaults to REPEATABLE READ. Under READ COMMITTED, each statement can see data committed before that statement begins. In PostgreSQL, that means each statement gets a new snapshot. In InnoDB, a consistent nonlocking read also uses a statement-level snapshot at this isolation level.

-- Initial committed state: product 7 has a price of 20.
 
-- Session A: checkout service reads the price.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT price FROM products WHERE id = 7;
-- Result: 20
 
-- Session B: pricing service changes the price while Session A remains open.
START TRANSACTION;
UPDATE products SET price = 25 WHERE id = 7;
COMMIT;
 
-- Session A: checkout service reads the same row again.
SELECT price FROM products WHERE id = 7;
-- Result: 25
COMMIT;

Both values were committed. This is different from a dirty read, where the first result could come from a transaction that later rolls back. Here, the database kept its promise for READ COMMITTED: each individual statement saw committed data. The result is still not a single point-in-time view of the transaction because the statements began at different times.

Why it matters

Non-repeatable reads become dangerous when a transaction makes a decision from multiple reads that are supposed to describe one state. A price check followed by an order insert, a permission check followed by a write, or a balance read followed by a transfer can become internally inconsistent.

An explicit transaction does not automatically make every statement read the same database state. The isolation level and database engine determine whether ordinary reads use one transaction snapshot or a fresh committed view for each statement. Locking reads such as SELECT ... FOR UPDATE can follow additional engine-specific rules, so test the exact read and write pattern.

When not to use stronger isolation

Do not raise isolation globally just because one workflow needs a stable read. Stronger isolation can increase contention, retain more row versions, or cause serialization failures that the application must retry.

For independent reads, READ COMMITTED is often a reasonable trade-off. The transaction does not need a single historical view if each query can stand on its own and the final write checks the right conditions. The relevant question is whether the workflow requires one coherent snapshot, not whether a transaction block exists in the application code.

Gotchas and alternatives

The first fix many teams try is adding START TRANSACTION around the code. That helps only if the selected isolation level provides the snapshot behavior the workflow needs. Another gotcha is hidden reads from an object-relational mapper. A property access or lazy-loaded relation can issue a second query and observe a newer committed version. A second connection, an autocommit boundary, or a retry can also move the read outside the intended transaction.

The main alternatives are:

• REPEATABLE READ: keep a consistent snapshot for ordinary reads inside the transaction. MySQL and PostgreSQL implement details differently, especially for locking reads and write conflicts, so test the exact workflow.
• SERIALIZABLE: make concurrent executions satisfy serializable results, accepting more waiting, aborts, or retries. The implementation may use locks, validation, or predicate protection.
• One statement with the required logic: reduce the window between reads by moving the decision into a single database operation.
• Optimistic concurrency control: store a version number and reject the write if another transaction changed the row first.

References

• PostgreSQL 16 Documentation, Transaction Isolation postgresql.org
• MySQL 8.4 Reference Manual, InnoDB Transaction Isolation Levels dev.mysql.com

Takeaway

A non-repeatable read happens when a transaction sees two different committed versions of the same row, so choose an isolation level that matches whether the workflow needs a stable snapshot.