Pessimistic vs Optimistic Locking in Spring Boot & PostgreSQL

Concurrency Dilemma in Booking Systems

When designing concurrent booking systems (such as medical clinic appointment slots or flash reservations), multiple threads frequently compete for the exact same resource record at the exact same millisecond.

Choosing between Optimistic Locking and Pessimistic Locking determines whether your application scales cleanly or collapses under cascade rollbacks.

Optimistic Locking: When It Fails

Optimistic locking assumes collisions are rare. We add a @Version field to the JPA entity:

@Entity
public class AppointmentSlot {
    @Id
    private Long id;
    
    private boolean isBooked;
    
    @Version
    private Long version;
}

Under heavy contention:

1. 50 users attempt to book the single remaining 10:00 AM doctor slot.
2. 1 transaction succeeds and increments version = 2.
3. 49 transactions throw OptimisticLockException because their expected version was stale.
4. If retry mechanisms are used, 49 requests hammer the database in a vicious cycle until all fail.

The Power of Pessimistic Locking

For high-contention writes, Pessimistic Locking (SELECT ... FOR UPDATE) takes a direct database row lock immediately:

public interface AppointmentSlotRepository extends JpaRepository<AppointmentSlot, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT s FROM AppointmentSlot s WHERE s.id = :id")
    Optional<AppointmentSlot> findByIdWithLock(@Param("id") Long id);
}

This translates to PostgreSQL SQL:

SELECT * FROM appointment_slot WHERE id = 123 FOR UPDATE;

Execution Flow

sequenceDiagram
    participant UserA as User A Request
    participant UserB as User B Request
    participant DB as Postgres Row (Slot #123)

    UserA->>DB: BEGIN TX; SELECT FOR UPDATE (Locks Row)
    UserB->>DB: BEGIN TX; SELECT FOR UPDATE (Blocked/Waiting)
    UserA->>DB: Update isBooked=true; COMMIT TX
    Note over DB: Lock released for Slot #123
    DB-->>UserB: Lock acquired by User B
    UserB->>UserB: Check isBooked == true?
    UserB->>DB: ROLLBACK TX (Slot already booked)

1. User A acquires the row lock.
2. User B waits until User A completes the short transaction.
3. User B immediately reads the updated isBooked = true state and cleanly terminates with a 409 Conflict—no retry loops, no wasted compute.

Key Rules for Pessimistic Locking

1. Keep Transactions Ultra-Short: Never perform external HTTP or slow I/O inside the @Transactional block while holding the lock.
2. Consistent Lock Ordering: Always acquire locks in the same deterministic key order to prevent deadlocks.
3. Set Lock Timeouts: Always specify lock timeout query hints so requests fail fast rather than hanging indefinitely.