A lot of data bugs look impossible when you read the code in isolation. Then traffic arrives, two requests overlap, and the database allows both to commit without raising a hand. ✅ The application did what it was programmed to do. The database also did what *it* was programmed to do. The problem is that your mental model of isolation often has less to do with reality than the configuration name suggests.
Take a scheduling system for doctors. The business rule is simple: at least one doctor must remain on call. Before allowing someone to clock out, the app checks how many doctors are still on duty. If the count is greater than one, it proceeds. That logic reads fine. But if two doctors leave at the same moment, both transactions can see 2, both can decide the invariant is safe, and both can commit. Now nobody is on call. ⚠️
That failure is the core of isolation. It is not about whether a single statement is correct. It is about what each transaction is permitted to observe while other work is happening at the same time. And once you compare engines, the uncomfortable part appears quickly: names like Read Committed, Repeatable Read, and Serializable sound portable, but they are not.
🧠 What Isolation Really Controls
A transaction groups several operations into one all-or-nothing unit. If one step fails, everything rolls back. Most developers internalize that part early.
The harder part is concurrency. Real databases are serving many transactions at once, sometimes thousands. Each engine must decide what one transaction can see of another transaction’s changes while both are active. That policy is isolation.
In the ideal world, every transaction would run alone, one after another. That is serial execution, and it is correct by definition. 🚀 It is also too slow for most real systems. So databases overlap transactions and try to preserve the illusion that they ran in some serial order. The chosen isolation level determines how strong that illusion is and what anomalies are tolerated.
At a high level, the familiar levels are supposed to mean this:
Read Committed: you never read uncommitted changes from another transaction.
Repeatable Read: reading the same row twice in one transaction returns the same result.
Serializable: the final outcome must match some one-at-a-time execution order.
There is another important axis too:
Pessimistic concurrency assumes conflicts are likely, so it locks early and makes transactions wait.
Optimistic concurrency assumes conflicts are uncommon, allows overlap, then aborts transactions later if a dangerous pattern is detected.
Two systems can expose the same isolation label while choosing opposite strategies underneath. That difference matters for correctness, latency, throughput, and retry behavior. 📌
🏷️ Why the Standard Created Confusion
The root problem goes back to SQL-92. The standard described isolation levels in terms of a few bad outcomes they must disallow:
dirty reads
non-repeatable reads
phantom reads
That sounds neat. It is also incomplete.
The doctor example above does not require any dirty read, non-repeatable read, or phantom read. Both transactions can operate on fully committed data and still break the invariant. The standard’s grid simply left out several important anomalies that show up constantly in production systems.
The missing ones include:
Dirty write: one transaction overwrites another transaction’s uncommitted write.
Lost update: two transactions read the same value, compute independently, and one overwrites the other.
Read skew: a transaction reads related values that never coexisted at a single moment.
Write skew: two transactions verify a shared constraint, then update different rows, leaving the invariant broken.
Write skew is exactly what hits the on-call doctors example. Both sessions read the same logical condition, each modifies a different row, and the business rule collapses even though no row-level write conflict exists. ⚠️
This is why Snapshot Isolation caused so much trouble conceptually. It blocks dirty reads, non-repeatable reads, and phantoms, so by the old checklist it looks as strong as serializable behavior. But it still permits write skew, which means it is not truly serializable. That puts it in a gap the original standard did not model well.
A more precise way to think about this is with dependency graphs: transactions are nodes, and edges represent read/write relationships. If the dependency graph contains a cycle, the history is not serializable. That framing is cleaner because it describes the actual guarantee instead of listing a few symptoms. 🛠️
🐘 PostgreSQL: Transparent, but Still Tricky
PostgreSQL is more candid than most vendors. Its documentation is fairly direct about what its levels really do. Even so, the ANSI names still mislead people.
🔄 Read Committed in PostgreSQL
PostgreSQL defaults to Read Committed, but the snapshot is taken per statement, not per transaction. That means two SELECT statements inside one transaction can see different committed states.
-- Session A -- Session B
BEGIN;
SELECT balance FROM accounts
WHERE user_id = 42;
-- Returns: 500
UPDATE accounts SET balance = 200
WHERE user_id = 42;
COMMIT;
SELECT balance FROM accounts
WHERE user_id = 42;
-- Returns: 200
COMMIT;That is expected behavior.
The stranger case shows up on writes. During UPDATE ... WHERE, PostgreSQL checks the predicate against the statement snapshot. If a matching row is locked by another transaction, it waits. After that other transaction commits, PostgreSQL re-fetches the changed row and reevaluates the WHERE clause for that row only. This is EvalPlanQual, often shortened to EPQ.
-- accounts: (user_id=1, status='active', balance=1000)
-- Session A -- Session B
BEGIN; BEGIN;
UPDATE accounts SET status = 'frozen'
WHERE user_id = 1;
UPDATE accounts SET balance = 0
WHERE status = 'active';
-- Blocks, waiting for Session B
COMMIT;
-- EPQ fires: re-checks the row.
-- status is now 'frozen', not 'active'.
-- Row no longer matches WHERE.
-- UPDATE affects 0 rows.
COMMIT;So one UPDATE can effectively span two different moments in time. If your application ignores affected-row counts, this kind of silent skip is easy to miss. 📌
📸 Repeatable Read in PostgreSQL
At Repeatable Read, PostgreSQL gives the transaction a single stable snapshot. Reads are consistent across the entire transaction, and phantoms are prevented too, which is stronger than the old ANSI description.
But this is still Snapshot Isolation, and Snapshot Isolation permits write skew.
-- on_call_shifts: (doctor_id=1, on_call=true), (doctor_id=2, on_call=true)
-- Invariant: COUNT(*) WHERE on_call = true must be >= 1
-- Session A -- Session B
BEGIN ISOLATION LEVEL BEGIN ISOLATION LEVEL
REPEATABLE READ; REPEATABLE READ;
SELECT COUNT(*) FROM on_call_shifts
WHERE on_call = true;
-- Returns: 2 (safe to leave)
SELECT COUNT(*) FROM on_call_shifts
WHERE on_call = true;
-- Returns: 2 (safe to leave)
UPDATE on_call_shifts SET on_call = false
WHERE doctor_id = 1;
UPDATE on_call_shifts SET on_call = false
WHERE doctor_id = 2;
COMMIT; COMMIT;
-- Both succeed. Zero doctors on call.Both transactions saw a valid snapshot. Both made a reasonable decision locally. The invariant still died. That is the hole. ⚠️
✅ Serializable in PostgreSQL
PostgreSQL’s Serializable mode uses Serializable Snapshot Isolation. It does not block readers. Instead, it tracks read/write dependencies and aborts a transaction if it detects a dangerous pattern that could form a non-serializable history.
When that happens, you get:
ERROR: could not serialize access due to read/write dependencies among transactions
SQLSTATE: 40001This catches the doctor scenario, but it changes your application contract. Transactions can run almost to completion and then fail at commit time. If your app does not retry on 40001, your “safe” transaction path is just a path that fails randomly under contention. 🔁
There is also a practical wrinkle: under memory pressure, PostgreSQL can track dependencies more coarsely, which increases false-positive aborts.
In short:
Read Uncommittedis silently treated likeRead CommittedRead Committeduses statement snapshotsRepeatable Readis Snapshot IsolationSerializableis optimistic and requires retry logic
🐬 MySQL/InnoDB: The Default You Probably Never Chose
MySQL’s default is Repeatable Read, and that default behaves unlike the simplified version most developers imagine.
🧩 Two Concurrency Models in One Transaction
In InnoDB Repeatable Read, plain SELECT statements use a transaction snapshot. But SELECT ... FOR UPDATE, SELECT ... FOR SHARE, and DML operate using next-key locks and inspect the latest committed state rather than the frozen snapshot.
That means a single transaction can have two different views of the same data.
-- orders has 3 rows: order_id 1, 2, 3, all status='pending'.
-- Session B inserts order_id 4 (pending) and commits AFTER
-- Session A's first read but BEFORE its locking read.
-- Session A
BEGIN ;
SELECT COUNT (*) FROM orders WHERE status = 'pending' ;
-- Returns: 3 (MVCC snapshot from transaction start)
-- ... Session B inserts order_id=4, commits ...
SELECT COUNT (*) FROM orders WHERE status = 'pending' FOR UPDATE ;
-- Returns: 4 (reads current committed data, bypasses snapshot)Three, then four, inside one transaction, with the same filter. 🤨 The result depends on whether the read was locking or non-locking.
⚠️ Write Skew Depends on Three Extra Words
Under MySQL Repeatable Read, write skew is prevented only if your check uses SELECT ... FOR UPDATE. Use a plain SELECT, and the invariant can still break. So whether your business rule holds can depend on typing those three words.
💥 Gap Locks and Surprising Deadlocks
Next-key locks also lock index gaps to prevent phantoms. Two transactions can both hold compatible gap locks, then deadlock when both try to insert into that gap.
-- Session A -- Session B
BEGIN; BEGIN;
SELECT * FROM orders
WHERE order_id = 999
FOR UPDATE;
-- Row doesn't exist.
-- Gap lock acquired on the gap
-- containing position 999.
SELECT * FROM orders
WHERE order_id = 999
FOR UPDATE;
-- Same gap lock. Compatible. Acquired.
INSERT INTO orders (order_id, ...)
VALUES (999, ...);
-- Needs insert-intention lock.
-- Blocked by Session B's gap lock.
INSERT INTO orders (order_id, ...)
VALUES (999, ...);
-- Needs insert-intention lock.
-- Blocked by Session A's gap lock.
-- DEADLOCK: ERROR 1213 (40001)This is not rare. It appears in ordinary check-then-insert flows such as idempotent writes and custom upserts. 🛠️
🔒 Serializable in MySQL
MySQL’s Serializable mode is pessimistic. Plain SELECT statements become shared locking reads when autocommit is off. That gives true serializable behavior, but concurrency can drop sharply because reads now block writes and vice versa.
This is the opposite of PostgreSQL’s approach:
PostgreSQL
Serializable: let it run, maybe abort laterMySQL
Serializable: lock early, avoid the anomaly by waiting
Same label, very different system.
🧱 Long Transactions Hurt Purge
There is another operational cost. InnoDB keeps old row versions in undo logs for snapshot reads. Long Repeatable Read transactions prevent those old versions from being purged. On a busy system, history length grows, undo tablespaces expand, purge threads lag, and system-wide performance suffers. Even a read-only report can become a write-path problem. 📉
🏛️ Oracle: No Repeatable Read, and “Serializable” Isn’t What You Think
Oracle offers only two isolation levels:
Read CommittedSerializable
Try to request Repeatable Read, and Oracle rejects it.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
-- ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }🔁 Read Committed and Statement Restart
Oracle’s Read Committed uses statement-level snapshots based on undo segments. That sounds ordinary. The unusual part is how writes handle conflicts.
If an UPDATE encounters a row that changed since the statement snapshot, Oracle can restart the entire statement from a hidden savepoint, rerunning it in a locking mode. No visible error is raised.
That means any attached side effects can happen twice:
row triggers fire again
package state may become inconsistent
file writes or emails triggered externally can duplicate
PostgreSQL’s EPQ reevaluates one row. Oracle can rerun the full statement. Same SQL, different engine behavior, different consequences. ⚠️
🏷️ Oracle Serializable Is Snapshot Isolation
Oracle’s Serializable gives a transaction-level snapshot and enforces first-committer-wins for writes to the same row. If that conflict occurs, you get:
ORA-08177: can't serialize access for this transactionBut that is not enough for true serializability. Disjoint-row write skew still passes.
-- Session A (SERIALIZABLE) -- Session B (SERIALIZABLE)
SELECT COUNT(*) FROM on_call_shifts
WHERE on_call = 1;
-- Returns: 2
SELECT COUNT(*) FROM on_call_shifts
WHERE on_call = 1;
-- Returns: 2
UPDATE on_call_shifts SET on_call = 0
WHERE doctor_id = 1;
UPDATE on_call_shifts SET on_call = 0
WHERE doctor_id = 2;
COMMIT; COMMIT;
-- Both succeed. No ORA-08177.
-- Zero doctors on call.That makes Oracle’s highest isolation level much closer to Snapshot Isolation than to full serializable execution. For workloads dominated by single-row OLTP changes, the difference may stay hidden. In modern web apps with multi-row invariants, it matters a lot. 📌
🧱 DB2: The Vendor That Mostly Matched the Textbook
DB2 stayed close to the lock-based interpretation of the standard. It does not rely on MVCC snapshots in the same way the others do. Instead, it uses locking directly:
Uncommitted ReadCursor StabilityRead StabilityRepeatable Read
Its strongest mode enforces correctness by locking all relevant rows, and even rows evaluated during a scan, not just rows finally returned. That prevents write skew mechanically because the read itself blocks conflicting writes.
The tradeoff is predictable: under contention, locks pile up. If lock memory thresholds are crossed, DB2 can escalate from row locks to a table lock. At that point, concurrency collapses into a queue. ✅ Correct, but expensive.
DB2 later introduced “currently committed” semantics to soften some read blocking, reconstructing the most recent committed row version from log data. That helps, but it also shows why other vendors moved toward looser or hybrid models.
🔄 Same Logic, Different Outcomes
The same doctor transaction gives four different results depending on the backend:
PostgreSQL Repeatable Read: both commits succeed, write skew occurs
Oracle Serializable: same outcome, because it is Snapshot Isolation-like
PostgreSQL Serializable: one transaction aborts with
40001MySQL Serializable: one transaction blocks behind the other, invariant preserved
That is the real lesson. The SQL text can be identical while the isolation behavior is not.
🛒 Two Production-Style Failures
One common pattern is checkout logic:
BEGIN ;
SELECT available_stock FROM products WHERE product_id = 7042 ;
-- Returns: 1
-- App logic: 1 >= 1, proceed
UPDATE products SET available_stock = available_stock - 1
WHERE product_id = 7042 ;
INSERT INTO orders (product_id, user_id, quantity)
VALUES ( 7042 , @user_id, 1 );
COMMIT ;If two users buy the final unit concurrently, both can read 1. On MySQL Repeatable Read, the second UPDATE waits, then decrements the already-updated row after the first commit. Stock becomes -1. Two orders, one product. ⚠️
Another ugly case appears during migrations. An app may run for years on Oracle using Serializable, never implementing retry logic because Oracle rarely surfaces the relevant conflicts. Move that same workload to PostgreSQL Serializable, and suddenly SQLSTATE 40001 starts appearing. PostgreSQL did not create a new bug. It exposed concurrency problems the previous engine allowed quietly. 🚨
🧰 Why ORMs Don’t Solve This
ORMs are useful, but transaction isolation is where portability breaks.
A setting like @Transactional(isolation = REPEATABLE_READ) does not normalize vendor semantics. It often just forwards the SQL isolation request to the backend:
PostgreSQL: Snapshot Isolation behavior
MySQL: MVCC plus next-key locking hybrid
Oracle:
ORA-02179
Likewise, same-row optimistic locking features do not catch write skew across different rows. If your invariant spans multiple entities, version columns alone are not enough.
Tests usually miss this too. Most integration suites use one connection, or wrap each test in a rollback-only transaction. No overlap means no anomaly. If you want to catch write skew or lost updates, you need concurrent tests with two connections and deliberate interleaving. 🧪
🛠️ What To Do Instead
1. Check the real default
-- PostgreSQL
SHOW default_transaction_isolation;
-- MySQL
SELECT @@transaction_isolation;
-- Oracle
-- No session query. It's Read Committed, always,
-- unless someone set it explicitly, which almost nobody does.If you have never checked this, then part of your correctness model is based on assumption.
2. Choose by behavior, not by label
Do not say, “we need Repeatable Read.” Say:
we must prevent write skew on
on_call_shiftswe need phantom protection for reservation range scans
we need same-row lost-update protection only
That phrasing is verifiable. The label alone is not.
3. On PostgreSQL Serializable, retries are mandatory
function executeWithRetry(txnFn, maxRetries = 5):
for attempt in 1..maxRetries:
try:
BEGIN ISOLATION LEVEL SERIALIZABLE
result = txnFn()
COMMIT
return result
catch error:
if error.code == '40001' AND attempt < maxRetries:
ROLLBACK
sleep(random(0, 2^attempt * 10) ms) // jitter + backoff
continue
throw errorRetry the whole transaction, not the failed statement. Add jitter. Cap retries. 🔁
4. Use SELECT ... FOR UPDATE as the portable fallback
When a read must remain valid until the write, lock explicitly.
SELECT COUNT (*) FROM on_call_shifts
WHERE shift_date = '2024-01-15' AND on_call = true
FOR UPDATE ;That can prevent write skew across vendors, but it shifts you toward pessimistic concurrency. Throughput falls, and deadlock risk rises, especially on MySQL. Use it where the invariant truly matters.
5. Test the invariant with two connections
conn_a = connect(); conn_b = connect()
conn_a.execute("BEGIN ISOLATION LEVEL REPEATABLE READ")
conn_b.execute("BEGIN ISOLATION LEVEL REPEATABLE READ")
conn_a.execute("SELECT COUNT(*) FROM on_call_shifts WHERE on_call = true")
conn_b.execute("SELECT COUNT(*) FROM on_call_shifts WHERE on_call = true")
conn_a.execute("UPDATE on_call_shifts SET on_call = false WHERE doctor_id = 1")
conn_b.execute("UPDATE on_call_shifts SET on_call = false WHERE doctor_id = 2")
conn_a.execute("COMMIT"); conn_b.execute("COMMIT")
assert count_on_call() >= 1 // Fails on PostgreSQL RR and Oracle SerializableThat is not a performance test. It is a correctness test with concurrency as the trigger. ✅
6. Audit isolation before migrations
If you move databases, review every critical transaction path:
where the target becomes stricter, add retry handling
where the target becomes weaker, look for anomalies that can now commit silently
document the expected isolation behavior next to the code, not in forgotten docs
🧭 The Bigger Lesson
This is not only a database story. It is also a specification story. When a standard defines something mainly by the failures it forbids, implementations can differ wildly while still claiming compliance.
That is what happened here. Four major databases use the same isolation vocabulary while shipping fundamentally different mechanisms:
optimistic graph-based aborts
lock-heavy serial execution
Snapshot Isolation under a stronger-sounding name
classic lock-based correctness with expensive escalation
So the safe habit is not “trust the label.” The safe habit is verify the behavior.
🔍 TL;DR Summary
✅ Isolation level names are not portable contracts; different databases attach different mechanisms to the same label.
⚠️ Snapshot-style systems can still allow write skew even when they block dirty reads, non-repeatable reads, and phantoms.
🔁 PostgreSQL
Serializableis optimistic and requires application-level retry onSQLSTATE 40001.🔒 MySQL
Serializableuses locking, while MySQLRepeatable Readmixes snapshot reads with locking reads in surprising ways.🏷️ Oracle’s
Serializablebehaves like Snapshot Isolation for many practical cases and can allow disjoint-row invariant violations.🧪 The only reliable approach is to test your real invariants concurrently on the exact backend and version you run.