# Inconsistent results inside a transaction: non-repeatable and phantom reads (isolation level)

**Code:** `isolation` · **Category:** SQL · **Seen on:** SQL, PostgreSQL, MySQL, SQL Server

Two reads in the same transaction see different data because another transaction committed in between.

## Meaning

READ COMMITTED allows non-repeatable and phantom reads; REPEATABLE READ and SERIALIZABLE prevent more anomalies at the cost of locks/retries. Pick the isolation level per need, use `SELECT ... FOR UPDATE` for read-modify-write, and be ready to retry serialization failures.

## Common causes

- Default READ COMMITTED isolation
- Read-then-write race (lost update)
- Phantom rows appearing in range queries
- No row locking on read-modify-write
- Long-running reports on changing data

## Quick fix

1. Use `SELECT ... FOR UPDATE` where you modify what you read
2. Raise the isolation level where needed and retry on conflict
3. Keep transactions short

## Detailed fix by platform

### SQL

1. 

```sql
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
```


## How to diagnose

1. **Isolation** — Which level?
2. **Lock** — On read?
3. **Retry** — Implemented?

---
Source: [WhatIsMyError](https://whatismyerror.com/errors/sql/isolation-level-non-repeatable-phantom-read)
