How do you handle concurrent updates to the same row? Optimistic versus pessimistic locking.

Question

Accepted Answer

The underlying problem is the lost update. Two transactions read the same row, both compute a new value from it, and the second write clobbers the first, so one update silently disappears. That is the classic read modify write race. There are 2 strategies. Pessimistic locking takes a lock when you read, with SELECT ... FOR UPDATE, so any other transaction that wants that row blocks until you commit or roll back. It is safe and simple when contention is high, but it holds the lock for the whole transaction, which cuts concurrency and can deadlock if transactions grab rows in different orders, so I keep those transactions short, ideally under 100 milliseconds. Optimistic locking takes no lock. I read the row with a version column, then write with UPDATE ... WHERE id = ? AND version = ?, incrementing the version. If someone else changed the row in between, the version no longer matches, the update affects 0 rows, and I detect the conflict and retry with fresh data. Optimistic is excellent when conflicts are rare, say under 1 percent of writes, because it holds no locks and maximises throughput, and the cost is just the occasional retry. So I choose by contention. Pessimistic when many transactions genuinely fight over the same rows and retrying would only thrash, optimistic when conflicts are infrequent and I want high throughput. The one thing I never do is ignore it and let read modify write race.

Answer

How does optimistic locking actually detect a conflict?

A version/timestamp column: you read it, then UPDATE ... WHERE version = the value you read, and bump it. If another writer already changed it, your update matches zero rows, signaling a conflict to retry.

Answer

When is pessimistic locking the better choice?

High-contention rows where conflicts are frequent: optimistic would constantly fail and retry (thrash), so taking the lock up front (SELECT FOR UPDATE) is more efficient, accepting reduced concurrency and watching for deadlocks.

How do you handle concurrent updates to the same row? Optimistic versus pessimistic locking.

How do you handle concurrent updates to the same row? Optimistic versus pessimistic locking.

The follow-ups they ask next

What the interviewer is listening for

What sinks the answer

If you genuinely do not know

Keep going with databases

Knowing the answer is not the same as recalling it under pressure