skip to content

What operational risks come with pessimistic locking (timeouts, deadlocks, held locks), and how do you mitigate them in Spring Data JPA?

level: seniorimportance: should knowfreq 32%

answer

  1. jakarta.persistence.lock.timeout hint (ms)
  2. deadlock -> victim -> CannotAcquireLockException
  3. consistent lock order (ascending PK)
  4. short tx, no user think-time, no network calls under lock
  5. retry with backoff; prefer optimistic if conflicts rare

basics

~10 s

Pessimistic locks block other transactions, so you risk long waits, deadlocks, and throughput loss. Mitigate with a jakarta.persistence.lock.timeout query hint, consistent lock ordering, short transactions, and never holding a lock across a user round-trip.

solid answer

~40 s

A pessimistic lock is held for the whole transaction and blocks competing transactions, so three things go wrong. First, indefinite waiting — bound it with a @QueryHints hint jakarta.persistence.lock.timeout (ms); on expiry Hibernate throws LockTimeoutException → Spring PessimisticLockingFailureException. Second, deadlocks — two transactions locking rows in opposite order deadlock; the DB kills a victim and you get CannotAcquireLockException. Prevent by locking rows in a consistent order and keeping the lock scope minimal. Third, throughput collapse — because writers serialize behind the lock, you must keep transactions short: acquire the lock, do the read-modify-write, commit fast, and never span an HTTP round-trip or external call. Also set a global spring.jpa lock/transaction timeout, add retry-with-backoff for CannotAcquireLockException, and consider optimistic locking instead when conflicts are actually rare.

code

java · 19 lines
java
interface SeatRepository extends JpaRepository<Seat, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints({ @QueryHint(
        name = "jakarta.persistence.lock.timeout", value = "2000") })
    @Query("select s from Seat s where s.id = :id")
    Optional<Seat> lockSeat(@Param("id") Long id);
}

@Service
class BookingService {
    // transient deadlock/timeout -> retry with backoff
    @Retryable(retryFor = CannotAcquireLockException.class,
               maxAttempts = 3, backoff = @Backoff(delay = 100, multiplier = 2))
    @Transactional(timeout = 5)               // cap held-lock duration
    public void book(List<Long> seatIds) {
        seatIds.stream().sorted()             // consistent lock order!
               .forEach(id -> repo.lockSeat(id).orElseThrow().reserve());
    }
}

go deeper

for a junior

Know pessimistic locks block others and should be held briefly.

for a middle

Configure lock timeouts and understand deadlock-victim exceptions.

for a senior

Design consistent lock ordering, retry-with-backoff, and short transaction scopes; know DB-specific timeout semantics.

for a principal

Set org-wide policy on optimistic-vs-pessimistic, connection-pool implications, and SLA-driven timeout/retry budgets.

## Why pessimistic locking is operationally dangerous A pessimistic lock (`PESSIMISTIC_READ`/`WRITE`/`FORCE_INCREMENT`) is a **real database lock held for the entire transaction**. Any other transaction needing a conflicting lock **blocks** until you commit or roll back. That blocking is the source of every risk below. ## Risk 1 — Unbounded waiting By default a transaction can wait a long time (or until the DB's deadlock/lock-wait timeout) for a held lock. Bound it explicitly with a JPA lock-timeout **query hint**: ```java @Lock(LockModeType.PESSIMISTIC_WRITE) @QueryHints({ @QueryHint( name = "jakarta.persistence.lock.timeout", value = "3000") }) // ms Optional<Row> findByIdForUpdate(@Param("id") Long id); ``` - `jakarta.persistence.lock.timeout` (Jakarta) / `javax.persistence.lock.timeout` (older) — value in **milliseconds**. - On expiry Hibernate throws `jakarta.persistence.LockTimeoutException`, translated to Spring `org.springframework.dao.PessimisticLockingFailureException` (or `CannotAcquireLockException`). - **DB-specific semantics**: PostgreSQL only supports `NOWAIT` (value `0`) and blocking; it does *not* honor an arbitrary millisecond wait for `FOR UPDATE` — the hint is effectively 'fail immediately' vs 'wait'. MySQL/InnoDB and Oracle honor real wait times. Know your dialect. ## Risk 2 — Deadlocks Transaction T1 locks row A then wants B; T2 locks B then wants A. Neither proceeds — a **deadlock**. The database's deadlock detector picks a **victim**, rolls it back, and Hibernate surfaces it as Spring `CannotAcquireLockException` / `DeadlockLoserDataAccessException`. Mitigations: - **Consistent lock ordering** — always acquire locks in a deterministic order (e.g. ascending primary key). This is the single most effective fix. - **Lock fewer rows / narrower scope** — reduces the surface for cycles. - **Retry the victim** with backoff — deadlocks are transient; a retry usually succeeds. ## Risk 3 — Throughput collapse / held-lock anti-patterns Because writers serialize behind the lock, holding it too long throttles the whole system. - **Never span user think-time** — do not acquire a lock, return to the browser, and hold it while the user edits. That's what optimistic locking is *for*. - **No external/network calls inside the locked transaction** — an HTTP call to a payment gateway while holding `FOR UPDATE` pins the lock for the call's latency. - **Keep the transaction tiny**: begin → lock → read-modify-write → commit. Do expensive computation *before* acquiring the lock where possible. ## Global vs per-query configuration - Per-query: `@QueryHints` as above. - Global default lock timeout can be set via a JPA property (`spring.jpa.properties.jakarta.persistence.lock.timeout`). - Bound overall transaction duration with `@Transactional(timeout = 5)` (seconds) so a stuck transaction can't hold locks forever. ## Retry pattern Because `CannotAcquireLockException` (deadlock/timeout) is transient, wrap the operation in a bounded retry with jittered backoff — e.g. Spring Retry `@Retryable(retryFor = CannotAcquireLockException.class, maxAttempts = 3)` or a manual loop. Do the same for `ObjectOptimisticLockingFailureException` if using optimistic locking. ## Choosing the right tool If conflicts are actually rare, **optimistic locking is usually the better default** — no blocking, no deadlocks, scales horizontally, and you retry the rare conflict. Reserve pessimistic locking for genuinely hot, high-contention rows where optimistic retry storms would be worse than serialization (inventory decrement, seat/ticket allocation, wallet debit). ## Gotchas summary - Lock timeout hint semantics differ per DB — test on your actual database. - A missing `@Transactional` makes the lock meaningless. - Long transactions + pessimistic locks = connection-pool starvation and cascading latency. - Deadlocks are inevitable at scale without consistent ordering; design for retry.

  • Two transactions occasionally deadlock while each locks two rows. What's the primary fix?
    Impose a consistent lock-acquisition order — e.g. always lock rows in ascending primary-key order — so a cycle can never form. Complement it with short transactions and a bounded retry on CannotAcquireLockException, since the DB rolls back one deadlock victim.
  • Why is holding a pessimistic lock across an HTTP call to an external service dangerous?
    The lock stays held for the full network latency of that call, blocking every competing transaction and pinning a DB connection. It can cascade into connection-pool exhaustion and system-wide latency. Do external work before acquiring the lock, or use optimistic locking.

saying these in an interview costs you the question

  • Assuming the lock.timeout hint works identically on every database (PostgreSQL only supports NOWAIT vs wait).
  • Holding a pessimistic lock across user think-time or a remote call.
  • No retry strategy for transient deadlock/timeout exceptions.
  • Believing deadlocks can be fully eliminated without consistent lock ordering.

context

resolving links…