# Timestamps, Optimistic Control and MVCC — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/c-mvcc

> Describe timestamp ordering, optimistic concurrency and multiversion concurrency control.

## 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.

```sql
-- 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.

**Quiz:** What is a key benefit of MVCC?

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

*Answer:* Readers see a consistent snapshot and do not block writers. Multiple versions let reads proceed without waiting for writes, and vice versa.