Two users update the same row at the same time. One of them is going to lose. The question is when they find out.

Pessimistic: Lock First, Ask Questions Later#

Grab the lock before you read. Nobody else can touch this row until you’re done.

START TRANSACTION;
SELECT * FROM configurations WHERE id = 42 FOR UPDATE;
-- Row is now locked. Other transactions block here.
UPDATE configurations SET value = 'new_value', version = version + 1 WHERE id = 42;
COMMIT;

Safe. Simple. Also means every other thread trying to update this row is sitting there waiting. Under low contention, you’re paying for locks nobody’s competing for. Under high contention, at least you’re not wasting work.

Optimistic: Try It, Apologize Later#

Read the row, note the version. Do your work. When you write back, check if the version still matches.

@Entity
public class Configuration {
    @Id private Long id;
    @Version private Long version;  // JPA handles CAS automatically
    private String value;
}

JPA translates this to UPDATE ... WHERE id = ? AND version = ?. If someone else changed the version between your read and write, zero rows update. You get an OptimisticLockException. Retry.

No locks held during your processing time. Great throughput when conflicts are rare. Terrible when conflicts are frequent because you’re doing work that gets thrown away.

graph TD Q{How often do conflicts happen?} Q -->|Rarely| O[Optimistic: version check on write] Q -->|Frequently| P[Pessimistic: lock on read] O --> OT[High throughput, occasional retries] P --> PT[Lower throughput, no wasted work] style Q fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style O fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style P fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style OT fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style PT fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff

The ABA Problem#

Version is 1. Thread A reads it. Thread B updates to version 2. Thread C updates back to version 1. Thread A writes, sees version 1, thinks nothing changed. But the data is different now.

Fix: use monotonically increasing version numbers. Never reuse values. JPA’s @Version does this by default: it only increments, never wraps.

At Oracle, we started with pessimistic locking on the NSSF config table. Every update took a row lock even though concurrent updates to the same config were rare (maybe once a week). Switched to optimistic with a version column. Connection pool utilization dropped because transactions weren’t blocking on locks anymore. The occasional OptimisticLockException was easy to handle with a retry.

What I’m Learning#

The choice comes down to contention. If two users rarely touch the same row, optimistic wins. High contention? Pessimistic saves you from burning CPU on work that’ll get rolled back. Most CRUD applications have low contention, which is why frameworks default to optimistic. The tricky part is when your system grows and contention patterns change.

What concurrency strategy does your system use? Have you hit cases where you had to switch?