Timestamps, Optimistic Control and MVCC

Alternatives to waiting on locks

Timestamp ordering gives each transaction a timestamp and ensures conflicting operations happen in timestamp order; each data item tracks its largest read and write timestamps, and an operation that would violate the order causes its transaction to abort and restart. Thomas's write rule allows ignoring obsolete writes. Optimistic concurrency control assumes conflicts are rare: a transaction reads and writes privately, then a validation phase checks for conflicts before applying its writes. Applications use the same idea with version columns: UPDATE ... SET version = version + 1 WHERE id = ? AND version = ? fails if someone else changed the row. Multiversion concurrency control (MVCC), used by PostgreSQL, Oracle, MySQL InnoDB and others, keeps several versions of each row. Readers see a consistent snapshot as of their start time and never block writers, and writers do not block readers; writers still conflict with other writers on the same row. Old versions must be cleaned up later (VACUUM in PostgreSQL, purge in InnoDB), and long-running transactions can hold back that cleanup.

Optimistic locking with a version column

The update succeeds only if nobody changed the row since it was read.

-- read
SELECT id, title, price, version FROM products WHERE id = 42;
-- returns version = 7

-- write back, guarded by the version we read
UPDATE products
SET    price = 549, version = version + 1
WHERE  id = 42 AND version = 7;

-- rows affected = 1 -> success
-- rows affected = 0 -> someone else updated it first: reload and retry or report a conflict

Editing a shared document

MVCC is like everyone reading their own printed copy of a document while the editor works on the next version. Optimistic locking is checking the version number before saving: if it changed while you were editing, you merge before overwriting.

Quick check: What is a key benefit of MVCC?

• It never stores more than one version of a row
• Readers see a consistent snapshot and do not block writers
• It removes the need for transactions
• It prevents all deadlocks between writers