CodeWithYash

Foundation · Build

Locking, Indexes & Query Plans

Optimistic @Version detects conflicts after the fact; pessimistic locks prevent them outright—and neither compensates for a query plan scanning the table.

Primary source: Spring Data JPA: Lock Mode Annotations

35 min

Lesson 15 of 17

1d streak

The Architectural Question

Lost updates corrupt inventory silently without version checks, while teams add indexes by folklore instead of reading plans—so the slow query stays slow and writes get slower.

What this guide proves

  • Add @Version and handle OptimisticLockingFailureException with bounded retries
  • Reserve hot rows with pessimistic write locks only when contention is real
  • Read EXPLAIN output before creating or blaming indexes

Optimistic vs Pessimistic locking: Concurrency control

Architectural Boundary · Do Not Confuse

Concept A

Optimistic Locking (@Version)

Concept B

Pessimistic Locking (SELECT ... FOR UPDATE)

Optimistic locking adds a version column to the entity. No database locks are held while reading. When updating, Hibernate checks `WHERE id = ? AND version = ?`. If another transaction updated the row first, an OptimisticLockException is thrown. Pessimistic locking issues a database lock (`SELECT ... FOR UPDATE`), blocking competing transactions until the lock is released.

Analyzing query plans with EXPLAIN (ANALYZE, BUFFERS)

Writing JPQL without verifying the underlying SQL plan risks severe full table scans. Inspect the PostgreSQL query plan to ensure indices are utilized:

Terminal / Diagnostic Output
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = '7b8c9d0e';

Bitmap Heap Scan on orders  (cost=4.28..12.35 rows=4 width=128) (actual time=0.042..0.051 rows=3 loops=1)
  Recheck Cond: (customer_id = '7b8c9d0e')
  Buffers: shared hit=3
  ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.28 rows=4 width=0) (actual time=0.015..0.015 rows=3 loops=1)
Planning Time: 0.120 ms
Execution Time: 0.082 ms

Active Recall Checkpoint

When should you choose pessimistic locking over optimistic locking?

Hint: Consider contention volume.

Show answer & explanation

When collision frequency is high (e.g. inventory counters, ticket reservations). Retrying optimistic locking exceptions under high contention creates CPU thrashing; pessimistic locking queues concurrent transactions predictably.

Locking strategy guidelines

Use it when

Default to Optimistic Locking (@Version) for standard enterprise entities where collisions are rare.

Reconsider when

Use Pessimistic Locking with a short timeout (@Lock(LockModeType.PESSIMISTIC_WRITE)) for scarce resources like flash-sale inventory.

Runtime Architecture & Flow

Persistence context · runtime visualization

Locking, Indexes & Query Plans

Transaction

Add @Version and handle OptimisticLockingFailureException with bounded retries

Entity state

Reserve hot rows with pessimistic write locks only when contention is real

Flush

Read EXPLAIN output before creating or blaming indexes

Commit

Run concurrent stock reservations against @Version, then fix a slow filter query with one composite index verified by EXPLAIN.

Working Implementation

Code Walkthrough

Run concurrent stock reservations against @Version, then fix a slow filter query with one composite index verified by EXPLAIN.

ProductConcurrency.java
@Entity
class ProductStock {
    @Id private UUID id;
    @Version private long version;
    private int available;

    void reserve(int quantity) {
        if (quantity > available) throw new InsufficientStock();
        available -= quantity;
    }
}

interface ProductStockRepository extends JpaRepository<ProductStock, UUID> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("select p from ProductStock p where p.id = :id")
    Optional<ProductStock> findForUpdate(UUID id);
}

// Verify supporting indexes with EXPLAIN ANALYZE, not guesswork.
// create index idx_products_active_name on products(active, lower(name));
@Version detects a lost update at commit time without holding a row lock while a user reads the screen.
PESSIMISTIC_WRITE issues SELECT ... FOR UPDATE, which serializes the hot row and brings waits, lock timeouts, and deadlock risk with it.
The functional index on (active, lower(name)) only pays off if queries filter that way, and EXPLAIN ANALYZE is what confirms it.

Synthesis & Production Verification

Deliberate Failure Condition

  1. Break: Two threads reserve the last unit with plain reads and writes
  2. Observe: Both succeed; available goes negative
  3. Repair: Check-then-act across transactions needs optimistic versioning or a pessimistic lock; application-level if statements cannot see other transactions.

Production Acceptance Criteria

  • Force an optimistic conflict and retry it successfully
  • Demonstrate a pessimistic lock blocking a second reader
  • Halve a query’s plan cost with an evidence-backed index

Architectural & Technical Review

Core Interview Questions

Q01When is optimistic locking preferable to pessimistic locking?Show answer

When concurrent update collisions are infrequent, avoiding long-held database row locks.

Q02How does EXPLAIN (ANALYZE, BUFFERS) help diagnose slow queries?Show answer

It shows the actual execution time, index scans vs sequential scans, and shared buffer hits in PostgreSQL.

Q03When should pessimistic write locks be employed?Show answer

For scarce, high-contention resources like flash-sale inventory or seat bookings where optimistic retries cause CPU thrashing.

Production Pitfalls

Critical Anti-Patterns

  • Pitfall: Omitting timeout configurations on pessimistic locks — Acquiring pessimistic locks without timeouts risks hanging application threads indefinitely during deadlocks.
  • Pitfall: Deploying queries without analyzing execution plans — Queries that look fast on small development datasets can trigger full table scans and timeouts in production.

Primary Documentation & Specifications

Lesson Primary References

Spring Data JPA: Lock Mode Annotations