Optimistic and pessimistic locking

Ever had two people edit the same document at once and accidentally overwrite each other? Databases face the same problem when two users try to change the same record at the same time. To avoid that mess, we use record locking. There are two main flavors: pessimistic (lock first, ask questions later) and optimistic (hope for the best, check at the end). Let’s break them down step by step with easy words and simple sequence diagrams.

{getToc} $title={Table of Contents} $count={true} $expanded={false}

Why Lock Records at All?

Imagine you and a friend both withdraw money from the same ATM account at the same time. If the bank doesn’t coordinate, you might both think you have $100 and each withdraw $100 now the account goes negative. Locks keep updates in order so you don’t end up with inconsistent data.

Pessimistic Locking: Hold It Until You’re Done

In pessimistic locking, before you read or change data, you grab an exclusive lock. While you hold it, nobody else can touch that record. Think of it like putting a “Do Not Disturb” sign on a hotel room you get private access until you check out.

sequenceDiagram participant U as User A participant DB as Database U->>DB: BEGIN TRANSACTION U->>DB: SELECT ... FOR UPDATE (lock row) DB-->>U: Lock granted U->>DB: UPDATE row SET value = X U->>DB: COMMIT DB-->>U: Lock released

1. BEGIN TRANSACTION: You start a safe zone.
2. SELECT … FOR UPDATE: You ask the database to lock that row for your exclusive use.
3. LOCK GRANTED: Database says okay, no one else can change it now.
4. UPDATE: You make your changes.
5. COMMIT: You’re done release the lock so others can go next.

Pros & Cons of Pessimistic Locking

Pros

• Guaranteed no one else can update at the same time.
• Easy to understand.

Cons

• Other users wait (they get blocked) until you finish.
• Risk of deadlocks if two users lock two rows in opposite order.

Deadlock Between Two Transaction with Opposite directions

A deadlock between two users (or threads/processes) occurring in opposite direction is a classic concurrency problem where each user holds a resource that the other needs, creating a circular wait condition that cant be resolved without intervention.

Here’s how it happens step by step:

1. Transaction 1 (T1) locks Row A.
2. Transaction 2 (T2) locks Row B.
3. Now T1 tries to lock Row B, but it’s already held by T2, so T1 waits.
4. Meanwhile T2 tries to lock Row A, but that’s held by T1, so T2 waits.
5. Because each is waiting for the other to release a lock, neither can proceed boom, deadlock.

sequenceDiagram participant T1 as Transaction 1 participant T2 as Transaction 2 participant DB as Database T1->>DB: LOCK Row A DB-->>T1: Granted Row A T2->>DB: LOCK Row B DB-->>T2: Granted Row B T1->>DB: LOCK Row B DB--x T1: (blocked—waiting for Row B) T2->>DB: LOCK Row A DB--x T2: (blocked waiting for Row A) note over DB: Deadlock neither can move on

A real world database scenario where two users update records in opposite order

    
-- User 1 transaction
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1; -- Locks account 1
UPDATE Accounts SET balance = balance + 100 WHERE id = 2; -- Waits for User 2's lock
COMMIT;

-- User 2 transaction (executed concurrently)
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 50 WHERE id = 2; -- Locks account 2
UPDATE Accounts SET balance = balance + 50 WHERE id = 1; -- Waits for User 1's lock
COMMIT;

Optimistic Locking: Hope for the Best, Check at the End

Optimistic locking assumes collisions are rare. You read data and make changes in memory without locking. When you save, the database checks a version number or timestamp. If someone else already changed it, your save fails and you retry or cancel.

sequenceDiagram participant U as User A participant DB as Database U->>DB: SELECT row (version = 5) DB-->>U: row data + version=5 U->>DB: UPDATE row SET value = X, version = 6 WHERE version = 5 alt version match DB-->>U: Success (1 row updated) else version mismatch DB-->>U: 0 rows updated → conflict end

SELECT: You fetch the row, reading its current version (e.g. 5).
UPDATE … WHERE version=5: You tell the database “only update if nobody else changed it since version 5.”
Success: You get 1 row updated and bump the version to 6.
Conflict: If 0 rows updated, someone else changed it time to retry or abort.

Pros & Cons of Optimistic Locking

Pros

• No blocking on reads high throughput when conflicts are rare.

• Better for distributed systems or user facing apps where wait times annoy people.

Cons

• You need to handle retries or show error messages when conflicts happen.

• Under heavy write contention, you get frequent conflicts and retries.

Which One Should You Use?

Pessimistic when:

• Conflicts are common (many users update the same record).

• You can afford short blocks transactions finish quickly.

Optimistic when:

• Conflicts are rare (most users read or update different records).
• You need to scale across many servers or want no waiting on reads.

Quick Spring Data JPA Examples

Pessimistic Lock

@Entity 
class Account { 
    @Id 
    Long id; 
    String name;
    BigDecimal balance;
}

public interface AccountRepo extends JpaRepository<Account,Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("select a from Account a where a.id = :id")
    Account lockById(@Param("id") Long id);
}

// Usage
@Transactional
public void deduct(Long id, BigDecimal amt) {
    Account acc = repo.lockById(id);
    acc.setBalance(acc.getBalance().subtract(amt));
}

Optimistic Lock

@Entity 
@Entity
class Account {
  @Id Long id;
  String name;
  @Version Integer version;
}

public interface AccountRepo extends JpaRepository<Account,Long> {}

// Usage
@Transactional
public void deduct(Long id, BigDecimal amt) {
  Account acc = repo.findById(id).orElseThrow();
  acc.setBalance(acc.getBalance().subtract(amt));
  // JPA will automatically check version on commit
  // On commit:
  // 1. JPA checks version automatically
  // 2. Throws OptimisticLockException if version changed
  // 3. Updates version if successful
}