Implementing Optimistic Locking

Optimistic locking updates rows only when a previously-read version still matches.

What It Does

Optimistic locking updates rows only when a previously-read version still matches. The following examples distinguish a normal use case, a boundary case and a practical application.

Requirements and Compatibility

PHP 8.x and a local test environment. Apply input validation, correct error handling and the requirements of any optional libraries.

The code focuses on the core concept. Production implementation may also need routes, validation, persistence, authorization or deployment tooling.

Example 1: Basic Behavior

Use an atomic version predicate in the UPDATE statement.

<?php
$sql = "UPDATE orders SET status = :status, version = version + 1 WHERE id = :id AND version = :expected";
echo $sql;

Example 2: Another Case

Check the affected row count; zero means the expected version was not updated.

<?php
// $pdo is an authenticated PDO connection configured to throw exceptions.
$stmt = $pdo->prepare('UPDATE products SET stock = :stock, version = version + 1 WHERE id = :id AND version = :version');
$stmt->execute(['stock' => 9, 'id' => 42, 'version' => 5]);
if ($stmt->rowCount() !== 1) throw new RuntimeException('Concurrent change or missing row');

This example depends on an installed extension, third-party library or configured service and is not a standalone local test.

Practical Example: Putting It to Use

A conflict should trigger a controlled reread instead of overwriting newer data.

<?php
$knownVersion = 7; $databaseVersion = 8;
if ($knownVersion !== $databaseVersion) echo "Reload and retry";

Important Behavior and Edge Cases

An application-side comparison without atomic UPDATE conditions can still lose concurrent writes.

Comparison with Related Functions or Approaches

Common Beginner Mistakes

Key Takeaways

• Optimistic locking updates rows only when a previously-read version still matches.
• An application-side comparison without atomic UPDATE conditions can still lose concurrent writes.
• Use WHERE version=:expected and check affected-row count inside the transaction.

Before adapting these examples, verify the return values, test the failure paths and confirm that your application meets its security and environment requirements.