Concurrency in Databases Is Tricky Until You Understand This

The race condition you didn't know you had

Your API has an endpoint that deducts credits from a user's account before processing a request. The logic is: read the balance, check it's sufficient, deduct the amount, save. In testing, it works perfectly. Under load with the same user making simultaneous requests, the balance goes negative. Two concurrent requests both read the same balance, both pass the check, both deduct. Classic race condition, classic concurrency bug.

This pattern — read, check, write — is the most common source of concurrency bugs in database-backed applications. Understanding why it fails and how to fix it correctly is the foundation of concurrent database programming.

The TOCTOU problem

Time-of-check to time-of-use (TOCTOU): the state you read may change between when you read it and when you act on it. In a database, this gap exists whenever two operations are not atomic.

# Non-atomic — subject to race condition
def deduct_credits(user_id, amount):
    balance = db.query("SELECT credits FROM users WHERE id = %s", user_id)
    if balance < amount:
        raise InsufficientCreditsError()
    db.execute("UPDATE users SET credits = credits - %s WHERE id = %s", amount, user_id)

Two concurrent calls for the same user_id, both reading balance = 100, both checking that 100 >= 50, both deducting 50. Result: balance = 50, but 100 credits were consumed.

Fix 1: Atomic update with constraint check

Move the check into the UPDATE itself and verify the result:

UPDATE users
SET credits = credits - :amount
WHERE id = :user_id AND credits >= :amount;

-- Check affected rows: 0 means insufficient credits

If the WHERE clause prevents the deduction, the UPDATE affects 0 rows. No deduction occurred. This is atomic — the check and the write happen in a single operation with no gap.

def deduct_credits(user_id, amount):
    rows_affected = db.execute(
        "UPDATE users SET credits = credits - %s WHERE id = %s AND credits >= %s",
        amount, user_id, amount
    )
    if rows_affected == 0:
        raise InsufficientCreditsError()

This works without any explicit locking in READ COMMITTED — the UPDATE acquires a row lock, and the condition is evaluated atomically at the time of the lock acquisition.

Fix 2: SELECT FOR UPDATE

When you need to read the value before deciding what to write (more complex business logic), lock the row at read time:

BEGIN;
SELECT credits FROM users WHERE id = :user_id FOR UPDATE;
-- Row is now locked. No other transaction can modify this row until we commit.
-- Your application logic runs here
UPDATE users SET credits = credits - :amount WHERE id = :user_id;
COMMIT;

SELECT FOR UPDATE tells the database: I'm going to modify this row, so lock it now. Other transactions attempting SELECT FOR UPDATE on the same row will wait until our transaction commits or rolls back. Transactions doing plain SELECT (without FOR UPDATE) will see the old value until we commit (in READ COMMITTED) or will see our committed value after we commit.

The tradeoff: this serializes all concurrent access to the locked row. Under high concurrency for the same user, this is a queue — one request at a time. For low-to-moderate concurrency, this is fine. For high-volume scenarios (a hot user hitting the endpoint 1000 times/second), consider counter patterns that don't serialize on a single row.

Fix 3: Optimistic locking

For lower-contention scenarios where conflicts are rare, optimistic locking avoids holding locks:

-- Add a version column to the table
ALTER TABLE users ADD COLUMN version BIGINT NOT NULL DEFAULT 1;

-- Read with version
SELECT credits, version FROM users WHERE id = :user_id;

-- Update only if version hasn't changed
UPDATE users
SET credits = credits - :amount, version = version + 1
WHERE id = :user_id AND version = :read_version AND credits >= :amount;

If another transaction modified the row between our read and write, the version won't match and the UPDATE affects 0 rows. The application retries. This works well when conflicts are infrequent — each transaction usually succeeds on first attempt, and the occasional retry is acceptable.

The UPSERT race condition

A common concurrency bug in UPSERT (insert-or-update) logic:

# Non-atomic UPSERT — race condition between SELECT and INSERT
def get_or_create_user(email):
    user = db.query("SELECT * FROM users WHERE email = %s", email)
    if user:
        return user
    return db.execute("INSERT INTO users (email) VALUES (%s)", email)

Two concurrent calls for the same email both find no user, both try to insert — one gets a unique constraint violation. The fix is database-native UPSERT:

-- PostgreSQL: INSERT ... ON CONFLICT
INSERT INTO users (email, created_at)
VALUES (:email, NOW())
ON CONFLICT (email) DO NOTHING
RETURNING *;

-- If no row returned, fetch the existing one
SELECT * FROM users WHERE email = :email;

Or with ON CONFLICT DO UPDATE (atomic upsert):

INSERT INTO counters (key, value)
VALUES ('page_views', 1)
ON CONFLICT (key) DO UPDATE SET value = counters.value + 1
RETURNING value;

The mental model

Concurrency bugs come from assuming that a sequence of database operations is atomic when it isn't. The solution is always to identify the operation that must be atomic and make it so — either with a single SQL statement that does the check and write together, with SELECT FOR UPDATE to hold locks across multiple operations, or with optimistic locking and retry logic. Any other approach leaves a race condition window.