Both stop the race. They differ in who pays.
Pessimistic locks first, so other writers wait. Optimistic checks a version at commit, so the loser redoes its work.
Conflicting writes a second on one row × each write’s window
Well under 0.1, go optimistic. Up to about 0.7, lock if attempts are expensive. Near 1, neither works.
Swiggy-style, 3,000 orders a second. Heavy contention?
Bad: Count total traffic
Sounds hot, so lock every restaurant row.
Good: Count per row
That’s 0.03 writes a second per restaurant row. A version check is nearly free.
Optimistic writers never queue. So a busy row takes more writes?
The ceiling
No. Both cap at 1/t, 50 writes a second at t = 20 ms. Optimism spends the overflow on retries.
Asha pays Bilal as Bilal pays Asha. Neither wait can end.
Each transfer locked its payer’s row, then asked for the other. Lock both in id order and the loop becomes a queue.
SKIP LOCKED: each worker walks past rows others hold
Plain FOR UPDATE makes every worker queue for the first ready job. Skipping locked rows means nobody waits and no job is claimed twice.
Flash sale at λt = 100? Change the design, not the lock.
- Shard a counter; read the sum
- One row per unit, claimed with SKIP LOCKED
- Or queue writes per SKU to one consumer
Remember
Locks make others wait; versions make losers redo. Choose per row by rate × window; near 1, split the row.