Optimistic Locking

Also Known As

TL;DR

Explanation

A version INT (or updated_at DATETIME) column is incremented on every UPDATE. The WHERE clause includes AND version = :expected — if rowCount() returns 0, another process changed the row first. Advantages: no lock contention, scales well for read-heavy workloads. Disadvantages: requires retry logic, degrades under high write contention on the same row. Preferred over pessimistic locking for most web application patterns.

Common Misconception

Why It Matters

Common Mistakes

• Not implementing retry logic — silently discarding the failed update.
• Using updated_at as the version — two updates within the same second both pass the check.
• Using optimistic locking for financial operations where a lost update is unacceptable.

Avoid When

• Avoid when conflict rate is high — retries become the dominant code path.
• Do not use for financial transfers where a missed update is a data integrity failure.

When To Use

• Use for read-heavy workflows where conflicts are infrequent.
• Use when holding locks between read and write is impractical — e.g. user edits a form for 30 seconds.

Code Examples

// Last write wins — concurrent change silently overwritten
$pdo->prepare('UPDATE products SET name = ? WHERE id = ?')->execute([$newName, $id]);

$stmt = $pdo->prepare('SELECT id, name, version FROM products WHERE id = ?');
$stmt->execute([$id]);
$product = $stmt->fetch();

$stmt = $pdo->prepare(
    'UPDATE products SET name = ?, version = version + 1 WHERE id = ? AND version = ?'
);
$stmt->execute([$newName, $id, $product['version']]);
if ($stmt->rowCount() === 0) {
    throw new ConcurrentModificationException('Row was modified by another process — retry');
}

References

• ↗ https://martinfowler.com/eaaCatalog/optimisticOfflineLock.html

Tags

Related Terms