skip to content

What is the difference between JPA's LockModeType.PESSIMISTIC_READ and LockModeType.PESSIMISTIC_WRITE, and what SQL does Hibernate generate for each?

level: middleimportance: must knowfreq 50%

answer

  1. READ = shared (FOR SHARE), WRITE = exclusive (FOR UPDATE)
  2. shared allows co-readers, blocks writers
  3. no shared lock in the dialect → silently upgraded
  4. read-then-write with shared locks = upgrade deadlock
  5. lock mode forces a flush and skips the L2 cache

basics

~20 s

PESSIMISTIC_READ takes a shared lock (SELECT ... FOR SHARE): other readers may also lock it, writers block. PESSIMISTIC_WRITE takes an exclusive lock (SELECT ... FOR UPDATE): nobody else may lock or modify the row. On dialects without shared locks, Hibernate upgrades READ to FOR UPDATE.

solid answer

~50 s

`PESSIMISTIC_READ` means "nobody may change this row while I look at it, but others may read it under the same protection". Hibernate renders it as the dialect's shared row lock — `for share` on PostgreSQL and MySQL 8, `lock in share mode` on older MySQL. `PESSIMISTIC_WRITE` means "I intend to change this row" and renders as `for update`, an exclusive lock that blocks other lockers and writers. Two things trip people up. First, if the dialect has no shared row lock, Hibernate does not fail — it silently uses the stronger exclusive lock, so `PESSIMISTIC_READ` can behave exactly like `PESSIMISTIC_WRITE`. Second, `PESSIMISTIC_READ` is a deadlock generator in read-then-write flows: two transactions both hold the shared lock, both then try to upgrade to exclusive, and each waits for the other. If you know you will write, ask for `PESSIMISTIC_WRITE` on the first read.

code

sql · 5 lines
sql
-- LockModeType.PESSIMISTIC_READ
select a.id, a.balance from account a where a.id = ? for share;

-- LockModeType.PESSIMISTIC_WRITE
select a.id, a.balance from account a where a.id = ? for update;

go deeper

for a junior

Shared versus exclusive, and the two SQL clauses: FOR SHARE and FOR UPDATE.

for a middle

Add the dialect upgrade when shared locks are unsupported, and that plain non-locking readers on an MVCC engine are unaffected by either mode.

for a senior

Lead with the lock-upgrade deadlock in read-then-write flows and the rule of taking the exclusive lock on the first read; mention the forced flush and cache bypass.

for a principal

Treat mode choice as a throughput-versus-failure-mode decision, and require checking the generated SQL per engine because portability of the weaker mode is not guaranteed.

## Shared versus exclusive Both modes ask the database for a row lock; they differ in what they let other transactions do. **PESSIMISTIC_READ — shared.** Many transactions can hold it on the same row at once. It guarantees the row will not change underneath you for the rest of your transaction: any transaction that wants to update or delete it, or to take an exclusive lock, must wait. Use it for "read this value and make decisions on it, but do not write it" — for example reading an exchange rate or a configuration row you must be sure stays stable while you compute. **PESSIMISTIC_WRITE — exclusive.** Only one holder at a time. Everyone else who wants any lock on the row waits. Use it for the classic read-modify-write: read the balance, check it, subtract, commit. ## What Hibernate actually emits The dialect performs the mapping: - PostgreSQL: `for share` (read) and `for update` (write). - MySQL 8: `for share` / `for update`; older MySQL: `lock in share mode` / `for update`. - Oracle: `for update` for both — Oracle has no shared row lock, so `PESSIMISTIC_READ` is upgraded. - SQL Server: table hints such as `with (holdlock, rowlock)` and `with (updlock, rowlock)`. The upgrade behaviour is the important portability lesson. Asking for `PESSIMISTIC_READ` never gives you *weaker* protection than requested, but it can give you stronger — meaning throughput you assumed you had (concurrent shared readers) may not exist on that engine. Always check the emitted SQL on the database you actually run on. ## The upgrade deadlock The most common production failure with `PESSIMISTIC_READ` is lock upgrade deadlock. Transaction A and transaction B both read the same row with a shared lock — both succeed, because shared locks are compatible. Both then decide to update it, which requires exclusive access. A waits for B to release its shared lock; B waits for A. Neither can proceed, and the database kills one with a deadlock error. The fix is not retry logic in the first instance: it is asking for `PESSIMISTIC_WRITE` at read time whenever the flow may end in an update. A single exclusive lock has no upgrade step and no upgrade deadlock. ## How Hibernate uses the mode elsewhere The lock mode also affects flush behaviour: before executing a query with a pessimistic lock mode, Hibernate flushes pending changes so the database sees your own writes and locks the right, current rows. And because the mode implies the row must be current, the second-level cache is bypassed. When an entity spans multiple tables (joined inheritance, `@SecondaryTable`), the lock covers the rows that make up that entity, which is more locking than a single-table entity implies — another reason to keep locked entities narrow. ## Choosing In practice most application code wants `PESSIMISTIC_WRITE`. `PESSIMISTIC_READ` earns its place only when many transactions genuinely need to read-and-hold a row concurrently and truly will not write it — otherwise its extra concurrency is paid back with deadlocks.

  • You use PESSIMISTIC_READ and still see deadlock errors under load. What is the likely cause?
    Lock upgrade. Two transactions hold the compatible shared lock on the same row and then both try to update it, which needs the exclusive lock; each waits for the other to let go. Take PESSIMISTIC_WRITE on the initial read whenever the transaction may write, so there is no upgrade step.
  • Your code asks for PESSIMISTIC_READ but the database log shows FOR UPDATE. Is that a bug?
    No — it is the dialect. Engines without a shared row lock (Oracle, for example) satisfy the request with the stronger exclusive lock, because stronger is always safe. The consequence is a concurrency change, not a correctness change: concurrent shared readers you assumed would run in parallel now serialise.

saying these in an interview costs you the question

  • Saying PESSIMISTIC_READ prevents other transactions from reading the row — it does not; plain reads are unaffected on MVCC engines.
  • Assuming FOR SHARE exists everywhere, so PESSIMISTIC_READ always behaves the same across databases.
  • Using PESSIMISTIC_READ for a read-modify-write flow and treating the resulting deadlocks as a database problem.
  • Claiming the two modes differ only in Hibernate's bookkeeping and produce the same SQL.

context

resolving links…