Transaction isolation

What is transaction isolation?

Transaction isolation decides how much one running transaction can see of other transactions that are still in flight. A transaction is a group of database changes that has to succeed or fail as one unit, and isolation is the rule that says what a second transaction may observe while the first one is only halfway done.

It is the I in ACID transactions. The other three letters cover a change being all-or-nothing, valid against the rules, and safe after a crash. Isolation is the one that deals with concurrency: several sessions reading and writing the same rows at the same time.

The setting is always a trade. Stronger isolation stops more of these collisions, but the database has to block, version, or reject more work to do it, so you lose concurrency and throughput. Weaker isolation gives you more of both and lets stranger intermediate states leak through.

The three read anomalies

The SQL standard defines isolation levels by naming three things that must not happen when transactions overlap. Each level is described by which of these it forbids.

• Dirty read. A transaction reads data that another transaction has written but not yet committed. If that other transaction rolls back, you acted on a value that never really existed.

• Non-repeatable read. A transaction reads a row, and when it reads the same row again, the value has changed because another transaction committed an update in between.

• Phantom read. A transaction runs a query that returns a set of rows, and when it runs the same query again, extra rows have appeared because another transaction committed an insert that matches the same condition.

The last two are easy to confuse. Say a transaction counts every order above 1000 euro, does some work, then counts again to reconcile. If another transaction commits a brand new 1500 euro order in between, the second count is higher. Nothing existing was changed, so this is not a non-repeatable read; a whole new row appeared, which is a phantom.

The four standard levels

From weakest to strongest, each level forbids one more anomaly than the one before it.

• Read uncommitted. The weakest level. Dirty reads are allowed.

• Read committed. You only see committed data, so no dirty reads. Non-repeatable reads and phantoms are still possible.

• Repeatable read. Rows you have already read stay stable for the life of the transaction, so no non-repeatable reads. The standard still permits phantoms.

• Serializable. The strongest level. The database behaves as if the transactions ran one after another, so none of the three anomalies can happen.

You set the level per session or per transaction. In SQL Server the statement reads like this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT SUM(amount) FROM orders WHERE amount > 1000;
COMMIT;

Engines do not implement the levels identically

This is where most explanations stop and most bugs start. The standard says which anomalies a level must prevent, not how, and it lets an engine be stricter than asked. Real databases take that freedom, so the same words mean different things.

PostgreSQL accepts all four names but implements only three distinct levels: its read uncommitted behaves exactly like read committed, because its version-based design has no dirty-read mode. Its repeatable read is stronger than the standard demands and blocks phantom reads outright, because it is really snapshot isolation, giving each transaction a frozen view of the database as of its start. Read committed is the PostgreSQL default.

SQL Server also defaults to read committed and enforces the levels mainly through locking. Its repeatable read still allows phantoms, exactly as the standard permits, and you have to reach serializable, which takes range locks, to stop new rows appearing. It adds a separate snapshot level built on row versioning.

MySQL with InnoDB defaults to repeatable read, not read committed, and that repeatable read uses next-key locks to block most of the phantoms the standard would allow. Code that behaves on MySQL out of the box can misbehave when the same logic runs against a read-committed default elsewhere.

The pair to remember: repeatable read forbids phantom reads on PostgreSQL but allows them on SQL Server. Same name, different guarantee. Do not assume the level from its label; read the engine's own documentation.

The cost: serialization failures and retries

Stronger isolation is not free. It comes from the database refusing to let some concurrent work proceed. Picture two checkout transactions that both read the same product with one unit left and both decide the sale is fine. At serializable, PostgreSQL commits one and aborts the other with a serialization failure (SQLSTATE 40001), rather than letting both sell the last unit and leave you with negative stock.

That puts recovery in the application, not the database. Code running at these levels has to catch the failure and run the whole transaction again from the start. A retry is only safe when the work is idempotent, so a replayed sale or transfer does not double up. Deadlocks are a sibling problem and end the same way, in a forced retry.

So isolation matters in an OLTP database and in a data pipeline alike. A checkout, a nightly reconciliation, and a bulk load do not need the same level, and setting the strictest one everywhere just buys conflicts and retries you did not need.