Choosing how much to protect against

Perfect isolation, where transactions behave exactly as though they ran one at a time, is available and slow. So SQL defines levels, and each one names which anomalies it still allows.

The three anomalies

Dirty read: you read a value another transaction wrote and then rolled back. You acted on something that never happened.

Non repeatable read: you read a row, someone commits a change, you read it again and it differs. The same query inside one transaction gave two answers.

Phantom read: you run WHERE amount > 1000, someone inserts a qualifying row, you run it again and there is an extra row.

The levels

Each level down costs more, in locks held longer or in more work tracking versions. Choosing a level is choosing what you are willing to be wrong about in exchange for throughput.

What you are actually running

The default differs by system and this matters more than the table does.

• PostgreSQL: READ COMMITTED. Each statement sees a fresh snapshot, so two identical queries in one transaction can differ.
• MySQL InnoDB: REPEATABLE READ, and its implementation also avoids most phantoms.
• Oracle: READ COMMITTED, and it does not offer READ UNCOMMITTED at all.

Code written against MySQL and moved to Postgres can therefore break in a way that looks random, because a guarantee it silently relied on is gone.

Reading without blocking

Most modern databases use multi version concurrency control. A writer creates a new version of the row rather than overwriting it, so readers see the old one and never wait. This is why "readers do not block writers" is true in Postgres and was not true in older systems.

The cost is that old versions accumulate and must be cleaned up, which is what Postgres VACUUM does and why a table under heavy update can grow far beyond the size of its live data.