skip to content

As an architect, how do you decide when to raise the isolation level versus using other concurrency-control tools?

level: principalimportance: nice to knowfreq 30%

answer

  1. Isolation is transaction-wide, anomalies are usually local
  2. @Version optimistic = rare conflicts, retry
  3. FOR UPDATE pessimistic = hot rows
  4. Raise isolation only for multi-row read-set consistency
  5. Constraints often beat SERIALIZABLE; always plan retries

basics

~20 s

Default to the DB's level and solve specific anomalies with targeted tools: optimistic locking (JPA @Version) for rare conflicts, pessimistic locks (SELECT ... FOR UPDATE) for hot rows. Reserve SERIALIZABLE for genuine serialization needs, and always plan retries.

solid answer

~50 s

Raising the global isolation level is a blunt instrument: it slows every query in the transaction and, at SERIALIZABLE, introduces retryable failures or heavy locking. So I default to the database's own level (usually READ_COMMITTED) and address specific anomalies surgically. For lost updates and most write conflicts I use **optimistic locking** — JPA `@Version` — which detects a stale write at commit and throws `OptimisticLockException`, cheap under low contention and needing a retry. For hot, high-contention rows I use **pessimistic locking** (`@Lock(PESSIMISTIC_WRITE)` / `SELECT ... FOR UPDATE`) to serialize just those rows. I raise isolation to REPEATABLE_READ or SERIALIZABLE only when a whole read-set must stay consistent (e.g. reports, invariant checks across many rows) and locking individual rows isn't enough. Whenever I use SERIALIZABLE — especially on PostgreSQL's SSI — I build in transaction retry. I also weigh cross-DB portability, connection-pool pressure, and deadlock risk.

code

java · 18 lines
java
// Optimistic locking solves lost updates without raising isolation.
@Entity
public class Account {
    @Id private Long id;
    @Version private long version;      // checked & bumped on UPDATE
    private BigDecimal balance;
}

public interface SeatRepo extends JpaRepository<Seat, Long> {
    // Pessimistic lock for a genuinely hot row: SELECT ... FOR UPDATE
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<Seat> findById(Long id);
}

// Reserve SERIALIZABLE for a phantom-sensitive invariant, with retry.
@Retryable(retryFor = ObjectOptimisticLockingFailureException.class)
@Transactional(isolation = Isolation.SERIALIZABLE)
public void bookExclusiveSlot(long roomId, LocalDate day) { /* ... */ }

go deeper

for a junior

Likely only knows to raise the isolation level.

for a middle

Knows @Version and FOR UPDATE exist but may over-apply isolation.

for a senior

Matches tool to anomaly and adds retry handling.

for a principal

Reasons about contention profile, portability, deadlock/pool cost, and constraint-first designs; treats global isolation as a last resort.

## The decision frame Isolation level is a **transaction-wide** setting: raising it affects *every* statement in the transaction and trades throughput for consistency. Most consistency problems are **localized** to a few rows, so a global isolation bump is usually the wrong tool. The architect's job is to match the mechanism to the specific anomaly. ## The anomalies vs. tools matrix | Problem | Cheapest appropriate tool | |---|---| | Lost update on a single entity | Optimistic locking (`@Version`) | | Contended hot row (inventory counter) | Pessimistic write lock (`SELECT ... FOR UPDATE`) | | Need a stable multi-row read-set (report, invariant) | REPEATABLE_READ / SERIALIZABLE | | Cross-row write invariant (no double-booking) | SERIALIZABLE, or a uniqueness/exclusion constraint | ## Optimistic locking (default choice) Add a version column: ```java @Entity class Account { @Id Long id; @Version long version; // Hibernate checks & increments on update BigDecimal balance; } ``` At commit, if another transaction changed the row, the `UPDATE ... WHERE version = ?` matches zero rows and Hibernate throws `OptimisticLockException` (Spring `ObjectOptimisticLockingFailureException`). Best when **conflicts are rare**: no locks held, high concurrency, just retry on the occasional clash. This solves lost updates without touching isolation at all. ## Pessimistic locking (hot rows) ```java @Lock(LockModeType.PESSIMISTIC_WRITE) Optional<Seat> findById(Long id); // SELECT ... FOR UPDATE ``` Serializes access to just the locked rows. Best when **conflicts are frequent** and retrying optimistically would thrash. Costs: holds row locks for the transaction's duration, risks deadlocks, reduces concurrency on those rows. ## When to actually raise isolation Raise it when the guarantee you need spans a **set of reads**, not one row: - **REPEATABLE_READ** when a transaction re-reads rows and must see stable values / a consistent snapshot (e.g. a financial report; on Postgres this also blocks phantoms). - **SERIALIZABLE** when correctness depends on the *absence* of rows another transaction might insert (phantom-sensitive invariants like "no overlapping bookings") and you can't express it as a DB constraint. ## Operational costs to weigh - **Retries**: higher levels (especially Postgres SSI SERIALIZABLE, and optimistic locking) surface as retryable exceptions — you need a retry strategy (Spring Retry) with idempotent transactions. - **Deadlocks**: pessimistic locks and higher isolation increase deadlock probability; order lock acquisition consistently. - **Portability**: `DEFAULT` behaves differently across engines; an explicit level may be unsupported (Oracle) — pin it deliberately. - **Pool pressure**: `REQUIRES_NEW` at a higher level holds a second connection; SERIALIZABLE transactions may run longer. - **Constraint-first thinking**: often a **unique/exclusion constraint** (Postgres `EXCLUDE`), a check constraint, or an idempotency key is a cheaper, more robust guard than SERIALIZABLE. ## Rule of thumb Start at `DEFAULT`. Reach for `@Version` first, pessimistic locks for hot rows, and only escalate to REPEATABLE_READ/SERIALIZABLE for genuine multi-row read consistency — always with retries and a clear picture of the target database's semantics.

  • When would you prefer optimistic over pessimistic locking?
    When write conflicts on the same row are rare. Optimistic locking holds no locks and scales well under low contention, paying only an occasional retry. Under high contention it thrashes on retries, so pessimistic (FOR UPDATE) locking that serializes access is better there.
  • Give a case where a database constraint beats raising isolation to SERIALIZABLE.
    Preventing double-booking: a PostgreSQL `EXCLUDE` constraint (or a unique index on the slot) enforces non-overlap at the storage layer for every writer, cheaply and without the retry/serialization-failure overhead of SERIALIZABLE, and it can't be bypassed by a code path that forgot the isolation setting.

saying these in an interview costs you the question

  • Reaching for SERIALIZABLE as the first tool for any concurrency bug
  • Using optimistic locking under heavy contention and drowning in retries
  • Raising isolation but forgetting to add retry handling
  • Ignoring that a unique/exclusion constraint may solve the invariant more cheaply

context

resolving links…