What is the difference between optimistic and pessimistic locking?

Question

Accepted Answer

**Optimistic** — check at commit. **Pessimistic** — lock during operation.

**Optimistic Locking:**
- No locks during read.
- Version number or timestamp.
- Conflict detected at commit.
- Retry on conflict.
- High concurrency.

```
Optimistic:
READ: SELECT * FROM accounts WHERE id=1 (version=5)
UPDATE: SET balance=200, version=6 WHERE id=1 AND version=5
If version changed → retry
```

**Pessimistic Locking:**
- Lock rows during operation.
- Other transactions wait.
- Guaranteed success.
- Lower concurrency.

```
Pessimistic:
SELECT * FROM accounts WHERE id=1 FOR UPDATE
(Other transactions wait)
UPDATE SET balance=200 WHERE id=1
COMMIT (release lock)
```

**Key differences:**
- **Optimistic** — no locks, retry on conflict.
- **Pessimistic** — locks, guaranteed success.
- **Optimistic** — high concurrency.
- **Pessimistic** — low concurrency.

Systems Analyst: What is the difference between optimistic and pessimistic locking?