Pessimistic Locking

Lock the Row When You Read It

Pessimistic locking asks the database to lock rows during the read. Other transactions that want the same lock wait until you commit or roll back.

LockModeType	Typical SQL	Meaning
`PESSIMISTIC_READ`	`for share` (PostgreSQL), `for share` / `lock in share mode` (MySQL)	others may read-lock, nobody may write
`PESSIMISTIC_WRITE`	`for update`	exclusive: others wait to lock or update
`PESSIMISTIC_FORCE_INCREMENT`	`for update` + version bump	also increments @Version

In a Spring Data Repository

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "3000")) // ms
    @Query("select p from Product p where p.id = :id")
    Optional<Product> findByIdForUpdate(@Param("id") Long id);
}

@Transactional                      // the lock is held until this method commits
public void reserve(Long productId, int qty) {
    Product p = products.findByIdForUpdate(productId).orElseThrow();
    if (p.getStock() < qty) throw new OutOfStockException(productId);
    p.setStock(p.getStock() - qty);
}

select p1_0.id,p1_0.name,p1_0.price,p1_0.stock,p1_0.version
  from products p1_0
 where p1_0.id=?
   for update

Note: A lock only lives inside a transaction. Calling a locking query without @Transactional fails with "no transaction is in progress" or releases the lock immediately.

Waiting for the Lock

Example

A ReentrantLock stands in for the row lock. Ravi blocks until Asha commits, then sees her change, so nothing is lost:

import java.util.*;
import java.util.concurrent.*;
import java.util.concurrent.locks.ReentrantLock;

public class Main {
    static int stock = 10;
    static final ReentrantLock rowLock = new ReentrantLock();
    static final List<String> log = Collections.synchronizedList(new ArrayList<>());

    public static void main(String[] args) throws Exception {
        CountDownLatch ashaLocked = new CountDownLatch(1);
        CountDownLatch raviWaiting = new CountDownLatch(1);

        Thread asha = new Thread(() -> {
            rowLock.lock();                                   // SELECT ... FOR UPDATE
            log.add("Asha: locked row, stock=" + stock);
            ashaLocked.countDown();
            try { raviWaiting.await(); } catch (InterruptedException e) { }
            stock -= 1;
            log.add("Asha: stock -> " + stock + ", COMMIT (lock released)");
            rowLock.unlock();
        });
        Thread ravi = new Thread(() -> {
            try { ashaLocked.await(); } catch (InterruptedException e) { }
            log.add("Ravi: SELECT ... FOR UPDATE -> waiting");
            raviWaiting.countDown();
            rowLock.lock();
            log.add("Ravi: got lock, stock=" + stock);
            stock -= 2;
            log.add("Ravi: stock -> " + stock + ", COMMIT");
            rowLock.unlock();
        });
        asha.start(); ravi.start(); asha.join(); ravi.join();
        log.forEach(System.out::println);
    }
}

Output

Asha: locked row, stock=10
Ravi: SELECT ... FOR UPDATE -> waiting
Asha: stock -> 9, COMMIT (lock released)
Ravi: got lock, stock=9
Ravi: stock -> 7, COMMIT

Timeouts and SKIP LOCKED

jakarta.persistence.lock.timeout = milliseconds to wait. On timeout Spring throws PessimisticLockingFailureException / CannotAcquireLockException.
Value 0 means NOWAIT: fail at once if the row is locked.
Value -2 (LockOptions.SKIP_LOCKED) means SKIP LOCKED: return only rows nobody has locked. Perfect for job queues where many workers pick pending rows.
MySQL ignores per-query timeouts in some versions; it uses innodb_lock_wait_timeout instead. PostgreSQL uses lock_timeout. Test on your real database.

// Many workers pick different pending orders without blocking each other
@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "-2"))
@Query("select o from Order o where o.status = 'PENDING_PAYMENT_CHECK' order by o.id")
List<Order> pickBatch(Pageable limit);     // ... for update skip locked