Optimistic locking isn't working: Four implementation bugs leading to double bookings

A notification arrives from the field: "There are two bookings in the exact same time slot." Checking the logs reveals that both requests completed normally, with not a single exception recorded. Looking at the database, sure enough, two records exist.

The exact same thing happens with inventory. Two users place an order simultaneously for an item with only 1 in stock, driving inventory to -1. Both orders clear payment, resulting in zero errors on the system side.

What makes this class of defect so tricky is that it does not reproduce easily. Executing steps sequentially on a local machine will never trigger it. It occurs only when requests collide concurrently, within windows of a few hundred milliseconds. And it routinely happens in systems where concurrency control was already "put in place"—because a slightly flawed implementation yields virtually the same outcome as having no locking at all.

What disappears is one of the updates itself

A lost update occurs in the following sequence:

1. Request A reads the corresponding row in the reservations table: available.
2. Request B reads the same row: also available.
3. A decides "it's available, so I can book" and writes the update.
4. B also decides "it's available, so I can book" and writes the update.

What B read was the state before A wrote. From B's perspective, the state was valid and the decision was correct. What is flawed is that B has no way of detecting that the state changed between making the decision and writing the update.

Concurrency control is all about providing this "means of detection." The approaches broadly divide into two. Pessimistic locking forces others to wait at the moment of reading. Conflicts do not occur, making it reliable, but it introduces wait times. Optimistic locking does nothing at read time, and checks at the exact moment of writing whether "anyone else touched the data after I read it." Provided conflicts are rare, this is faster because it incurs no waiting.

The issue is whether that concept of "checking at the moment of writing" is correctly represented in the implementation.

The moment check and update diverge, optimistic locking becomes ineffective

The standard way to build optimistic locking is to give the target row a version column and increment it by 1 on every update. Up to this point, most implementations are identical.

Where they diverge is what comes next. The following code may look like it works, but in reality, it protects nothing.

// 読む
Reservation r = repo.findById(id);

// 比較する
if (r.getVersion() != requestVersion) {
    throw new ConflictException();
}

// 更新する
r.setStatus("BOOKED");
r.setVersion(r.getVersion() + 1);
repo.save(r);

At the moment of comparison, version matches for both A and B, so both pass the check. Subsequently, both issue an UPDATE, and whichever arrives later wins. The very "lost update" you sought to prevent happens right after implementing optimistic locking.

The cause is that comparison and update are separate operations. A gap exists between the two, and other transactions can slip into that gap. To prevent this, comparison and update must be combined into a single database statement.

UPDATE reservations
   SET status  = 'BOOKED',
       version = version + 1
 WHERE id      = :id
   AND version = :expected_version;

With this structure, evaluating the condition and updating happen within the exact same statement, leaving no gap. The later transaction will see a mismatched version, and will return an affected row count of 0.

Failing to inspect the affected row count causes silent breakage

This is the second pitfall. Even if you write the SQL above, it is meaningless unless you inspect the returned affected row count. Because returning 0 rows is still considered successful in SQL, no exception is raised.

int updated = jdbc.update(SQL, params);
if (updated == 0) {
    throw new OptimisticLockException();  // ここが無いと、失敗が成功として通る
}

When using an ORM, mechanisms corresponding to @Version usually check the affected row count and throw an exception, but depending on the configuration, paths may remain that pass through silently. Bulk updates, native queries, and syntax utilizing INSERT ... ON CONFLICT fall outside framework version tracking. Rather than dismissing it with "our ORM handles it so we're fine," it is worth verifying whether that specific code path actually validates the version.

Optimistic locking is unsuited for quantity management

The third scenario is choosing the wrong tool in the first place.

When you apply optimistic locking to data where "multiple users concurrently decreasing a value is everyday behavior," such as inventory counts, conflicts occur constantly. Optimistic locking is designed on the premise that conflicts are rare; where they happen frequently, retries pile up, latency spikes during peak loads, and failure rates increase under congestion. It exhibits the unwelcome characteristic of becoming weakest precisely when you need protection the most.

For quantities, atomic updates that place the condition inside the SQL statement are far more straightforward.

UPDATE inventories
   SET stock = stock - :qty
 WHERE sku   = :sku
   AND stock >= :qty;

Instead of reading the current value and subtracting it in application code, express "subtract only if available" in a single statement. If the affected row count is 0, stock is insufficient. No version column is needed.

A practical guideline for choosing between methods settles around the following:

Systems like reservation platforms, where slot securing, inventory decrement, and payment processing execute sequentially, blend these three data types into a single flow. Trying to standardize the entire flow on a single concurrency approach inevitably strains the architecture somewhere. Criteria for deciding whether to custom-build reservation logic or adopt existing SaaS are outlined in Reservation Systems: Build or Adopt SaaS?.

Remaining stumbling blocks

It is safer to avoid using updated_at as a substitute for a version identifier. In environments where time resolution is limited to milliseconds, two updates occurring within the same millisecond will receive the exact same value. Additionally, in architectures with multiple application servers, clock drift between servers interferes with conflict detection. Using an integer counter eliminates unnecessary edge cases.

Retries are not something you should always add. If you automatically retry after detecting a conflict, the contents displayed on the user's screen will diverge from what is actually written. In business systems, returning "Another user updated this record first; please review the latest information" and letting a human decide is more appropriate in most cases. Retries should be reserved solely for operations whose outcome remains unchanged even when repeated.

Transaction boundaries sometimes drift away from actual implementations. If the optimistic lock check lives inside a transaction, but external API calls or file writes happen outside of it, rolling back will still leave behind unmanaged side effects. How to structure table state transitions is closely tied to the concepts discussed in Status Management and Soft Delete Design, and clarifying where state is held makes it easier to reason about boundaries.

How to verify that it is not broken

Concurrency defects pass standard tests without issue. If you want to verify your implementation, you have no choice but to write tests that hit endpoints concurrently.

The setup is simple: send N update requests against the same target at the exact same moment. Then, verify both that exactly 1 succeeds while the rest return conflict errors, and that the final database state contains no inconsistencies. Testing only one side is insufficient. "Returning an error" and "having valid data" are separate matters, and checking both is what makes it a real test.

Simply synchronizing execution using CountDownLatch or Promise.all is enough to expose the implementation flaws mentioned above. If running these on every CI build is too resource-intensive, shifting them to daily batch jobs is perfectly acceptable. It is far better than releasing without ever testing concurrent execution.

What to do next

Select a table in your system prone to concurrency conflicts, such as bookings or inventory, and inspect the actual SQL statements performing updates. Check just one thing: does the UPDATE statement's WHERE clause include a condition on the version or quantity? If not, that logic is completely defenseless against concurrent execution.

If the condition is present, next confirm whether the code branches based on the affected row count. Anywhere lacking both remains an active defect waiting to happen.

GleamHub offers consultations on software development, AI, and automation, including reviewing concurrency control in existing systems and establishing testing procedures that include concurrency. Appropriate methods depend on business flows, so please reach out for an individual consultation. Feel free to contact us via Contact Us.

Sources

• Key Points in Implementing Optimistic Locking and Common Blunders — Zenn (Levtech Development Team)
• Understanding Database Optimistic and Pessimistic Locking — Zenn
• Illustrated Guide: Introduction to Optimistic and Pessimistic Locking — Zenn
• About Optimistic and Pessimistic Locking in Concurrency Control — Rainbow Engine