Transaction Isolation Levels: What the ORM Doesn't Tell You Behind transaction()

Two users press "withdraw" at the same moment. The account balance is 100 dollars, and each withdraws 80. Both requests read the balance (100) before either wrote, so both permitted the withdrawal, and the account ended at "minus 60." The code did not visibly fail; each request checked the balance and found it sufficient. The problem is somewhere most developers do not see: the transaction isolation level. And when you wrap the code in `transaction()` in the ORM, you are choosing — often without knowing — an isolation level that determines exactly this behavior. Let's reveal what the ORM hides.

What Does "Isolation" Mean in ACID?

The I in ACID means isolation: to what degree one transaction's changes are hidden from another running in parallel. The paradox is that full isolation (as if transactions ran one after another) is costly in performance, and low isolation is fast but opens the door to hidden data errors. So the SQL standard defines four levels, each a trade-off between consistency and concurrency. Understanding this trade-off is what separates a developer who writes `transaction()` by instinct from one who knows what it actually does.

The Three Anomalies: What Are We Trying to Prevent?

Before the levels, you must understand the "read phenomena" that isolation protects against, ordered from most dangerous:

Dirty Read: your transaction reads data modified by another transaction that has not yet committed. If that transaction rolls back, you have read data that never existed. This is the most dangerous anomaly.

Non-repeatable Read: you read a row, then read it again in the same transaction and find a different value, because another transaction modified and committed it in between.

Phantom Read: you run the same query twice and get a different set of rows, because another transaction inserted or deleted rows matching your condition in between.

The Four Levels, From Weakest to Strongest

Each level prevents more anomalies at the cost of less concurrency:

1. Read Uncommitted: the weakest. Allows all anomalies including dirty reads. Rarely used. (Note: PostgreSQL does not actually implement it, treating it as Read Committed.)

2. Read Committed: prevents only dirty reads. You always see committed data, but it may differ between two reads. It is the default in PostgreSQL, and suitable for most web applications.

3. Repeatable Read: prevents dirty and non-repeatable reads. Within the transaction, all reads see a fixed "snapshot" from the first read. It is the default in MySQL InnoDB.

4. Serializable: the strongest. Prevents all anomalies, as if transactions ran one after another. The most costly in performance and the most prone to serialization errors.

Here Is Where What the ORM Hides Begins: The Defaults Differ

The most dangerous point the abstraction hides: the default level differs between databases. PostgreSQL defaults to Read Committed, while MySQL InnoDB defaults to Repeatable Read. This means the same code, with the same ORM, may behave differently on two different databases. An application developed and tested on MySQL then migrated to PostgreSQL may suddenly start seeing "non-repeatable reads" that never appeared before, simply because the default changed. The ORM does not warn you about this; it passes your request along silently.

An Even Bigger Surprise: Repeatable Read Is Not One Thing

Even within the same level, the implementation differs fundamentally. The standard says Repeatable Read "allows" phantom reads. But:

In MySQL InnoDB, it prevents phantom reads via "gap locks" that lock the ranges between index entries. The hidden side effect: they may cause unexpected lock waits on INSERT statements.

In PostgreSQL, it also prevents phantom reads but via the MVCC mechanism (multi-version concurrency control), stronger behavior than the standard requires. Most importantly: in PostgreSQL, attempting to update a row modified by another transaction under Repeatable Read produces an explicit error: "could not serialize access." Many consider this better than silently allowing the modification as in MySQL, because it prevents a confusing state rather than hiding it.

-- In MySQL (default: Repeatable Read)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- returns 500
-- Another transaction commits balance = 800
SELECT balance FROM accounts WHERE id = 1; -- still returns 500 (fixed snapshot)
COMMIT;

Serializable: Two Radically Different Implementations

Even the strongest level is implemented in two different ways with divergent practical effects. PostgreSQL uses "Serializable Snapshot Isolation" (SSI): it monitors the read/write dependency graph among transactions, and when it detects a dangerous pattern that could lead to an anomaly, it aborts the minimum number of transactions with a serialization error. MySQL InnoDB relies on locks (gap locks and next-key locks). The practical difference: with PostgreSQL your application must be ready to retry transactions that fail with a serialization error, while with MySQL you usually face lock waits or a deadlock.

The Practical Lesson: Do Not Leave the Decision to Chance

The biggest mistake is to write `transaction()` and assume it "solves concurrency problems." It only defines the atomicity scope, but it operates at your database's default isolation level — which may not suit your case. The practical rule: do not raise the isolation level to Serializable for "safety" without need, as its cost in performance and serialization errors is high. Instead, for critical operations on shared state (like debiting a balance), use explicit optimistic or pessimistic locking at the appropriate isolation level. An example with a pessimistic lock that prevents the double-withdrawal problem we started with:

-- Explicit row lock prevents the double withdrawal
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- locks the row
-- The other request waits here until we finish
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

In the ORM, this usually corresponds to an explicit call like lockForUpdate in Laravel, not just `transaction()`.

The practical bottom line: `transaction()` in the ORM is not a magic concurrency button, but a wrapper hiding an important decision called the isolation level. Know your database's default (Read Committed in PostgreSQL, Repeatable Read in MySQL), understand that the implementation differs fundamentally between them even at the same level, and choose isolation and locks consciously, not by instinct. The difference between an application that holds up under concurrency and one that silently leaks data is not in the visible code, but in this layer the abstraction hides — and whoever understands it writes systems that do not collapse when two users press the button at the same moment.