Optimistic or Pessimistic? Understanding Locking in Databases

A Practical Guide to Managing Database Concurrency

Understanding Locks in Sql

FOR SHARE

• When you run a query like:

SELECT * FROM seats WHERE id = 1 FOR SHARE;

• It means: I want to read this row, and I don’t want others to update or delete it while I’m reading.

• Other transactions can still read this row with FOR SHARE, but they cannot update or delete it until your transaction ends.

• This is similar to a shared lock — many readers can coexist, but no writer is allowed.

FOR UPDATE

• When you run a query like:

SELECT * FROM users WHERE id = 1 FOR UPDATE;

• It means: I am planning to update this row, so lock it exclusively for me.

• No other transaction can read it with FOR SHARE or FOR UPDATE until your transaction is done.

• This is similar to an exclusive lock — only one person can hold it, and others must wait.

Locks in Action: Two Programs, One Row

Most tutorials just say:

"FOR SHARE is a shared lock, FOR UPDATE is an exclusive lock."

Cool. But what does that look like in real life when two threads (or two apps) are fighting over the same row?

What happened?

Transaction 1

• Transaction 1 grabbed a write lock on row id=1.

• That’s like putting a “Do Not Disturb” sign on the row.

• Until T1 commits/rolls back, no one else can even peek with FOR SHARE or FOR UPDATE.

Transaction 2

• T2 was just trying to read with a share lock (“hey, I only wanna look, not touch”).

• But T1 already said: “nah fam, I might update this row, so nobody else can even look until I’m done”

• So MySQL makes T2 wait… and after some time, it times out with that error.

But what about N peeps reading at the same time? Bruh, of course you can, it’s chill

Can i get a deadLock?

Yes you can even face a deadLock

What happened?

• T1 grabbed a read lock on row id=1.

• T2 grabbed a read lock on row id=1.

• T1 then has to write, but before it writes T2 should stop reading.

• T2 says i also have to write but T1 should stop reading.

• T1 waits for T2 to stop reading, T2 waits for T1 to stop reading(deadlock).

  Both T1 and T2 start with a shared lock (FOR SHARE). When they try to upgrade to an exclusive lock for writing, each is waiting for the other to release its shared lock. This causes a deadlock, so MySQL aborts one of the transactions

Now that we know about for share and for update, lets start understanding different locks in java.

Locks in Java

1. Optimistic Lock

Optimistic locking assumes that multiple transactions can complete without interfering with each other. Instead of blocking access, it allows operations to proceed freely but validates at the end whether a conflict occurred.

• How it works: Each transaction reads the data and, before updating, checks a version (or timestamp). If the version has changed, it means another transaction modified the data, and the update is rolled back or retried.

• Think of this like no actual SQL lock being placed when you read the data.

• In SQL terms:

  • It’s like doing a SELECT ... without FOR SHARE or FOR UPDATE.

  • Multiple readers can freely read the same row(may give stale reads but updates on the latest data only).

  • Only when someone tries to update the row, the version check happens.

  • If another transaction already modified it in the meantime, your update will fail with a conflict (usually OptimisticLockException).

• Use case: Best when conflicts are rare and reads are frequent.

2. Pessimistic Lock

Pessimistic locking assumes that conflicts are likely. Therefore, it prevents issues by locking the data upfront so no other transaction can modify it until the lock is released.

• How it works: When one transaction accesses a resource, it places a lock (read or write). Other transactions must wait until the lock is released.

• In SQL terms:

  • Using SELECT ... FOR UPDATE (or FOR SHARE depending on mode).

  • Other transactions can still read in pessimistic read (share) mode, but cannot modify until the lock is released(never gives stale reads since the whole row is locked from reading and concurrent write threads when a thread is writing).

  • In pessimistic write, even readers can be blocked until you’re done.

• Use case: Best when conflicts are frequent or when operations involve critical data integrity.

Implementing an optimistic lock in java:

To implement an optimistic lock, you are just required to add a version field to your table with @Version

package com.example.locking.entity;

import jakarta.persistence.*;
import lombok.Data;

@Entity
@Data
public class Seat {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "movie_name")
    private String movieName;
    private boolean booked;
    @Version
    private Integer version;
}

The @Version field can be of type int, long, Integer, Long, or even java.sql.Timestamp

2. What happens under the hood?

Let’s say two transactions (T1 and T2) load the same seat row with version 0.

• T1 updates booked to 1.

UPDATE seat SET booked=1, version=1 WHERE id=1 AND version=0;
// in general
UPDATE table_1 SET col_1=?, version=version+1 WHERE id=? AND version=curr_version;

• Update succeeds, row’s version becomes 1.

• T2 tries to update booked = 1 as well based on its stale version (0).

UPDATE seat SET booked=1, version=1 WHERE id=1 AND version=0;

• No rows updated → Hibernate throws OptimisticLockException.

This way, lost updates are prevented.

DB STATE(Before Update):

DB STATE(AFTER UPDATE)

LOGS:

[OPTIMISTIC] Thread-1 started booking seat 2
[OPTIMISTIC] Thread-2 started booking seat 2
[Thread-2] OptimisticLock: Fetched seatId=2, movie=MOVIE_2, booked=false, version=0 
[Thread-1] OptimisticLock: Fetched seatId=2, movie=MOVIE_2, booked=false, version=0 
[OPTIMISTIC] Thread-1 ✅ successfully booked seat 2
[OPTIMISTIC] Thread-2 ❌ failed to book seat 2 -> Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [com.example.locking.entity.Seat#2]

• Both threads read seat 2 at version=0 simultaneously.

• Thread-1 updates booked=true and increments the version to 1 → succeeds.

• Thread-2 tries to update based on the old version 0 → fails with OptimisticLockException.

• Key point: Optimistic locking detects conflicts at commit, preventing lost updates without locking rows during reads.

Implementing pessimistic lock in java:

Unlike optimistic locking, which detects conflicts after they occur, pessimistic locking prevents conflicts up front by acquiring a database lock on the row. This ensures no other transaction can modify (or sometimes even read) the data until your transaction completes.

Decide the Type of Pessimistic Locking You Require

• PESSIMISTIC_READ

  • Annotation: @Lock(LockModeType.PESSIMISTIC_READ) on the query also annotate your service method with @Transactional

  • SQL Equivalent: SELECT ... FOR SHARE / LOCK IN SHARE MODE

  • Behavior:

    • Prevents modifications by other transactions.

    • Still allows other transactions to read the data.

• PESSIMISTIC_WRITE

  • Annotation: @Lock(LockModeType.PESSIMISTIC_WRITE) on the query

    also annotate your service method with @Transactional

  • SQL Equivalent: SELECT ... FOR UPDATE

  • Behavior:

    • Prevents both reads (in some DBs) and writes.

    • Ensures exclusive access to the row until commit/rollback.

package com.example.locking.repository;

import com.example.locking.entity.Seat;
import jakarta.persistence.LockModeType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Optional;

public interface SeatRepository extends JpaRepository<Seat, Long> {

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

@Transactional
public Seat pessimisticLockingMechanism(Long seatId) {
    Seat seat = seatRepository.findByIdAndLock(seatId) // no need for the version column in pessimistic locking...
            .orElseThrow(() -> new RuntimeException("Seat not found"));

    System.out.printf("[%s] PessimisticLock: Fetched seatId=%d, movie=%s, booked=%s\n   ",
            Thread.currentThread().getName(), seatId, seat.getMovieName(), seat.isBooked());

    if (seat.isBooked()) {
        throw new RuntimeException("Seat is already booked!");
    }

    seat.setBooked(true);
    return seatRepository.save(seat);
}

DB STATE(Before Update):

DB STATE(AFTER UPDATE)

LOGS:

[PESSIMISTIC] Thread-2 started booking seat 3
[PESSIMISTIC] Thread-1 started booking seat 3
[Thread-1] PessimisticLock: Fetched seatId=3, movie=MOVIE_3, booked=false
   [PESSIMISTIC] Thread-1 ✅ successfully booked seat 3
[Thread-2] PessimisticLock: Fetched seatId=3, movie=MOVIE_3, booked=true
   [PESSIMISTIC] Thread-2 ❌ failed to book seat 3 -> Seat is already booked!

Here’s a concise explanation:

• Thread-1 starts booking seat 3 and acquires a pessimistic write lock. It sees booked=false and successfully books the seat.

• Thread-2 tries to book the same seat at the same time but must wait for the lock. After Thread-1 commits, Thread-2 sees booked=true and fails to book.

• Key point: Pessimistic locking ensures exclusive access, preventing lost updates. Only one thread can modify a row at a time.

---

Optimistic locking allows multiple transactions to read and update data concurrently, detecting conflicts at commit, making it ideal for scenarios with rare conflicts and high read throughput, though retries may be needed.

Pessimistic locking, on the other hand, locks rows upfront to ensure strict data consistency, preventing lost updates and stale reads, but can reduce concurrency and increase blocking.

Choosing the right strategy depends on your application’s conflict frequency, performance needs, and the criticality of data integrity.

That’s all from my side! ⭐ If you find any mistakes or notice anything missing, do comment — I’m always open to learning and improving.

Github link :- LocksRepoLink