Answer

Transaction isolation affects how concurrent transactions see each other's changes. It is an important part of ACID properties. In ANSI SQL, there are four basic isolation levels:

• READ UNCOMMITTED — Sees even uncommitted changes from other transactions (dirty reads).
• READ COMMITTED — Sees only committed changes; prevents dirty reads but allows non-repeatable reads.
• REPEATABLE READ — The same data in one transaction appears unchanged. Avoids dirty and non-repeatable reads, but phantom reads may occur.
• SERIALIZABLE — The strictest level, transactions are completely isolated as if executed sequentially; eliminates all types of anomalies.

The choice of level depends on the application's requirements:

• For reporting, REPEATABLE READ or higher is often sufficient;
• For high-load systems, the optimal compromise is READ COMMITTED;
• For financial transactions — SERIALIZABLE, despite reduced performance.

Example:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- Subsequent SELECTs will see "frozen" values

Trick Question

"Does the REPEATABLE READ level guarantee protection against phantom reads in any DB?"

No. In PostgreSQL and some other DBMS, the REPEATABLE READ level prevents only dirty and non-repeatable reads but does not necessarily protect against phantom reads. In MySQL/InnoDB, REPEATABLE READ is essentially SERIALIZABLE, but not in other DBMS.

Example:

-- In one transaction, read SELECT * FROM orders WHERE amount > 100;
-- In another transaction, a new value with amount > 100 is inserted and committed
-- The first transaction upon a repeated SELECT will see a "phantom" row if isolation is below SERIALIZABLE

Examples of real errors due to a lack of understanding of the subtleties of the topic

---

Story

A financial service locked only READ COMMITTED for performance — the user saw an amount that had already been changed by another process, resulting in balance discrepancies.

---

Story

In a hotel booking system, double bookings of the same room occurred — transactions did not isolate current bookings, the level was READ COMMITTED.

---

Story

Transitioning from MySQL to PostgreSQL: a developer was used to REPEATABLE READ protecting against phantoms, but after migration, "stuck" orders appeared that were not expected to be seen in repeated queries within the same transaction.