Stage 9. Isolation Levels for Concurrent Workloads

An isolation level defines how one transaction is allowed to interact with other transactions running at the same time. In this article, “transaction A” and “transaction B” mean two separate units of work started by two different requests, workers, or users. They may touch the same rows while they are both still open.

Without isolation, a database can be fast but incorrect. With very strict isolation, a database can be safer but may block more often or ask the application to retry. The task is to choose the level that fits the business risk.

Lost Update in Plain Language

Lost update means one transaction silently overwrites the result of another transaction.

Example with an account balance:

1. Transaction A reads balance = 100.
2. Transaction B also reads balance = 100.
3. Transaction A subtracts 10 and writes 90.
4. Transaction B subtracts 20 from its old copy and writes 80.

The final balance becomes 80, but it should be 70 if both withdrawals were applied. Transaction B did not intentionally cancel transaction A. It simply worked with a stale value and overwrote the row later.

A safer version locks the row while the first transaction is changing it:

BEGIN;
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts
SET balance = balance - 10
WHERE id = 1;
COMMIT;

FOR UPDATE locks the selected row for writers until the transaction ends. Another transaction that wants to update the same account must wait, fail, or retry depending on the database and timeout settings.

For simple balance changes, an atomic update is often even better than reading the value first:

UPDATE accounts
SET balance = balance - 10
WHERE id = 1 AND balance >= 10;

Then the application checks how many rows were updated. If the result is 0, there was not enough money or another condition failed.

Common Read Anomalies

Isolation is not only about lost updates. It also controls what a transaction can read while other transactions are changing data.

Dirty reads are usually blocked by mainstream relational databases at normal settings. Non-repeatable reads and phantom reads depend more on the chosen isolation level and database implementation.

Main Isolation Levels

Read Committed is a common default. It prevents reading uncommitted data, but it does not promise that two SELECT statements in one transaction will return the same value. If another transaction commits between your two statements, your second statement may see the new value.

BEGIN;
SELECT amount FROM orders WHERE id = 10; -- returns 100
-- another transaction updates amount to 150 and commits
SELECT amount FROM orders WHERE id = 10; -- may return 150 at Read Committed
COMMIT;

Repeatable Read gives a more stable view during the transaction. It is useful when one operation makes several decisions from the same dataset. The exact details differ between databases, but the intent is the same: avoid surprising changes while the transaction is still running.

Serializable is the strictest level. The database protects correctness as if transactions were executed sequentially. This does not mean the database literally runs one transaction at a time. It may run them concurrently, detect a dangerous conflict, and abort one transaction. The application must then retry the aborted transaction safely.

Locks, Versions, and Retries

Databases usually protect isolation with locks, row versions, or both. A lock temporarily prevents conflicting access. Row versioning lets readers see a stable older version while writers prepare a new committed version.

Stricter isolation can produce more waiting, deadlocks, or serialization conflicts. That is not always a bug. Sometimes the database is correctly refusing to produce an unsafe result. The service must know which operations can be retried.

Practical strategy:

• use Read Committed for ordinary lists, detail pages, and simple CRUD flows;
• use row locks or atomic conditional updates for money, stock, and counters;
• use stricter isolation for operations where a wrong result is more expensive than a retry;
• add bounded retries for serialization conflicts;
• keep transactions short so locks are held for less time.

Common mistakes:

1. Assuming the default isolation level fits every business operation.
2. Reading a value, calculating in application code, then writing it back without a lock or atomic update.
3. Using Serializable without retry logic.
4. Holding a transaction open while calling an external API.
5. Testing only one request at a time and missing concurrency bugs.

Isolation level is part of business correctness, not only database tuning. The right choice depends on what can go wrong if two users touch the same data at the same time.

Practice Before the Next Lesson

Re-run the examples from this article on a tiny dataset of 5-10 rows. This step matters because SQL becomes much easier once you execute queries yourself and inspect real tabular output. Start with a plain version of the query, then change one thing at a time and compare behavior or execution plan. If results look wrong, validate each part: filters, selected columns, join conditions, and expected NULL handling.

Quick self-check:

1. I can explain the lesson in one simple sentence.
2. I can write a basic SQL example from memory.
3. I can read query output and explain each column.
4. I can name one common mistake and how to avoid it.

For critical workflows, add one concurrent integration test per invariant: balance, stock, quota, booking slot. This turns isolation decisions into verified behavior, not assumptions.

Extra Scenario: Retry on Serialization Conflict

At stricter isolation levels, some transactions may fail with serialization conflicts. This is expected behavior, and services should retry safely.

Practical pattern:

1. Execute transaction.
2. On conflict, apply short backoff.
3. Retry up to 2-3 times.

This is especially important for operations where correctness is more important than single-request latency.