Questions: Optimistic Concurrency Control: Version Numbers

Question 1

Transaction T1 and T2 both read row id=42 at version=5. T1 updates the row first, bumping it to version=6. T2 then executes: UPDATE accounts SET balance=300, version=6 WHERE id=42 AND version=5. What happens?

• A. T2's update succeeds and the balance is set to 300
• B. T2's update matches zero rows — the conflict is detected and T2 must retry
• C. T2 blocks, waiting for T1 to release a lock before proceeding
• D. A deadlock is recorded and both transactions are rolled back

Answer: (B)

This is the core mechanism of OCC. T1 already changed the version from 5 to 6, so T2's WHERE clause (version=5) no longer matches. The update returns zero affected rows, signaling to the application that a concurrent modification occurred. T2 must re-read the current row (version 6) and recompute its update. No lock was held, no blocking occurred — the conflict was detected only at write time.

Question 2

Which workload is most suited to optimistic concurrency control?

• A. A high-frequency trading platform where many transactions update the same account balance per second
• B. A content platform where millions of users each update only their own profile data
• C. A ticket booking system during a flash sale where thousands of users compete for the last 10 seats
• D. A bank transfer system where strict consistency is enforced across accounts

Answer: (B)

OCC excels when conflicts are rare — when transactions are unlikely to touch the same rows simultaneously. A platform where users update their own distinct profiles has very low contention: most transactions will never compete, so the overhead of locks is wasteful. The other options involve high contention (many transactions competing for the same rows), where OCC's retry storms would degrade performance relative to pessimistic locking.

Question 3

In optimistic concurrency control, a transaction holds no locks during its read and processing phases.

• T. True
• F. False

Answer: True

This is the defining characteristic of OCC. The transaction reads data, does its work, and only at commit time checks whether the data has changed (via the version number WHERE clause). No locks are acquired during reading — contrast this with pessimistic locking where a read lock is held from the moment of the read until commit. The lock-free read phase is why OCC performs well when contention is low.

Question 4

Optimistic concurrency control prevents most data conflicts between concurrent transactions by detecting them before they can occur.

• T. True
• F. False

Answer: False

OCC detects conflicts at write time — after they have already occurred — and responds by rejecting the update and requiring a retry. It does not prevent conflicts from happening; it detects and handles them after the fact. Pessimistic locking prevents conflicts by blocking concurrent access upfront. The tradeoff is that OCC's detection-after-the-fact is cheaper when conflicts are rare, but each detected conflict requires discarding work and retrying.

Question 5

Why can optimistic concurrency control perform worse than pessimistic locking under high contention, even though OCC avoids lock overhead?

Model answer: Under high contention, many transactions compete for the same rows, so a large fraction of OCC transactions will find that someone else modified the row before they committed. Each failed transaction must discard all its work, re-read the current data, redo the computation, and attempt the update again — only to potentially fail again. This retry storm means the system burns CPU and I/O on work that is repeatedly discarded. Pessimistic locking, by contrast, makes transactions wait in turn and then complete successfully — no work is wasted. The relative efficiency flips depending on whether wasted retries outweigh the cost of blocking waits.

The key insight is that OCC's efficiency advantage depends on the assumption that most transactions will succeed on their first try. When that assumption breaks down under contention, the retry cost dominates.