SQL Isolation Levels and Phantom Reads Explained in Java
The four SQL isolation levels and the anomalies they allow, including phantom reads, how PostgreSQL and MySQL differ and how to set a level in Java.

Two transactions that run at the same time can disturb each other. Isolation levels say how much. This explains the anomalies, the levels and what the databases actually do. The Java API is JDBC and Spring on JDK 25.
The anomalies
| Anomaly | What happens |
|---|---|
| Dirty read | You read data another transaction has not committed |
| Non repeatable read | You read a row twice and it changed between reads |
| Phantom read | You run a query twice and new rows match the second time |
| Lost update | Two transactions read, both write, one change vanishes |
The levels
| Level | Dirty | Non repeatable | Phantom |
|---|---|---|---|
READ UNCOMMITTED | possible | possible | possible |
READ COMMITTED | no | possible | possible |
REPEATABLE READ | no | no | possible per the standard |
SERIALIZABLE | no | no | no |
The table is the SQL standard's. Real engines differ. PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, and its REPEATABLE READ uses a snapshot taken at the first query, which also prevents phantoms. MySQL InnoDB's REPEATABLE READ uses a snapshot for plain reads and gap locks for locking reads. Check your database's manual before reasoning about a specific case.
A phantom in code
-- transaction A
select count(*) from booking where room = 7 and day = '2026-12-01'; -- 0
-- transaction B inserts a booking for room 7, commits
-- transaction A, same query
select count(*) from booking where room = 7 and day = '2026-12-01'; -- 1 at READ COMMITTEDIf A then inserts its own booking based on the first count, both bookings exist: a double booking. Raising the level helps only if the database actually detects the conflict, so a unique constraint on (room, day) is the dependable fix. See constraint violations.
Setting the level in Java
@Transactional(isolation = Isolation.SERIALIZABLE)
public void book(long roomId, LocalDate day) { /* ... */ }With plain JDBC:
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);Set it before the transaction starts and reset it before returning the connection to a pool, as described in manual transactions.
The price of SERIALIZABLE
PostgreSQL's serializable mode detects dangerous patterns and aborts one transaction with SQLSTATE 40001. Code must retry on that error, the same pattern used for deadlocks and retries. Throughput drops under contention.
Alternatives to a higher level
Use the lowest level that the invariant needs, then protect specific rows:
select * from account where id = ? for update; -- lock the rowOptimistic locking with a version column, described in stale object state, detects lost updates without long locks.
See an anomaly with two sessions
Reading about isolation is not the same as watching it. Open two psql sessions against a scratch PostgreSQL database and run this:
-- both sessions
create table doctor (name text primary key, on_call boolean);
insert into doctor values ('ann', true), ('bob', true);
-- session 1 -- session 2
begin isolation level repeatable read; begin isolation level repeatable read;
select count(*) from doctor where on_call; select count(*) from doctor where on_call; -- both see 2
update doctor set on_call=false update doctor set on_call=false
where name='ann'; where name='bob';
commit; commit;Both commits succeed, and nobody is on call. Each transaction checked the rule on its own snapshot and changed a different row, so no row-level conflict was detected. This is write skew, and it is allowed at REPEATABLE READ in PostgreSQL. Change both sessions to serializable and one commit fails with could not serialize access due to read/write dependencies among transactions, SQLSTATE 40001. That failure is the feature: the database refused a state that no serial order could produce.
What MySQL does differently
InnoDB's default REPEATABLE READ reads from a snapshot for plain select, so repeating a query gives the same rows. A locking read, such as select ... for update, instead reads the latest committed data and takes next-key locks, which lock the gaps between index records as well as the records. The gap locks are what stop another transaction from inserting into the range you just scanned, so phantoms are blocked by locking and not by versioning. The price is that unrelated inserts into a locked range wait, and two transactions that each lock a range and then insert into it can deadlock. If your MySQL workload shows deadlocks on inserts, READ COMMITTED removes most gap locking, at the cost of seeing new rows between reads.
Choosing a level
Our default advice is blunt. Stay on the database default for ordinary web traffic, because the default has had years of tuning behind it. Use SERIALIZABLE on PostgreSQL for the few transactions that enforce a rule across rows, such as the on-call example, and wrap them in retry logic. Prefer a constraint, a unique index or an exclusion constraint where one can express the rule, because a constraint is checked at any isolation level and costs nothing in concurrency. Reach for SELECT ... FOR UPDATE when you know which rows must not change, since it documents the intent and does not depend on a connection setting.
Retrying a serialization failure
for (int attempt = 1; ; attempt++) {
try {
return tx.execute(status -> bookRoom(roomId, day));
} catch (CannotSerializeTransactionException | DeadlockLoserDataAccessException e) {
if (attempt == 3) throw e;
}
}The retry has to start a new transaction, so it wraps the transactional call, not the code inside it. Cap the attempts, add a small randomized delay when contention is high, and make sure the work is safe to repeat, which means no emails or external calls inside the transaction.
Reading the level you really have
Do not assume a connection runs at the level you configured. A pool, a framework default or a proxy such as PgBouncer in transaction mode can change what a session sees. Check from inside the application, with show transaction_isolation; on PostgreSQL or select @@transaction_isolation; on MySQL, at the start of a request in a staging environment. We have debugged an anomaly for a day only to find that the service ran at the default while the code comment claimed SERIALIZABLE.
Long transactions make every level worse
An anomaly needs two transactions to overlap, and the longer a transaction lives, the more overlap there is. A transaction that calls a remote service while holding row locks, or one that stays open while a user thinks, multiplies the conflicts at any isolation level. Keep the transaction to the database work, do remote calls before it starts or after it commits, and measure the age of your oldest open transaction. On PostgreSQL, pg_stat_activity shows it, and a transaction that has been idle for minutes also blocks vacuum from cleaning dead rows, a separate cost that people forget.
A test that proves the invariant
Write a concurrency test for any rule you protect with isolation or locking. Start two threads with a CountDownLatch so that both read before either writes, run the booking logic in each, and assert that exactly one succeeds. Run it a few hundred times in a loop, because a race that appears once in fifty runs still shows up in production daily. If the test passes only when you add a sleep, the protection is accidental.
Where this does not apply
Reads of data that tolerates being slightly stale, such as reports, need no strict level. The behaviour for your database is specified in the PostgreSQL transaction isolation chapter and the MySQL InnoDB isolation levels.


