Pessimistic locking and isolation

Key points

• LockModeType.PESSIMISTIC_WRITE issues select ... for update. Anyone else touching that row waits until you commit.
• Optimistic locking scales and fails late; pessimistic serialises and fails early. Reach for it on short, genuinely contended rows only.
• Set jakarta.persistence.lock.timeout, or a waiting request blocks as long as the database allows, holding a connection.
• Lock rows in the same order everywhere. Two transactions taking A then B, and B then A, deadlock - the database kills one of them.
• That victim surfaces as an exception, so the whole unit of work has to be safe to retry from the start.
• Most applications sit at READ COMMITTED. Raising the isolation level is a far blunter instrument than locking the one row you care about.

Example

public interface AccountRepository extends JpaRepository<Account, Long> {

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

@Service
public class TransferService {

    @Transactional
    public void transfer(long fromId, long toId, long cents) {
        // always lock the lower id first - a fixed order cannot deadlock
        long first  = Math.min(fromId, toId);
        long second = Math.max(fromId, toId);
        accounts.findForUpdate(first).orElseThrow();
        accounts.findForUpdate(second).orElseThrow();

        accounts.getReferenceById(fromId).withdraw(cents);
        accounts.getReferenceById(toId).deposit(cents);
        // both rows stay locked until this method commits
    }
}

A pessimistic lock is a queue. Keep the queue short and always in one order.