Why Optimistic Locking Improves High‑Concurrency Performance

Optimistic Locking

Optimistic locking is a concurrency‑control strategy that assumes conflicts are rare. Instead of acquiring a database lock for the whole transaction, the application stores a version identifier (e.g., an integer column version or a timestamp) when reading a row. When the row is later updated, the UPDATE statement includes a condition that the stored version matches the original version. If the condition succeeds, the row is updated and the version is incremented; otherwise the update fails, indicating a concurrent modification.

Typical Implementation

-- Table definition
CREATE TABLE account (
    id        BIGINT PRIMARY KEY,
    balance   DECIMAL(15,2),
    version   INT NOT NULL DEFAULT 0
);

-- Read
SELECT id, balance, version FROM account WHERE id = 123;

-- Update (optimistic check)
UPDATE account
SET balance = balance + 100,
    version = version + 1
WHERE id = 123 AND version = :original_version;

An alternative is to use a timestamp column instead of an integer version; the update checks that the timestamp read earlier is still current.

Example Scenario

In a financial application an operator reads a user's account balance, performs calculations, and writes the new balance. With pessimistic locking the row would remain locked from the moment it is read until the transaction commits, potentially blocking thousands of concurrent sessions. With optimistic locking the row stays unlocked during the read‑modify‑write cycle; the version check at update time detects whether another session has modified the same row in the meantime.

Advantages

Eliminates long‑running database locks, reducing contention and improving throughput under high concurrency.

Works with standard SQL; no special database configuration is required.

Allows the application to handle conflict resolution (e.g., retry, merge) explicitly.

Limitations

Conflicts are detected only at commit time; if many concurrent updates target the same row, the retry rate may become high.

Optimistic checks are performed by the application layer, so updates performed outside the application (direct scripts, other services) bypass the version check and can introduce dirty data.

To protect against external updates, the version logic can be moved into a stored procedure or trigger, exposing only controlled update interfaces.