What they're testing
Whether you can reason quantitatively about contention, or just follow whatever your framework defaults to.
The short answer~30 seconds
Pessimistic (SELECT … FOR UPDATE) holds the row until commit: nobody redoes work, but everybody queues. Optimistic (a version column plus WHERE version = $1) holds nothing, detects the conflict at write time and makes the loser redo it. Under low contention optimistic wins clearly; under high contention it degenerates into a retry loop burning CPU, and pessimistic becomes the cheaper option.
The long answer
The number to estimate is the probability that two transactions touch the same row during their overlapping lifetimes. Editing a user profile: effectively zero, since each person edits their own — go optimistic, and most of the time you need no lock at all. Decrementing stock for one hot SKU during a flash sale: near-certain collision, and optimistic will retry thousands of times, getting worse as you add servers.
There's a third option candidates routinely forget: rewrite it as a single atomic statement. UPDATE stock SET qty = qty - 1 WHERE sku = $1 AND qty > 0 needs no explicit lock, has no read-modify-write window, and the affected-row count tells you whether it worked. When the problem can be expressed this way, it beats both of the others.
Pessimistic locking has an operational trap: it holds until the transaction ends, so if you call an external API between FOR UPDATE and COMMIT, a third party's latency becomes your lock-hold time. It's a very common cause of connection-pool exhaustion: a payment gateway slows by two seconds and the whole orders table stalls.
Three ways, same business rule
-- Bi quan: ai tới sau thì chờ
BEGIN;
SELECT qty FROM stock WHERE sku = 'A1' FOR UPDATE;
UPDATE stock SET qty = qty - 1 WHERE sku = 'A1';
COMMIT;
-- Lạc quan: không chờ, thua thì làm lại
UPDATE stock SET qty = qty - 1, version = version + 1
WHERE sku = 'A1' AND version = 42; -- 0 hàng bị sửa = có người nhanh hơn
-- Nguyên tử: không khoá, không retry, không cửa sổ đua
UPDATE stock SET qty = qty - 1 WHERE sku = 'A1' AND qty > 0;What they'll ask next
?What's the difference between FOR UPDATE and FOR NO KEY UPDATE?
FOR NO KEY UPDATE is weaker: it doesn't block other transactions from creating foreign keys pointing at the row. In Postgres, an UPDATE that doesn't touch key columns takes that weaker mode itself, so choosing the right level lowers contention without giving up safety.
?How do deadlocks arise with pessimistic locking?
When two transactions lock the same set of rows in opposite orders. The cheapest prevention is a fixed lock order — for example, sort the ids ascending before locking. The engine detects the cycle and aborts one side, so the application still needs a retry.
These lose points
- Picking whatever the ORM defaults to without a reason. JPA ships
@Version, but using it on a hot row is the wrong call. - Never mentioning the single-statement rewrite. It's frequently the best answer available.