skip to content

What database-specific caveats must you know before relying on a particular `isolation` level in Spring?

level: seniorimportance: should knowfreq 48%

answer

  1. Spring only forwards to setTransactionIsolation
  2. Oracle: only READ_COMMITTED + SERIALIZABLE
  3. Defaults differ: MySQL=RR, Postgres/Oracle=RC
  4. Postgres maps READ_UNCOMMITTED -> READ_COMMITTED
  5. Postgres SERIALIZABLE = SSI, must retry 40001

basics

~20 s

Spring just forwards the level to the driver; the DB decides what it means. Not all levels are supported (Oracle has only READ_COMMITTED and SERIALIZABLE), default levels differ (MySQL=REPEATABLE_READ, Postgres/Oracle=READ_COMMITTED), and some engines silently map or strengthen levels.

solid answer

~40 s

`@Transactional(isolation=...)` is only a request Spring passes to `Connection.setTransactionIsolation`; the actual semantics belong to the database. Key caveats: (1) Not every level exists everywhere — Oracle supports only READ_COMMITTED and SERIALIZABLE and will reject READ_UNCOMMITTED/REPEATABLE_READ. (2) `DEFAULT` differs per engine — PostgreSQL and Oracle default to READ_COMMITTED, MySQL InnoDB to REPEATABLE_READ. (3) Engines may silently upgrade a requested level: PostgreSQL treats READ_UNCOMMITTED as READ_COMMITTED. (4) A level can be *stronger* than the standard: PostgreSQL's REPEATABLE_READ (snapshot isolation) also blocks phantoms, and its SERIALIZABLE uses SSI that aborts transactions with serialization_failure you must retry. (5) With `JpaTransactionManager`, custom isolation support depends on the JPA provider/driver and may need extra configuration. So always verify the level against your specific database rather than trusting the SQL-standard table.

code

java · 12 lines
java
// PostgreSQL SERIALIZABLE aborts conflicting txns (SQLState 40001)
// instead of blocking — so wrap in a retry.
import org.springframework.retry.annotation.Retryable;
import org.springframework.dao.CannotSerializeTransactionException;

@Retryable(retryFor = CannotSerializeTransactionException.class,
           maxAttempts = 4)
@Transactional(isolation = Isolation.SERIALIZABLE)
public void reserveSeat(long showId, int seat) {
    // On a serialization conflict Postgres throws; Spring maps it to
    // CannotSerializeTransactionException and this method retries.
}

go deeper

for a junior

Aware that behavior depends on the DB, not just Spring.

for a middle

Knows default levels differ and Oracle lacks some levels.

for a senior

Explains silent mapping/strengthening and the Postgres SERIALIZABLE retry requirement.

for a principal

Weighs raising isolation against optimistic/pessimistic locking and cross-DB portability of DEFAULT.

## Spring only forwards the request Spring's transaction managers translate `Isolation.X` into `java.sql.Connection.setTransactionIsolation(int)`. Spring does **not** implement isolation itself — it hands the number to the JDBC driver, and the database decides what actually happens. That means every guarantee ultimately depends on the DB engine, not on Spring. ## Caveat 1 — Not all levels are supported - **Oracle** implements only **READ_COMMITTED** and **SERIALIZABLE**. It has no READ_UNCOMMITTED and no REPEATABLE_READ. Requesting an unsupported level fails. - If a driver doesn't support a requested level, `setTransactionIsolation` throws `SQLException`, which surfaces as a Spring `TransactionException`/`InvalidIsolationLevelException`. ## Caveat 2 — `DEFAULT` is engine-specific - **PostgreSQL**: default READ_COMMITTED. - **Oracle**: default READ_COMMITTED. - **MySQL InnoDB**: default **REPEATABLE_READ**. - **SQL Server**: default READ_COMMITTED (with optional RCSI snapshot variant). So the same `@Transactional` (no explicit isolation) behaves differently across environments — a real portability trap when dev and prod use different databases. ## Caveat 3 — Silent mapping to a stronger level - **PostgreSQL** treats a request for **READ_UNCOMMITTED as READ_COMMITTED** — it simply never allows dirty reads. So you cannot get dirty reads on Postgres even if you ask for them. ## Caveat 4 — Real levels can exceed the standard - **PostgreSQL REPEATABLE_READ** is snapshot isolation: it prevents non-repeatable reads **and phantom reads**, going beyond the SQL-standard minimum. - **PostgreSQL SERIALIZABLE** uses **Serializable Snapshot Isolation (SSI)**: instead of heavy locking it detects conflicts and **aborts** one transaction with a `serialization_failure` (SQLState 40001). Your code must be prepared to **catch and retry** — otherwise SERIALIZABLE on Postgres will surface as intermittent failures, not blocking. - **MySQL InnoDB REPEATABLE_READ** uses next-key (gap) locking, which prevents many phantom reads that the standard would otherwise permit. ## Caveat 5 — JPA / provider constraints With `JpaTransactionManager`, applying a non-default isolation level requires the transaction manager to reach the underlying JDBC connection and can depend on the JPA provider and driver. Historically some providers ignored the setting unless configured; Hibernate honors it, but this is worth verifying. `DataSourceTransactionManager` (plain JDBC/MyBatis) applies it directly. ## Caveat 6 — Connection pools Spring resets the connection's isolation to its prior value after the transaction, so a pooled connection isn't left contaminated. But if you set a non-default level, be aware the reset adds a round-trip and the pool's baseline level still comes from the driver/DB. ## Practical guidance - Don't trust the generic staircase table for anomaly guarantees — **look up your specific engine's documentation**. - If you rely on SERIALIZABLE on PostgreSQL, implement **retry-on-serialization-failure** (e.g. Spring Retry around `CannotSerializeTransactionException`/`SQLState 40001`). - Prefer `DEFAULT` plus explicit **optimistic locking** (JPA `@Version`) or `SELECT ... FOR UPDATE` pessimistic locking for targeted consistency, rather than globally raising isolation.

  • You set `Isolation.READ_UNCOMMITTED` and expect dirty reads on PostgreSQL — what happens?
    You still never see dirty reads. PostgreSQL accepts the request but silently runs it as READ_COMMITTED; it has no true READ_UNCOMMITTED mode.
  • Why can SERIALIZABLE on PostgreSQL surface as intermittent errors rather than slow queries?
    Postgres implements SERIALIZABLE with Serializable Snapshot Isolation, which detects dangerous read/write conflicts and aborts one transaction with serialization_failure (40001) instead of blocking. Callers must catch it and retry.
  • How would you get strong consistency without globally raising isolation?
    Use targeted locking: JPA optimistic locking via `@Version`, or pessimistic `SELECT ... FOR UPDATE` (`@Lock(PESSIMISTIC_WRITE)`), applied only to the rows that need it — cheaper and more predictable than SERIALIZABLE everywhere.

saying these in an interview costs you the question

  • Assuming every database supports all four levels
  • Assuming DEFAULT is READ_COMMITTED everywhere (it's REPEATABLE_READ on MySQL)
  • Using SERIALIZABLE on Postgres with no retry logic
  • Believing Spring itself enforces the anomaly guarantees

context

resolving links…