Pessimistic Locking is a concurrency control strategy that assumes a high probability of conflicting concurrent writes. Rather than deferring conflict checks until transaction commit, pessimistic locking immediately acquires physical row-level database locks at the database engine level at the moment data is read (SELECT ... FOR UPDATE), holding those locks until the current transaction completes (COMMIT or ROLLBACK).

---

🟢 Junior Level

How Pessimistic Locking Works

Unlike optimistic locking, which operates via application-level version checks, pessimistic locking relies directly on the relational database management system’s (RDBMS) native row-locking subsystem. A transaction acquiring a lock ensures that no concurrent transaction can modify, delete, or lock the same rows until the holding transaction finishes.

In JPA, pessimistic locks are requested via EntityManager.find(), EntityManager.lock(), or the @Lock annotation in Spring Data JPA:

@Transactional
public void withdrawMoney(Long accountId, BigDecimal amount) {
    // Generates SQL: SELECT id, balance, owner FROM accounts WHERE id = ? FOR UPDATE;
    Account account = entityManager.find(
        Account.class, 
        accountId, 
        LockModeType.PESSIMISTIC_WRITE
    );

    account.setBalance(account.getBalance().subtract(amount));
    // Upon method exit, Spring commits the transaction (COMMIT),
    // and the database engine automatically releases the row lock.
}

                     PESSIMISTIC LOCKING TIMELINE
                     
    Transaction 1 (Thread A)                          Transaction 2 (Thread B)
          │                                                 │
    SELECT ... FOR UPDATE                                   │
    (Acquires Exclusive Lock in DB)                         │
          │                                                 │
    Business logic execution...                       SELECT ... FOR UPDATE
          │                                           (Database suspends Thread B!
          │                                            Waits for lock release)
          │                                                 │ ⏳
    UPDATE accounts SET balance = ...                       │ ⏳
    COMMIT (Database releases lock) ──────────────────────> │ 
                                                      (Thread B wakes up and acquires
                                                       the freshly updated row!)

When to Use Pessimistic Locking

• High-contention transactional operations: account balance withdrawals, seat reservations, flash-sale inventory decrements.
• Scenarios where conflict rates are high (> 5–10%), causing optimistic retry loops to thrash database CPU and connection pools.
• Implementing robust relational database job queues via the SKIP LOCKED pattern.

---

🟡 Middle Level

Pessimistic Lock Modes (LockModeType)

JPA Mode	SQL Translation (PostgreSQL / MySQL)	Operational Behavior
PESSIMISTIC_READ	SELECT ... FOR SHARE (Postgres) / LOCK IN SHARE MODE (MySQL)	Shared Read Lock: Concurrent transactions can read via FOR SHARE, but any attempt to UPDATE, DELETE, or acquire FOR UPDATE is blocked.
PESSIMISTIC_WRITE	SELECT ... FOR UPDATE	Exclusive Write Lock: Blocks all concurrent transactions attempting to read with locks or write to the locked row.
PESSIMISTIC_FORCE_INCREMENT	SELECT ... FOR UPDATE + @Version increment	Acquires an exclusive row lock and unconditionally increments the entity’s @Version column upon transaction commit.

Managing Lock Timeouts (jakarta.persistence.lock.timeout)

By default, if a row is already locked by another transaction, the incoming transaction blocks indefinitely until the database connection times out, potentially exhausting the HikariCP connection pool.

JPA allows configuring lock acquisition timeouts in milliseconds via query hints:

// 1. Wait a maximum of 3,000 ms (3 seconds) for lock acquisition:
Map<String, Object> hints = Map.of("jakarta.persistence.lock.timeout", 3000);
Account account = entityManager.find(Account.class, 1L, LockModeType.PESSIMISTIC_WRITE, hints);

// 2. NOWAIT Mode (0 ms): Fail immediately without blocking if row is locked:
Map<String, Object> nowaitHints = Map.of("jakarta.persistence.lock.timeout", 0);
// Generates SQL: SELECT ... FOR UPDATE NOWAIT
Account fastFailAccount = entityManager.find(Account.class, 1L, LockModeType.PESSIMISTIC_WRITE, nowaitHints);

In Spring Data JPA repositories:

public interface AccountRepository extends JpaRepository<Account, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints({@QueryHint(name = "jakarta.persistence.lock.timeout", value = "2000")})
    @Query("SELECT a FROM Account a WHERE a.id = :id")
    Optional<Account> findByIdForUpdate(@Param("id") Long id);
}

Common Concurrency Exceptions

• jakarta.persistence.PessimisticLockException: General failure when attempting to obtain a pessimistic lock.
• jakarta.persistence.LockTimeoutException: Thrown when the configured timeout window expires before the lock is granted.
• org.hibernate.exception.LockAcquisitionException: Indicates a low-level JDBC failure or a detected database deadlock.

---

🔴 Senior Level

High-Throughput Task Queue Pattern: SKIP LOCKED

In distributed architectures, relational databases frequently serve as transactional task queues (such as the Transactional Outbox pattern). If multiple worker nodes execute standard SELECT ... FOR UPDATE, they block one another sequentially.

The solution is the SKIP LOCKED modifier, which instructs the database engine to skip any rows currently locked by other workers and return only unlocked rows:

public interface TaskQueueRepository extends JpaRepository<ScheduledTask, Long> {

    // Value "-2" in Hibernate/JPA translates to SKIP LOCKED
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints({@QueryHint(name = "jakarta.persistence.lock.timeout", value = "-2")})
    @Query("SELECT t FROM ScheduledTask t WHERE t.status = 'PENDING' ORDER BY t.createdAt ASC")
    List<ScheduledTask> fetchNextBatch(Pageable pageable);
}

-- Generated SQL statement in PostgreSQL / MySQL 8+:
SELECT * FROM scheduled_tasks 
WHERE status = 'PENDING' 
ORDER BY created_at ASC 
LIMIT 10 
FOR UPDATE SKIP LOCKED;

Each worker instantly receives an exclusive, non-overlapping batch of tasks without waiting and without triggering deadlocks.

Deadlock Elimination via Deterministic Lock Ordering

A classic deadlock occurs during concurrent fund transfers between Account $A$ and Account $B$:

• Thread 1 transfers from Account 1 to Account 2 (locks Account 1, then attempts to lock Account 2).
• Thread 2 simultaneously transfers from Account 2 to Account 1 (locks Account 2, then attempts to lock Account 1).
• The database engine detects a circular dependency (Deadlock) and aborts one transaction with an error.

                         DEADLOCK CIRCULAR DEPENDENCY
                         
     Thread 1: LOCK(Account 1) ──[waits]──> Blocked by Thread 2 (Account 2)
                    ▲                                │
                    │                                ▼
     Thread 2: Blocked by Thread 1 <──[waits]── LOCK(Account 2)

Senior Solution: Deterministic Numerical Lock Ordering

Always acquire locks on multiple resources in a strictly sorted deterministic order (e.g., ascending by primary key):

@Transactional
public void transferMoney(Long fromId, Long toId, BigDecimal amount) {
    Long firstId = fromId < toId ? fromId : toId;
    Long secondId = fromId < toId ? toId : fromId;

    // Acquire locks in strict numerical ascending ID order:
    Account first = accountRepository.findByIdForUpdate(firstId).orElseThrow();
    Account second = accountRepository.findByIdForUpdate(secondId).orElseThrow();

    Account from = fromId.equals(first.getId()) ? first : second;
    Account to = toId.equals(first.getId()) ? first : second;

    from.debit(amount);
    to.credit(amount);
    // Deadlocks are mathematically impossible!
}

---

4 Tricky Questions

1. If a transaction locks a row using SELECT ... FOR UPDATE, can a parallel transaction read that same row using a regular SELECT * FROM table WHERE id = ? (without locks)?

Answer:

1. Yes, it can. Modern relational database engines (PostgreSQL, MySQL InnoDB, Oracle) implement MVCC (Multi-Version Concurrency Control).
2. A non-locking SELECT reads a consistent point-in-time snapshot from undo logs (WAL / Undo Segments) and is never blocked by exclusive row locks.
3. SELECT ... FOR UPDATE only blocks concurrent transactions that attempt to:
   • Execute SELECT ... FOR UPDATE or FOR SHARE on the same row.
   • Execute an SQL UPDATE or DELETE targeting the locked row.

2. What happens if you execute a PESSIMISTIC_WRITE query on an un-indexed column (e.g., WHERE status = 'NEW') across a 1,000,000-row table?

Answer:

1. To locate matching rows, the database engine must execute a Full Table Scan.
2. During the scan, the database acquires row locks on every single scanned row in the table (and in engines like MySQL InnoDB, applies Gap Locks or escalates to a full Table Lock).
3. The entire table becomes effectively frozen for concurrent write operations across the entire application.
4. Golden Rule: Pessimistic locking queries must always target indexed columns (ideally the primary key).

3. A transaction attempts to acquire a lock with jakarta.persistence.lock.timeout = 1000 and throws LockTimeoutException. Can you catch the exception in a try-catch block and continue the transaction in PostgreSQL?

Answer:

1. In PostgreSQL, categorically NO.
2. When an SQL statement fails in PostgreSQL (including a lock timeout), the database marks the transaction as aborted: ERROR: current transaction is aborted, commands ignored until end of transaction block.
3. The active JPA session becomes Rollback-Only. Any subsequent statement executed within that transaction block will be rejected.
4. The transaction must be rolled back immediately, and any retry logic must be initiated from an outer layer.

4. What is the fundamental difference between LockModeType.PESSIMISTIC_WRITE and LockModeType.PESSIMISTIC_FORCE_INCREMENT?

Answer:

1. PESSIMISTIC_WRITE acquires an exclusive FOR UPDATE lock and only updates the entity’s version if scalar fields of the entity were actually modified.
2. PESSIMISTIC_FORCE_INCREMENT acquires the FOR UPDATE lock and unconditionally increments the @Version column upon commit (via UPDATE table SET version = version + 1 WHERE id = ?), even if no fields of the root entity were changed.
3. This is essential for notifying external systems and optimistic readers that the aggregate boundary was mutated (e.g., when items in an associated child collection were added or removed).

---

🎯 Interview Cheat Sheet

30-Second Elevator Pitch

“Pessimistic Locking in JPA acquires physical database-level row locks during read time. PESSIMISTIC_WRITE translates to SQL SELECT ... FOR UPDATE, blocking concurrent writes and locked reads until the current transaction commits or rolls back. PESSIMISTIC_READ translates to FOR SHARE, allowing concurrent reads while blocking updates. To prevent connection pool exhaustion, always set a jakarta.persistence.lock.timeout (or 0 for NOWAIT). For high-throughput worker queues, use SKIP LOCKED (timeout = -2). Critical risks include database deadlocks (eliminated by deterministic ascending ID lock ordering) and connection pool saturation if transactions take longer than 50 ms.”

Key Production Metrics & Health Checks

• Row Lock Wait Time / Lock Contention: Spikes indicate transactions holding pessimistic locks for too long (e.g., executing remote HTTP calls inside @Transactional).
• Deadlock Frequency (PostgreSQL pg_stat_database.deadlocks): Indicates non-deterministic locking orders across application threads.
• Active Transaction Duration: Transactions holding pessimistic locks should ideally complete in under 10–50 ms.

Red Flags to Avoid

• ❌ “Pessimistic locks block regular non-locking SELECT queries of other users.” — Under MVCC, non-locking reads access undo logs and are never blocked.
• ❌ “I use pessimistic locking on all service queries without timeouts to ensure data safety.” — A guaranteed way to cause severe database lock contention and exhaust HikariCP connections.
• ❌ “Executing remote REST API calls or slow file I/O inside a transaction holding a pessimistic lock.” — A severe anti-pattern that holds database row locks across network boundaries.
• ❌ “Executing SELECT FOR UPDATE on an un-indexed column.” — Causes table scans and locks the entire table.

Related Topics

• How to Implement Optimistic Locking in JPA — Optimistic vs pessimistic locking
• What is @Version and Why is It Needed — Version tracking
• What Does the refresh() Method Do — Upgrading locks via refresh
• How Does the Flush Mechanism Work in Hibernate — Flush and transaction commit