Optimistic Locking

Hook: Optimistic locking is the database version of 'I’ll check before I commit.' Interviewers love it because it tests whether you understand lost updates, not just UPDATE syntax.

Question: What is optimistic locking in SQL?

Answer: Optimistic locking is a way to stop one user from silently overwriting another user’s changes. You read a row, remember its version, and when you write back you include that version in the WHERE clause. If the row was changed by someone else, zero rows are updated and you know there was a conflict.

Interview-Ready Answer: I use optimistic locking when I expect conflicts to be rare. I add a version column, read the current version, and update with a condition like WHERE id = ? AND version = ?; then I increment the version on success. If the update affects zero rows, I know another transaction won, so I re-read and retry or show a conflict message instead of overwriting data.

What it means

Optimistic locking is not a special database lock. It is a concurrency check: a rule that says, 'only write if the row still looks like the version I read.' A conflict is detected at write time, not by blocking other users while you think.

How it works under the hood

1. The application reads a row and its version, for example version = 7.
2. The user edits data in memory or in a form; no database lock is held during this time.
3. The application sends an UPDATE that includes both the primary key and the old version in the WHERE clause.
4. If one row is affected, the write won and the app increments the version to 8.
5. If zero rows are affected, the row changed first, so the app must re-read, merge, or ask the user to try again.

The key idea is that the old version acts like a receipt. If the receipt no longer matches the row, someone else already changed it.

When and why to use it

• Best for low contention: most writes do not collide, so you avoid the cost of waiting on locks.
• Great for user-facing edits: form updates, profile pages, admin screens, and cart edits often sit idle while a user types.
• Useful for lost-update protection: a lost update is when one write quietly erases another write.

In practice, a version column is usually an integer starting at 1. That is safer than a timestamp because clock precision can be coarse, and two writes in the same tick can look identical. SQL itself does not standardize a built-in optimistic-locking clause; most systems implement it with a version field or a timestamp field plus a careful WHERE condition.

Optimistic vs pessimistic locking

Pessimistic locking often uses SELECT ... FOR UPDATE, which blocks other writers until the transaction ends. That is useful when a row is very hot, but it can reduce throughput and increase lock waits. Optimistic locking usually wins when writes are short and conflicts are uncommon.

Performance notes and real numbers

• The extra storage is tiny: one INTEGER version column is usually 4 bytes per row.
• If you have 10 million rows, that is about 40 MB of raw data before storage overhead.
• The update cost is still dominated by the primary-key lookup, which is typically an indexed O(log n) search.
• The expensive part is not the check itself; it is the retry when a conflict happens.

A good retry policy is usually 3 tries with small backoff, such as 50 to 200 ms. If conflicts happen constantly, optimistic locking can thrash: many requests keep failing and retrying. In that case, switch to pessimistic locking, split the hot row into smaller rows, or redesign the write path.

Important edge cases

• Row count matters: zero affected rows means conflict, not success.
• Timestamp versions can lie: low precision or clock skew can miss collisions.
• Isolation level is not the same thing: transaction isolation helps, but optimistic locking still gives you a clear, application-level conflict signal.
• Do not forget to increment the version: if you do not change it on success, future checks become meaningless.

Real-World Example: Imagine a checkout service for a concert ticket store. Two users open the same last ticket at almost the same time. Both screens show quantity = 1, both click buy, and both requests reach the database.

With optimistic locking, the first order update succeeds and increments the version. The second request tries to update the same row with the old version, gets zero affected rows, and the app returns 'sold out' or 'your cart expired.' That is the correct behavior because only one customer can buy the last ticket.

What goes wrong without it: the system uses a plain UPDATE by product id, so both writes succeed and the stock can become -1, or two order confirmations are sent for one item. Symptoms show up as duplicate receipts, inventory mismatches, and support tickets that say 'I paid but the item disappeared.' In logs you might see two successful payment events but only one real unit in inventory, or a later reconciliation job shouting about negative stock.

This is why interviewers care: optimistic locking is not theory. It is the difference between clean conflict handling and a silent data bug that only appears under real user load.

Follow-up & Tricky Questions:

• How do you detect an optimistic-lock conflict? Check the number of affected rows. If the UPDATE touches zero rows, the version is stale and the app should re-read or show an error.
• Why is a version integer better than a timestamp? Integers are simple and precise. Timestamps can fail if the clock resolution is too coarse or if two writes happen in the same time slice.
• Should I retry automatically? Usually yes, but only a few times and with backoff. If the row is very hot, blind retries can create a retry storm and make latency worse.
• Can optimistic locking and transactions be used together? Yes. The transaction groups your work, and optimistic locking prevents lost updates inside that work by checking the row version at commit time.
• What if I need to merge two edits? Re-read the latest row and merge field by field in the application. Optimistic locking tells you there is a conflict; it does not automatically decide which values should win.
• Is optimistic locking the same as SELECT ... FOR UPDATE? No. FOR UPDATE is pessimistic locking and blocks other writers, while optimistic locking lets everyone proceed and only checks for conflicts when writing.
• Does serializable isolation make optimistic locking unnecessary? Not always. Serializable can still abort transactions, and optimistic locking gives you a clear business-level conflict signal that is easier to explain to users.
• Can I use a checksum or hash as the version? Yes, but it is usually heavier and more complex than a simple counter. A counter is the cleanest default unless you have a special reason.
• Is a conflict the same as a deadlock? No. A conflict means the row changed and your stale write failed cleanly. A deadlock means two transactions wait on each other and the database must break the cycle.

Common Mistakes:

• Forgetting the version in the WHERE clause. Correction: always include both the primary key and the old version, or you are back to last-write-wins.
• Not checking affected rows. Correction: zero rows updated means a conflict, not success.
• Using a weak timestamp as the version. Correction: prefer an integer counter unless you are certain the timestamp granularity is safe.
• Retrying forever on a hot row. Correction: cap retries, add backoff, and consider pessimistic locking or schema changes when contention is high.

Memory Hook: Read, edit, prove. Read the row, edit it locally, then prove nobody changed it by matching the old version on write.

Cheat Sheet:

• Optimistic locking prevents lost updates.
• Use a version column, usually an integer.
• Write with WHERE id = ? AND version = ?.
• Increment the version on every successful update.
• Zero rows updated = conflict, then re-read and retry.
• Best when writes are short and conflicts are rare.

Practice Tasks:

• Add a version column to an orders table and protect status updates with it.
• Simulate two sessions decrementing the same inventory row and observe the stale update fail.
• Compare optimistic locking with SELECT ... FOR UPDATE in a hot-row scenario and note which one blocks.