| Dimension | Optimistic (no blocking, detect & retry) | Pessimistic (block first, then proceed) |
|---|---|---|
| Expected conflict rate | Low ā most concurrent Tx-ns touch different rows or only read. | High ā the same rows are updated a lot (inventory counters, queue tables, hot accounts). |
| Tx-n length / latency | Short, in-memory work ā cheap to retry. | Long-running units (minutes ā¦) ā retry would waste too much work. |
| System load | Spare capacity to absorb occasional retries. | System already near saturation ā retries would make it worse. |
| Hold user input? | No (everything computed server-side). | Yes ā e.g., interactive āedit form ā saveā flows where the user might collide. |
| Isolation level used | Snapshot / MVCC, version-columns, compare-and-set, SSI. | SELECT ā¦ FOR UPDATE, 2-phase locking, explicit row/table locks. |
| Failure handling | Must detect conflict (ERR_CONFLICT) and retry in code (with back-off). |
App just blocks until the lock is freeāno retry logic needed (but might need a timeout). |
| Throughput & scaling | Scales well: readers never block writers, writers never block readers. | Lock wait chains can hurt p-99 latency; limited by contention on hot rows. |
| Typical use-cases | ā¢ Micro-services writing distinct rowsā¢ Event-sourced append logsā¢ Read-heavy analytics queries on snapshotsā¢ āShopping-cartā merges in doc DBs | ā¢ Bank account debits/creditsā¢ Warehouse stock decrementā¢ Job queues / ātake-next-jobā patternā¢ Anything that must be strictly sequential |
Measure conflict probability
If two updates on the same row are rare (<1 %) ā optimistic is simpler and faster.
Frequent clashes (>~10 %) ā pessimistic avoids thrashing.
How painful is a retry?
Milliseconds of CPU ā optimistic.
Heavy calculations, external calls, or user think-time ā pessimistic.
Do you hold a lock across network hops?
Cross-service calls while a DB lock is held = danger; optimistic (or split Tx-ns) is safer.
Hot-spot keys (global counters, single āsettingsā row) almost always need pessimistic or an atomic DB primitive.
Many production systems mix both:
SELECT ā¦ FOR UPDATE or queue tables to serialize access.Bottom line
Use optimistic when conflicts are unlikely and retries are cheap; switch to pessimistic (or atomic DB primitives) when the same rows are hammered, work is expensive, or business correctness canāt tolerate a retry loop.