Tackling HTTP Request Race Conditions with Atomic Conditional Updates

In modern backend systems, especially those dealing with order processing, payments, or status-based workflows, race conditions at the HTTP request level are a very real problem that often appears uninvited. This condition is usually triggered by three scenarios that are actually quite common: clients retrying either manually or automatically, UIs unintentionally sending duplicate requests because the user pressed the submit button twice, or third-party webhooks sent more than once due to network issues on their side. If not handled correctly, race conditions like this can cause an order’s status to change repeatedly and inconsistently, heavy processes to be executed more than once, and most dangerously, side effects like payment charges, email sends, or callbacks to other systems happening twice. This article discusses one of the most solid and practical approaches to solving this problem: atomic conditional updates at the database level, using rows affected as an idempotency indicator.

A Short Case Study

To understand this problem concretely, imagine we have an orders table with a simple one-way status flow:

PENDING → PROCESSING → COMPLETED

In a real scenario, several HTTP requests can arrive almost simultaneously to process the same order. Request A and request B arrive just milliseconds apart, and both intend to change the order’s status from PENDING to PROCESSING. Without a proper protection mechanism, both could consider themselves the legitimate first request — and this is the essence of the race condition we’re discussing.

sequenceDiagram
    participant ReqA as Request A
    participant ReqB as Request B
    participant DB as Database

    Note over ReqA,ReqB: Both arrive almost simultaneously
    ReqA->>DB: Check order status = PENDING?
    ReqB->>DB: Check order status = PENDING?
    DB-->>ReqA: Yes, PENDING
    DB-->>ReqB: Yes, PENDING (doesn't know A is already processing)
    ReqA->>DB: Update status -> PROCESSING
    ReqB->>DB: Update status -> PROCESSING
    Note over DB: Without protection, both succeed

Without an atomic mechanism, these two requests can both pass the “check first, then update” validation separately, because both read the PENDING condition before either of them actually completes its change.

The Approach Used

The solution discussed in this article combines three simple elements that, when combined, become very powerful. First, wrapping the process in a database transaction so the entire sequence of operations is all-or-nothing. Second, doing a conditional UPDATE based on the current status — not just an unconditional update. Third, checking the number of updated rows, commonly called rows affected, as a signal for whether this request truly changed the state or not.

BEGIN;

UPDATE orders
SET status = 'PROCESSING'
WHERE id = :order_id
  AND status = 'PENDING';

-- check rows affected

COMMIT;

Interpreting the result of this query is simple enough to understand. If rows affected is greater than 0, it means this request is the first one that successfully changed the status — a valid request that should continue to the next process. If rows affected equals 0, it means this request is the second or later one, essentially a duplicate of an action already performed by another request before it.

ROWS AFFECTED INTERPRETATION:
  ✓ rows affected > 0  -- first request, valid, continue the process
  ✗ rows affected = 0  -- duplicate request, stop, don't repeat the side effect

The key to this approach is the WHERE status = 'PENDING' clause on the UPDATE query. Without this condition, the query will always succeed in changing the status no matter how many times it’s run, and rows affected is no longer useful as an idempotency signal.

Why Do the Second and Later Requests Get Rows Affected = 0?

This part is the core of the entire mechanism, and the most important to understand deeply so you’re confident this approach is truly safe, not just coincidentally working in certain conditions.

Implicit Locking During UPDATE

When an UPDATE statement runs against a row, the database automatically takes a lock on the relevant row without you needing to request it explicitly. This lock prevents parallel changes to the same row until the transaction holding it finishes — whether commit or rollback.

On the first request, the sequence of events goes like this: it finds the row with status = PENDING, takes a lock on that row, changes its status to PROCESSING, then commits its transaction.

sequenceDiagram
    participant ReqA as Request A
    participant DB as Database

    ReqA->>DB: BEGIN
    ReqA->>DB: UPDATE ... WHERE status='PENDING'
    Note over DB: Lock taken on the order_id row
    DB-->>ReqA: rows affected = 1
    ReqA->>DB: COMMIT
    Note over DB: Lock released, status is now PROCESSING

What Happens to the Second Request?

The second request arrives almost simultaneously and tries to run an identical query. Depending on the timing — which actually differs by only milliseconds — two possible scenarios can occur.

First scenario, the second request arrives while the lock is still active. In this situation, the second request will wait or be blocked until the first request releases the lock. After the first request commits, the lock is released, and the second request then resumes its previously blocked execution. However, at the moment of this resume, the row’s status has already changed to PROCESSING due to the first request’s action, so the WHERE status = 'PENDING' condition on the second request’s query is no longer satisfied. As a result, the outcome is rows affected = 0.

sequenceDiagram
    participant ReqA as Request A
    participant ReqB as Request B
    participant DB as Database

    ReqA->>DB: BEGIN, UPDATE WHERE status='PENDING'
    Note over DB: Lock taken
    ReqB->>DB: BEGIN, UPDATE WHERE status='PENDING'
    Note over ReqB: Waiting (blocked) because the lock is active
    ReqA->>DB: COMMIT
    Note over DB: Lock released, status = PROCESSING
    DB->>ReqB: Resume execution
    Note over ReqB: WHERE condition no longer satisfied
    DB-->>ReqB: rows affected = 0

Second scenario, the second request arrives after the commit is done. In this case, there’s no lock to wait for at all because the first request is truly finished. But the row’s status has already changed to PROCESSING, so the WHERE condition on the second request’s query fails from the very start of evaluation. The result stays the same: rows affected = 0.

The Essence: The Database Guarantees Consistency

What makes this solution truly strong, regardless of which timing scenario occurs, is that condition evaluation and the update process happen atomically in a single operation. There’s no time gap between “check status” and “update status” that another request could exploit to slip in between — both happen as one indivisible unit of work. The entire race condition is broken right at the database level, not at the application level which is far more vulnerable to timing errors. Your application only needs to read the final result and react according to the returned rows affected value.

This locking mechanism isn’t something you need to implement manually. Almost all modern relational databases — PostgreSQL, MySQL, and others — already provide this kind of implicit locking as part of their standard ACID guarantees for UPDATE operations.

This Isn’t Just Pessimistic Locking

Although it’s often called pessimistic locking in everyday discussion, this approach is actually more accurately categorized as an atomic state transition, or better known by the term compare-and-set — a pattern also commonly found outside the database context, for example in atomic memory operations in some programming languages.

Compared with other approaches commonly used to handle concurrency, conditional UPDATE has an interesting position in the trade-off between safety and complexity.

A mutex implemented at the application level fails to protect against race conditions once the application runs on more than one instance, because a lock held by one instance isn’t visible to other instances — the classic problem in horizontally scalable architectures. Redis or other distributed locks can be a solution, but they add an external infrastructure component that must be kept available, and add complexity around failure scenarios like locks not released due to a crash.

SELECT ... FOR UPDATE and version-column-based optimistic locking are both conceptually safe, but require more application-side code — from additional queries to read data before updating, to retry logic when a version conflict is detected. Conditional UPDATE simplifies all of that into a single statement that directly gives a definitive answer without additional round-trips to the database.

Overall, the conditional UPDATE approach is simpler to implement, more efficient because it only needs one query, and easier to maintain because there’s no additional state to synchronize outside the database.

The Connection to Idempotency

One of the hidden benefits of this pattern is how it naturally produces idempotency, without you needing to build a separate idempotency key mechanism for every endpoint.

With this pattern, your endpoint becomes naturally idempotent — duplicate requests won’t damage existing state, because the second and later attempts will always get rows affected = 0 and make no changes. You also don’t need to store a mutex or additional state in the application’s memory, because all the protection logic is already contained within the query itself.

flowchart TD
    A[Request arrives] --> B[Run the conditional UPDATE]
    B --> C{rows affected > 0?}
    C -- Yes --> D[Valid request, continue the process]
    C -- No --> E[Duplicate request, stop with no side effects]
    D --> F[Run the side effect: charge, email, etc.]
    E --> G[Idempotent response, no changes]

As long as these two conditions hold, the system will be safe from duplicate requests: status may only change one way according to the defined state machine, and all status transitions must go through the conditional update — there’s no other path that changes status without going through this mechanism.

Things to Watch Out For

This approach is very suitable for status updates, but there are several conditions that need extra care before you apply it blindly across the entire system.

There Are External Side Effects

If the process following a status change involves external side effects like payment charges, sending emails, or calling external APIs, make sure those side effects only run when rows affected > 0. This is the most critical rule of the entire pattern — violating it means returning to the risk you wanted to avoid from the start.

// ANTI-PATTERN: running the side effect without checking rows affected
rowsAffected := updateOrderStatus(orderID, "PROCESSING")
chargePayment(orderID) // always runs, including on duplicate requests

// CORRECT: the side effect only runs if this request successfully changed the status
rowsAffected := updateOrderStatus(orderID, "PROCESSING")
if rowsAffected > 0 {
    chargePayment(orderID)
}

Complex Workflows with Many Branches

For workflows with many possible status transitions — for example an order that can be cancelled, returned, or have several sub-statuses depending on certain conditions — consider defining the state machine explicitly. The conditional UPDATE remains relevant as its execution mechanism, but which transitions are valid should be validated against a clear state machine, not just relying on a single simple WHERE clause.

stateDiagram-v2
    [*] --> PENDING
    PENDING --> PROCESSING: conditional update
    PROCESSING --> COMPLETED: conditional update
    PROCESSING --> FAILED: conditional update
    PENDING --> CANCELLED: conditional update
    COMPLETED --> [*]
    FAILED --> [*]
    CANCELLED --> [*]

Audit and Observability

Record and log requests that produce rows affected = 0 as part of system observability. A high occurrence count of this condition could indicate another larger problem — for example clients retrying too aggressively, or third-party webhooks consistently sending duplicates more often than expected.

Don’t treat rows affected = 0 as an error that must be returned with a failing HTTP status. From the perspective of a client doing a retry, this condition is actually the correct result — the operation they requested was indeed already successfully performed before. The appropriate response is usually still a success status, possibly with additional information indicating this is the result of a request that was already processed.

Conclusion

Using a conditional UPDATE combined with a rows affected check inside a transaction is conceptually correct, concurrency-safe, performance-efficient, and practical to apply directly in production environments. This approach uses the database according to its natural strength — as a guardian of data consistency, not just a passive storage place waiting for instructions without validation.

For many idempotency and race condition cases at the HTTP request level — especially those involving one-way status changes like order processing or payments — this approach isn’t just sufficient, it’s highly recommended as the primary choice before considering more complex solutions like distributed locks or message queues with additional deduplication.

Summary

• HTTP request race conditions often come from client retries, double submits in UIs, or third-party webhooks sent repeatedly.
• The core solution: wrap in a transaction, run a conditional UPDATE based on the current status, then check rows affected as an idempotency signal.
• rows affected > 0 means this request is valid and successfully changed the state; rows affected = 0 means a duplicate request that must not trigger further side effects.
• Implicit locking on UPDATE ensures condition evaluation and status change happen atomically, with no exploitable time gap for other requests.
• More accurately called an atomic state transition (compare-and-set) rather than just pessimistic locking, and lighter in complexity than mutexes, distributed locks, SELECT FOR UPDATE, or optimistic locking.
• Endpoints become naturally idempotent as long as status only changes one way and all transitions go through the conditional update.
• External side effects (charges, emails, callbacks) must only run when rows affected > 0 — this is the most critical rule of the entire pattern.
• For complex workflows, combine this pattern with an explicit state machine, and always log rows affected = 0 occurrences for observability.