Isolation Level

When two or more transactions run against the same rows at the same time, they can step on each other. Isolation level is the knob that decides how much they are allowed to interfere. A weaker level lets transactions run faster and with less locking, but they can see messy intermediate data. A stronger level makes them behave as if they ran one after another, at the cost of speed and more conflicts.

The SQL standard defines four levels by which read anomalies they forbid. A dirty read is reading a row another transaction wrote but has not committed yet. A non-repeatable read is reading the same row twice in one transaction and getting two different values because someone else committed an update in between. A phantom read is running the same query twice and getting a different set of rows because someone else inserted or deleted matching rows.

Read Uncommitted allows all three. Read Committed forbids dirty reads. Repeatable Read forbids dirty and non-repeatable reads. Serializable forbids all three plus guarantees the end result is the same as if transactions ran one at a time.

There are two main strategies. The classic approach is locking: a transaction takes shared locks to read and exclusive locks to write, and holds them for varying lengths of time depending on the level. Stronger levels hold locks longer, which blocks other transactions and reduces throughput.

The modern approach most engines use is Multi-Version Concurrency Control, or MVCC. Instead of blocking readers, the database keeps multiple versions of each row. When a transaction reads, it sees a consistent snapshot of the data as of a certain point in time, so readers never block writers and writers never block readers. PostgreSQL and Oracle both work this way.

One trap is that the same level name behaves differently across engines. PostgreSQL Repeatable Read is actually snapshot isolation and prevents phantom reads, which the standard does not require. Oracle has no true Serializable in the lock-based sense and uses snapshot isolation for its strongest practical level. MySQL with InnoDB defaults to Repeatable Read, while PostgreSQL and SQL Server default to Read Committed. Always check what your specific database does rather than trusting the label.

Most applications run fine on Read Committed, which is why it is the default in PostgreSQL, SQL Server, and Oracle. You get protection from reading garbage that was never committed, and you keep high concurrency. The cost is that values can shift under you between two reads in the same transaction.

Step up to Repeatable Read or Serializable when correctness depends on a stable view of data across multiple statements: think reporting that sums many tables, inventory checks before a purchase, or a transfer that reads a balance then writes it back. Serializable is the safest and the easiest to reason about because the database guarantees no anomalies at all, but under contention it produces serialization failures that your application must catch and retry.

Read Uncommitted is rarely a good idea on a transactional system because dirty reads can show data that gets rolled back. The general rule is to use the weakest level that still keeps your application correct, then handle the conflicts that the stronger levels would have prevented either with explicit locking or with retry logic.

Say you move 100 dollars from account A to account B. The transaction reads A's balance, checks it is at least 100, subtracts 100 from A, and adds 100 to B. Now imagine two transfers from account A run at the same time and A only has 100 dollars.

Under Read Committed, both transactions can read the balance as 100, both see it as sufficient, and both proceed, leaving A overdrawn by 100. This is a write skew style problem. Read Committed did its job of hiding uncommitted data, but it did not stop two transactions from making decisions on the same stale snapshot.

Under Serializable, the database detects that these two transactions could not have happened in any serial order without conflict, aborts one of them, and you retry it. The retried transaction now sees the updated balance, finds it insufficient, and correctly rejects the second transfer. This is exactly the class of bug that picking the right isolation level prevents.