Database

Prevent Lost Updates in Concurrent Applications

Protect read-modify-write operations with atomic SQL, optimistic locking, or row-level locks.

Overview

A lost update occurs when two requests read the same value, independently calculate a replacement, and then overwrite one another. The application may return success twice while one change silently disappears. This often appears in inventory, counters, account balances, and editable settings.

Start with the workload and the invariant

The first choice is often to express the operation as one atomic SQL statement. Instead of reading a quantity into PHP, subtracting one, and writing the result, execute UPDATE inventory SET quantity = quantity - 1 WHERE id = ? AND quantity > 0. Check the affected-row count. This lets the database evaluate the condition and update as one statement.

Practical example

UPDATE inventory
SET quantity = quantity - 1
WHERE id = 123
  AND quantity > 0;

-- In PHP, require rowCount() === 1.

For records edited through a form, use optimistic locking. Add an integer version column. The form submits the version it originally read; the update succeeds only when the stored version still matches. Increment the version in the same statement. A zero-row update means the record changed or disappeared and the user must reload or resolve the conflict.

UPDATE documents
SET title = ?, body = ?, version = version + 1
WHERE id = ? AND version = ?;

Trade-offs and failure modes

Use pessimistic locking when the operation must inspect a row and then make a decision that cannot be represented safely in one statement. Start a transaction, select the row with FOR UPDATE, validate it, update it, and commit quickly. Keep lock acquisition order consistent across code paths.

Verification and rollout

Do not assume that disabling the submit button prevents concurrency. Multiple tabs, retries, mobile clients, background jobs, and network timeouts can all submit the same operation. Use unique idempotency keys for operations that may be retried, and enforce important uniqueness rules with database constraints.

Engineering checklist

Write a concurrency test that launches two workers against the same starting state. Assert the final value and the number of successful operations. A solution is not proven by a single request in a development browser.

Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.

Share this page Help other developers discover this resource.