Race Condition #

A race condition is the slipperiest kind of bug in the backend world. It doesn’t show up in local development because there’s only one request at a time. It doesn’t show up in staging because the traffic is too small. It only shows up in production, when dozens or hundreds of requests hit the same point simultaneously — and even when it appears, it doesn’t always leave a clear trail in the logs. What’s left behind is just wrong data: a balance that didn’t decrease twice even though two transactions happened, stock that went negative even though there was validation, or two users registered with the same email even though there’s a duplicate check in the code.

This is what makes race conditions dangerous: not because they’re hard to fix, but because they’re hard to find. The system looks like it’s running normally. No errors in the logs. No exceptions thrown. Only data that slowly becomes untrustworthy — and that lost trust is far more expensive than any bug that throws an exception.

This article covers race conditions from the database perspective — where they most often happen, what their concrete forms look like with visual timelines, and the correct techniques for handling them.

Why the Database Is the Main Battlefield for Race Conditions #

Many developers assume the database is safe from race conditions because it’s transactional. This assumption is partly true — but only partly. The database does provide mechanisms to prevent race conditions, but those mechanisms aren’t active by default for every scenario. You need to know when and how to use them.

Race conditions in the database almost always follow the same pattern: read, then act based on that read, then write. Between the read and the write lies the vulnerable time window. If another process reads the same data within that window, both will act based on identical data — and one of those actions will produce an invalid state.

The common race-prone pattern:

sequenceDiagram
    participant TA as Thread A
    participant TB as Thread B
    Note over TA, TB: "The common race-prone pattern"
    TA->>Database: READ data
    Note over TA, TB: "[Vulnerable window]"
    TB->>Database: READ data
    TA->>Database: WRITE calculation result
    TB->>Database: "WRITE calculation result (overwrites Thread A's update)"

What makes this worse in the modern era: almost every backend runs with many instances simultaneously, many workers processing job queues, and thousands of users sending requests in the same second. Race conditions that were previously very rarely triggered can happen dozens of times per minute as traffic rises — and along with it, corrupted data accumulates.

Three Most Common Forms of Race Conditions #

Check-Then-Act: The Trap That Looks Safe #

The most classic race condition form. Code checks a certain condition, then acts based on that check. The problem: between checking and acting, the condition can already have changed due to another process running in parallel.

The most common example: duplicate validation in the application layer before an insert.

-- ANTI-PATTERN: check-then-insert without database protection

-- Step 1: check whether the email already exists
SELECT COUNT(*) FROM users WHERE email = '[email protected]';
-- Result: 0 → "safe" to insert

-- Step 2: insert the new user
INSERT INTO users (email, name) VALUES ('[email protected]', 'Ali');

It looks safe with only one request. But this is what happens when two requests arrive almost simultaneously:

Timeline of two concurrent requests — check-then-act:

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

    Note over A, B: "Timeline of two concurrent requests — check-then-act"
    Note over A, DB: "T=0ms"
    A->>DB: "SELECT COUNT(*)"
    DB-->>A: "result: 0"

    Note over B, DB: "T=1ms"
    B->>DB: "SELECT COUNT(*)"
    DB-->>B: "result: 0"

    Note over A, DB: "T=2ms"
    A->>DB: "INSERT ([email protected])"

    Note over B, DB: "T=3ms"
    B->>DB: "INSERT ([email protected])"

    Note over DB: "T=4ms: Two rows with the same email are stored!"

Both saw COUNT = 0 before either managed to commit. Both proceeded to INSERT. The result is duplicate data that should never have existed.

The right solution isn’t adding more validation in the application layer — that only shrinks the time window, it doesn’t close it. The right solution is making the database guard uniqueness, not the application code:

-- CORRECT: uniqueness guarded by the database, not just the application layer

-- Add a UNIQUE constraint
ALTER TABLE users ADD UNIQUE KEY uk_users_email (email);

-- Now if two inserts happen at the same time:
-- The first insert succeeds.
-- The second insert gets: Duplicate entry '[email protected]' for key 'uk_users_email'
-- Catch this error in the app and return an "email already registered" response.

-- Or use INSERT ... ON DUPLICATE KEY for more elegant handling:
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Ali')
ON DUPLICATE KEY UPDATE name = name; -- no-op if duplicate
-- rows_affected = 1 → new insert succeeded
-- rows_affected = 0 → already exists, nothing changed

The same pattern applies to many other contexts: stock checks before orders, voucher validation before use, event capacity checks before registration. All “check first, then act” patterns are vulnerable to race conditions without a safeguard at the database level.

Lost Updates: Changes That Disappear Without a Trace #

A lost update happens when two processes read the same data, each computes changes based on the value they read, then both write their calculation results. One update overwrites the other — and the overwritten update is lost as if it never happened.

The easiest scenario to understand is a balance deduction:

-- ANTI-PATTERN: read-modify-write in the application layer without locking

-- Transaction A (withdraw Rp 10,000):
SELECT balance FROM wallets WHERE user_id = 1;  -- balance = 100,000
-- [compute in the app: 100,000 - 10,000 = 90,000]
UPDATE wallets SET balance = 90000 WHERE user_id = 1;

-- Transaction B (payment of Rp 30,000) — running almost simultaneously:
SELECT balance FROM wallets WHERE user_id = 1;  -- balance = 100,000
-- [compute in the app: 100,000 - 30,000 = 70,000]
UPDATE wallets SET balance = 70000 WHERE user_id = 1;

The lost update timeline:

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B
    participant DB as Database

    Note over A, B: "Lost update timeline"
    Note over A, DB: "T=0ms"
    A->>DB: "SELECT balance"
    DB-->>A: "100,000"

    Note over B, DB: "T=1ms"
    B->>DB: "SELECT balance"
    DB-->>B: "100,000"

    Note over A, DB: "T=2ms"
    A->>DB: "UPDATE balance = 90,000"

    Note over B, DB: "T=3ms"
    B->>DB: "UPDATE balance = 70,000"

    Note over DB: "T=4ms: Final balance in the database = 70,000 (should be 60,000)"

Both succeeded. No errors. No exceptions. But the final balance is wrong — and in a financial system context, that discrepancy is a real loss appearing on either the user’s side or the business’s side.

Double Processing: One Job Done Twice #

This third form is especially common in systems using job queues or message brokers. Two workers grab the same job at the same time, both see the job status as “unprocessed”, both process it — and the result is double processing: an email sent twice, a payment processed twice, notifications appearing twice.

-- ANTI-PATTERN: workers grabbing jobs without an atomic claim

-- Worker A:
SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1;
-- gets job_id = 42, status = 'pending'
UPDATE jobs SET status = 'processing' WHERE id = 42;

-- Worker B (running at almost the same time):
SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1;
-- also gets job_id = 42 — because Worker A hadn't committed when B read
UPDATE jobs SET status = 'processing' WHERE id = 42;

-- Result: two workers processing the same job

Isolation Levels: Not a Complete Shield #

One of the most common misconceptions: “we’re already using transactions, so we must be safe from race conditions.” Transactions with a certain isolation level do prevent some anomaly types — but not all of them.

The four isolation levels and the anomalies that can still occur:

┌──────────────────────┬─────────────┬──────────────────┬──────────────┬─────────────┐
│ Isolation Level      │ Dirty Read  │ Non-Repeatable   │ Phantom Read │ Lost Update │
│                      │             │ Read             │              │             │
├──────────────────────┼─────────────┼──────────────────┼──────────────┼─────────────┤
│ READ UNCOMMITTED     │ Can happen  │ Can happen       │ Can happen   │ Can happen  │
│ READ COMMITTED       │ Prevented   │ Can happen       │ Can happen   │ Can happen  │
│ REPEATABLE READ      │ Prevented   │ Prevented        │ Can happen   │ Can happen  │
│ SERIALIZABLE         │ Prevented   │ Prevented        │ Prevented    │ Prevented   │
└──────────────────────┴─────────────┴──────────────────┴──────────────┴─────────────┘

Note: MySQL InnoDB REPEATABLE READ prevents some lost update cases
for pure UPDATE operations, but not for the read-compute-write pattern
in the application layer like the balance example above.

SERIALIZABLE does prevent all anomalies — but at a very high cost. The database must ensure every transaction runs as if no other transaction were running concurrently. In high-concurrency systems, this can dramatically reduce throughput.

The conclusion: an isolation level is one layer of defense, not the only one. You need more precise techniques matched to the type of race condition you’re facing.

Four Techniques for Handling Race Conditions #

1. Atomic Operations — Eliminate the Time Window #

The most elegant way to handle a race condition is to eliminate the time window entirely. If the read and write can be done in a single atomic query, there’s no gap for another process to slip in between them.

-- ANTI-PATTERN: read in the app → compute → separate write
SELECT balance FROM wallets WHERE user_id = 1;     -- the time window opens here
-- [calculation in the application layer]
UPDATE wallets SET balance = [result] WHERE user_id = 1;

-- CORRECT: calculate directly in the database in a single atomic query

-- Balance deduction with validation:
UPDATE wallets
SET balance = balance - 10000
WHERE user_id = 1
  AND balance >= 10000;
-- rows_affected = 1 → succeeded, sufficient balance
-- rows_affected = 0 → insufficient balance, reject the transaction

-- Claim a job from a queue atomically — only one worker succeeds:
UPDATE jobs
SET status = 'processing',
    worker_id = 'worker-A',
    started_at = NOW()
WHERE id = 42
  AND status = 'pending';
-- rows_affected = 1 → this worker processes the job
-- rows_affected = 0 → the job was already taken by another worker, find the next one

-- Increment a counter without a race condition:
UPDATE articles SET view_count = view_count + 1 WHERE id = 99;

The key: the database executes this single query atomically. There’s no time window between read and write. Other processes can only execute the same query before or after — never in the middle.

The conditional update pattern — WHERE id = X AND status = 'expected_condition' — is the simplest and most effective technique for handling race conditions in most cases. Always check rows_affected after the update: if it’s 0, the condition wasn’t met and another process already changed the data first.

2. Pessimistic Locking — Lock First, Process Later #

Pessimistic locking assumes conflicts will definitely happen, so it’s better to lock the data from the start. This is done with SELECT ... FOR UPDATE, which locks the selected rows until the transaction finishes. Other processes trying to lock the same rows will wait.

-- CORRECT: pessimistic locking with SELECT FOR UPDATE
START TRANSACTION;

-- Lock this row. Other processes doing SELECT FOR UPDATE on the same row
-- will be blocked until this transaction COMMITs or ROLLBACKs.
SELECT balance FROM wallets WHERE user_id = 1 FOR UPDATE;

-- Now it's safe for complex business logic in the application layer —
-- no other process can change this row.
-- [validate balance, compute fees, determine transfer values, etc.]

UPDATE wallets SET balance = balance - 10000 WHERE user_id = 1;

COMMIT;
-- The lock is released. Waiting processes can now proceed with the latest data.

Pessimistic locking visualization:

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B
    participant DB as Database

    Note over A, B: "Pessimistic locking visualization"
    Note over A, DB: "T=0ms"
    A->>DB: BEGIN

    Note over A, DB: "T=1ms"
    A->>DB: "SELECT ... FOR UPDATE (Lock acquired)"

    Note over B, DB: "T=2ms"
    B->>DB: BEGIN

    Note over B, DB: "T=3ms"
    B->>DB: "SELECT ... FOR UPDATE (WAITING/Blocked)"

    Note over A: "T=4ms: Business processing"

    Note over A, DB: "T=5ms"
    A->>DB: UPDATE balance

    Note over A, DB: "T=6ms"
    A->>DB: COMMIT (Lock released)

    Note over B, DB: "T=7ms"
    DB-->>B: "Lock acquired (SELECT ... FOR UPDATE)"

    Note over B: "T=8ms: Business processing with the latest data"

    Note over B, DB: "T=9ms"
    B->>DB: UPDATE balance

    Note over B, DB: "T=10ms"
    B->>DB: COMMIT
Never make HTTP calls to external services, perform file operations, or heavy computation inside a transaction that’s holding a lock. The longer the transaction stays open, the longer other processes wait. In high-concurrency systems, a lock held open for 2–3 seconds can create long queues that end in cascading timeouts.

3. Optimistic Locking — Detect Conflicts After They Happen #

Optimistic locking takes the opposite approach: nothing is locked at the start. Every process is free to read and process. But before writing, the process must prove the data hasn’t changed since it read it. If it has changed, the write is rejected.

How it works: add a version column to the table. Every update increments the version. When updating, include the version that was read as a condition — if the version has changed, another process wrote first.

-- Set up the version column
ALTER TABLE wallets ADD COLUMN version INT NOT NULL DEFAULT 0;

-- Step 1: read the data along with its version
SELECT balance, version FROM wallets WHERE user_id = 1;
-- balance = 100,000, version = 5

-- [business processing in the application layer]

-- Step 2: update, including the version as a condition
UPDATE wallets
SET balance    = 90000,
    version    = version + 1
WHERE user_id  = 1
  AND version  = 5;                   -- only succeeds if version is still 5

-- Check rows_affected:
-- = 1 → succeeded, no conflict
-- = 0 → version changed, another process was faster → retry or error

Optimistic locking visualization:

sequenceDiagram
    participant A as Process A
    participant B as Process B
    participant DB as Database

    Note over A, B: "Optimistic locking visualization"
    Note over A, DB: "T=0ms"
    A->>DB: "SELECT balance=100k, version=5"

    Note over B, DB: "T=1ms"
    B->>DB: "SELECT balance=100k, version=5"

    Note over A, DB: "T=2ms"
    A->>DB: "UPDATE ... WHERE version=5"
    DB-->>A: "Success (version becomes 6)"

    Note over B, DB: "T=3ms"
    B->>DB: "UPDATE ... WHERE version=5"
    DB-->>B: "Failed (rows_affected = 0) -> Retry/Conflict"
Choose the right locking technique based on the situation:

  Situation                                         Right Technique
  ─────────────────────────────────────────────    ──────────────────────────
  Logic can be summarized in one query              Atomic Operation (best)
  Complex logic, high conflict rate                 Pessimistic Locking
  Complex logic, low conflict rate                  Optimistic Locking
  Operations that must never happen twice           Idempotency Key

4. Idempotency Keys — Protection from Double Processing #

For operations that must never happen twice — payments, email sending, order creation — an idempotency key is the most reliable mechanism. The client sends a unique key with every request. The server stores this key after the operation succeeds. Duplicate requests carrying the same key get the same response without the operation being re-executed.

-- Set up the idempotency key table
CREATE TABLE idempotency_keys (
    key_value    VARCHAR(255)    NOT NULL,
    response     JSON            NOT NULL,
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY  (key_value)
);

-- The application-layer flow for every request:

-- Step 1: try to "claim" the idempotency key
INSERT INTO idempotency_keys (key_value, response)
VALUES ('checkout-req-abc123', '{}')
ON DUPLICATE KEY UPDATE key_value = key_value;
-- rows_affected = 1 → new key, proceed with the process
-- rows_affected = 0 → key already exists, fetch the previous result and return it

-- Step 2: if it's a new key (rows_affected = 1), process the operation in a transaction
START TRANSACTION;

INSERT INTO orders (user_id, total, status)
VALUES (1, 150000, 'pending');

-- Step 3: save the result into idempotency_keys
UPDATE idempotency_keys
SET response = JSON_OBJECT('order_id', LAST_INSERT_ID(), 'status', 'created')
WHERE key_value = 'checkout-req-abc123';

COMMIT;

-- Step 4: for duplicate requests, return the stored result without reprocessing
SELECT response FROM idempotency_keys WHERE key_value = 'checkout-req-abc123';

With an idempotency key, no matter how many times the same request is sent — the result is always the same and the operation is only executed once.

Deadlocks: The Side Effect of Careless Locking #

A deadlock is a condition that can appear as a side effect of pessimistic locking with inconsistent access order. Two transactions each wait for a lock held by the other — and neither can move forward.

The classic deadlock scenario:

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B
    participant DB as Database

    Note over A, B: "The classic deadlock scenario"
    Note over A, DB: "T=0ms"
    A->>DB: BEGIN

    Note over A, DB: "T=1ms"
    A->>DB: "SELECT wallets id=1 FOR UPDATE (Lock acquired)"

    Note over B, DB: "T=2ms"
    B->>DB: "BEGIN & SELECT orders id=10 FOR UPDATE (Lock acquired)"

    Note over A, DB: "T=3ms"
    A->>DB: "SELECT orders id=10 FOR UPDATE (WAITING/Blocked)"

    Note over B, DB: "T=4ms"
    B->>DB: "SELECT wallets id=1 FOR UPDATE (WAITING/Blocked)"

    Note over A, B: "T=5ms: DEADLOCK detected by the database!"
    DB-->>A: "Rollback (Transaction A rolled back as the victim)"
    Note over B: "Transaction B is freed and can continue"

Modern databases detect deadlocks automatically and roll back one of the transactions. But every deadlock means a failed transaction that must be retried — and a high deadlock frequency is a sign of an architectural problem.

The prevention is simple: always access tables and rows in a consistent order across the entire codebase.

-- ANTI-PATTERN: different lock orders in two places → deadlock risk

-- In service A:
SELECT * FROM wallets WHERE id = 1 FOR UPDATE;   -- locks wallets first
SELECT * FROM orders WHERE id = 10 FOR UPDATE;   -- then orders

-- In service B (reversed order):
SELECT * FROM orders WHERE id = 10 FOR UPDATE;   -- locks orders first
SELECT * FROM wallets WHERE id = 1 FOR UPDATE;   -- then wallets ← DEADLOCK

-- CORRECT: the same order everywhere
-- Establish a convention: always lock wallets before orders
-- Never the reverse, anywhere in the codebase.

-- In service A:
SELECT * FROM wallets WHERE id = 1 FOR UPDATE;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;

-- In service B (SAME order):
SELECT * FROM wallets WHERE id = 1 FOR UPDATE;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;

Anti-Patterns to Avoid #

-- ✗ Anti-pattern 1: uniqueness validation only in the application layer
SELECT COUNT(*) FROM registrations WHERE event_id = 5;
-- If 0 or below capacity, proceed with the insert.
-- A race condition can let many requests through at once.

-- ✓ Solution: UNIQUE constraint in the database + catch the duplicate error in the app

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 2: HTTP calls inside a transaction holding a lock
START TRANSACTION;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
-- [HTTP call to the payment gateway — can take 1-5 seconds]
-- This lock blocks ALL other processes needing order id=10 for 1-5 seconds
UPDATE orders SET status = 'paid' WHERE id = 10;
COMMIT;

-- ✓ Solution: do external operations outside the transaction
payment_result = payment_gateway.charge(amount)   -- outside the transaction
START TRANSACTION;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
UPDATE orders SET status = payment_result.status WHERE id = 10;
COMMIT;

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 3: relying on a Redis lock alone without a database safety net
-- Redis locks can fail because: Redis is down, the lock expires too quickly,
-- a network partition, or a process crashing after releasing the lock but before
-- the operation finishes. Without a database constraint, race conditions can slip through.

-- ✓ Solution: Redis locks as an optimization to reduce contention,
-- database constraints as the final guarantee that can't be bypassed.

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 4: unlimited retries without backoff
-- If many processes hit conflicts simultaneously and all retry immediately,
-- they'll keep colliding → a retry storm that worsens the situation.

-- ✓ Solution: limit the retry count (e.g. max 3 attempts),
-- add jitter (random delay) between retries to reduce collisions,
-- and return a clear error to the user if all retries fail.

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 5: locking an entire table for a problem that row-level locks suffice
LOCK TABLES wallets WRITE;   -- locks ALL rows in the entire table
-- [process one transaction]
UNLOCK TABLES;
-- No other query to the wallets table can run during this time.

-- ✓ Solution: use precise row-level locks
SELECT * FROM wallets WHERE user_id = 1 FOR UPDATE;
-- Only locks the row for user_id = 1, other rows remain accessible.

Race Condition Review Checklist #

RISK IDENTIFICATION:
  □ All endpoints callable concurrently are mapped
  □ All read-then-write pattern operations identified
  □ Financial operations and state machines reviewed specifically
  □ Job queues and background workers reviewed for double processing

ATOMIC OPERATIONS:
  □ Simple operations (increment, decrement, status claims) use
    a single atomic query with a conditional WHERE
  □ rows_affected always checked after conditional updates
  □ No SELECT → compute in the app → UPDATE pattern for critical data

PESSIMISTIC LOCKING:
  □ SELECT FOR UPDATE always inside an explicit transaction
  □ No HTTP calls, file I/O, or sleeps inside a locking transaction
  □ Lock duration as short as possible — only during database operations
  □ Table/row locking order consistent across the entire codebase

OPTIMISTIC LOCKING:
  □ A version column exists and is incremented on every update
  □ The version condition included in every critical UPDATE
  □ A retry mechanism with a maximum limit and backoff exists
  □ Conflict errors communicated clearly to the user or caller

IDEMPOTENCY:
  □ Operations that must never happen twice use idempotency keys
  □ Idempotency keys stored in the database (not just a cache)
  □ The same key always returns the same result without re-execution

DATABASE CONSTRAINTS:
  □ Uniqueness that's a business rule guarded by UNIQUE constraints
  □ The database is the last line of defense, not the application layer
  □ Duplicate errors from the database handled correctly in the app

MONITORING:
  □ Deadlocks monitored — rising frequency is an early warning sign
  □ Optimistic locking conflict rates monitored
  □ rows_affected = 0 on critical operations logged for investigation

Summary #

  • Race conditions live in the time window between read and write — the shorter the window, the smaller the risk. Atomic operations eliminate the window entirely.
  • The check-then-act pattern is the most common race condition source — application-layer validation isn’t enough because there’s a gap between check and action. Database constraints are the safeguard no concurrent request can bypass.
  • Lost updates happen when two processes read the same data then both write — the result is one update disappearing without any error trace. Use atomic updates or locking to prevent it.
  • Isolation levels aren’t a complete solution — even REPEATABLE READ is still vulnerable to lost updates on the read-compute-write pattern in the application layer. Choose the technique matching the problem type.
  • Atomic operations are the best technique for simple cases — one query combining validation and change, with no time window.
  • Pessimistic locking fits complex logic with high conflict rates — but don’t put slow operations inside a locking transaction.
  • Optimistic locking is lightweight and non-blocking — fits low conflict rates; make sure there’s a reasonable retry limit with backoff.
  • Idempotency keys are the only reliable way to prevent double processing for operations like payments and email sending.
  • Deadlocks happen when lock orders are inconsistent — establish a table/row access order convention across the entire codebase and follow it without exception.
  • The database is the last line of defense — application locks (Redis, mutexes) are fine as optimizations to reduce contention, but can’t replace constraints and locking at the database level.

← Previous: Replication   Next: Locking →

About | Author | Content Scope | Editorial Policy | Privacy Policy | Disclaimer | Contact