Concurrency Control II: Versioning (Optimistic)

Assume conflicts are rare

Locking is pessimistic: it assumes conflicts will happen and pays the cost of preventing them on *every* access. Versioning is the optimistic approach: it assumes conflicts are rare, lets transactions proceed without locking, and only checks for a conflict at commit time.

How it works:

- each transaction sees the database as it was when the transaction started (a consistent snapshot); - when a transaction modifies a record, the DBMS creates a new version of that record instead of overwriting the old one — so no lock is needed; - at commit, the DBMS checks whether another transaction has already committed a conflicting change to the same data. If not, the new version is merged in. If so, this transaction is rolled back and restarted.

Because readers never overwrite and writers create new versions, read-only transactions run concurrently with writers without blocking — readers never wait for writers, writers never wait for readers.

The trade-off

- Advantage: much better performance in read-heavy workloads (reporting, web reads) — the reader/writer contention that dominates locking simply disappears. - Disadvantage: when a write conflict *does* occur, the losing transaction must be rolled back and restarted (work is redone), and the application must handle that restart. If conflicts are frequent, versioning thrashes on rollbacks and locking would have been better.

The rule of thumb: versioning wins when reads dominate and conflicts are rare; locking wins when contention on the same data is high.

MVCC in the real world

Many production databases — PostgreSQL and Oracle among them — implement versioning as Multi-Version Concurrency Control (MVCC): readers see a snapshot as of their start time while writers create new row versions, eliminating nearly all reader/writer contention. So the optimistic model isn't just theory; it is how the database running these very exercises behaves.

Worked example: optimistic vs. pessimistic on the same scenario

A reporting dashboard runs hundreds of long read-only queries while a few clerks occasionally update an order's status.

- Under locking (pessimistic): every clerk's exclusive lock blocks any report touching that order's table/page — the read-heavy dashboard stalls behind a handful of writers. - Under versioning (optimistic): each report reads a consistent snapshot and never blocks; a clerk's update writes a new version and only conflicts (rarely) with another clerk editing the *same* order. The dashboard runs at full speed.

Read-heavy, rare conflicts → versioning is the clear win. Flip it to many clerks fighting over the *same* hot order and the constant rollbacks would favor locking instead.